reduce databse size: jobs

Showing posts with label jobs. Show all posts

Wednesday, March 28, 2012

Reg. Log Shipping.

I have configured a log shipping on one of my Database.
Both the two jobs i.e. copy log and resotre log jobs run successfully
on the destination machines without any problem but I dont see any
logs copied on the destination machine.
1.Initially I had a job which used to take the transaction log
backup ,I thought this might be the reason as all the transaction are
getting backed up log shipping copy job is not getting any transaction
log to copy to the destination server.
2.But after disabling the transaction log backup job also samething is
happening i.e. log shipping copy and restore job is running
successfully but i dont see anything copied or resotred.
I must be doing something wrong but dont know whats that..
Please help.
Thanks
Sudhir
Hi
Are you sure that job was running? Have you looked at view job history?
"Sudhir" <sam904@.gmail.com> wrote in message
news:1182320095.674384.196630@.u2g2000hsc.googlegro ups.com...
>I have configured a log shipping on one of my Database.
> Both the two jobs i.e. copy log and resotre log jobs run successfully
> on the destination machines without any problem but I dont see any
> logs copied on the destination machine.
> 1.Initially I had a job which used to take the transaction log
> backup ,I thought this might be the reason as all the transaction are
> getting backed up log shipping copy job is not getting any transaction
> log to copy to the destination server.
> 2.But after disabling the transaction log backup job also samething is
> happening i.e. log shipping copy and restore job is running
> successfully but i dont see anything copied or resotred.
> I must be doing something wrong but dont know whats that..
> Please help.
> Thanks
> Sudhir
>

Reg. Log Shipping.

I have configured a log shipping on one of my Database.
Both the two jobs i.e. copy log and resotre log jobs run successfully
on the destination machines without any problem but I dont see any
logs copied on the destination machine.
1.Initially I had a job which used to take the transaction log
backup ,I thought this might be the reason as all the transaction are
getting backed up log shipping copy job is not getting any transaction
log to copy to the destination server.
2.But after disabling the transaction log backup job also samething is
happening i.e. log shipping copy and restore job is running
successfully but i dont see anything copied or resotred.
I must be doing something wrong but dont know whats that..
Please help.
Thanks
SudhirHi
Are you sure that job was running? Have you looked at view job history?
"Sudhir" <sam904@.gmail.com> wrote in message
news:1182320095.674384.196630@.u2g2000hsc.googlegroups.com...
>I have configured a log shipping on one of my Database.
> Both the two jobs i.e. copy log and resotre log jobs run successfully
> on the destination machines without any problem but I dont see any
> logs copied on the destination machine.
> 1.Initially I had a job which used to take the transaction log
> backup ,I thought this might be the reason as all the transaction are
> getting backed up log shipping copy job is not getting any transaction
> log to copy to the destination server.
> 2.But after disabling the transaction log backup job also samething is
> happening i.e. log shipping copy and restore job is running
> successfully but i dont see anything copied or resotred.
> I must be doing something wrong but dont know whats that..
> Please help.
> Thanks
> Sudhir
>

Reg. Log Shipping.

I have configured a log shipping on one of my Database.
Both the two jobs i.e. copy log and resotre log jobs run successfully
on the destination machines without any problem but I dont see any
logs copied on the destination machine.
1.Initially I had a job which used to take the transaction log
backup ,I thought this might be the reason as all the transaction are
getting backed up log shipping copy job is not getting any transaction
log to copy to the destination server.
2.But after disabling the transaction log backup job also samething is
happening i.e. log shipping copy and restore job is running
successfully but i dont see anything copied or resotred.
I must be doing something wrong but dont know whats that..
Please help.
Thanks
SudhirHi
Are you sure that job was running? Have you looked at view job history?
"Sudhir" <sam904@.gmail.com> wrote in message
news:1182320095.674384.196630@.u2g2000hsc.googlegroups.com...
>I have configured a log shipping on one of my Database.
> Both the two jobs i.e. copy log and resotre log jobs run successfully
> on the destination machines without any problem but I dont see any
> logs copied on the destination machine.
> 1.Initially I had a job which used to take the transaction log
> backup ,I thought this might be the reason as all the transaction are
> getting backed up log shipping copy job is not getting any transaction
> log to copy to the destination server.
> 2.But after disabling the transaction log backup job also samething is
> happening i.e. log shipping copy and restore job is running
> successfully but i dont see anything copied or resotred.
> I must be doing something wrong but dont know whats that..
> Please help.
> Thanks
> Sudhir
>

Monday, March 26, 2012

REG : SQL JOBS

Hi,
Is there anyway that we would be able to get the start time of the job
when it was started manually..I already looked into all the tables related
to jobs and everything gets updated only after the completion of the job.But
i would like to know the start time as soon as the job is started..Not aware of any. A work around:
add a step at the very beginning of the job, put something like "select 1"
in it. Then check job history after starting the job.
HTH
Quentin
"Karthik" <nkvatsan@.hotmail.com> wrote in message
news:eoOSuHHTDHA.2120@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there anyway that we would be able to get the start time of the job
> when it was started manually..I already looked into all the tables related
> to jobs and everything gets updated only after the completion of the
job.But
> i would like to know the start time as soon as the job is started..
>|||Click on the job in Enterprise manager and view job history
It gets the time from sysjobhistory in the msdb database.
Regards
John|||sysjobhistory is not updated after the completion of the job. It gets an
update (or insert) after the completion of every step of every job.
"Karthik" <nkvatsan@.hotmail.com> wrote in message
news:eoOSuHHTDHA.2120@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there anyway that we would be able to get the start time of the job
> when it was started manually..I already looked into all the tables related
> to jobs and everything gets updated only after the completion of the
job.But
> i would like to know the start time as soon as the job is started..
>|||Thanks for the reply...Well thats what i meant.I dont want to add a step at
the beginning of every job because we have 300 jobs...But i was thinking of
modifying the sp_start_job proc..But was just wondering is there any
undocumented sp that will give the start time of the job as soon as it is
started..
"Quentin Ran" <quentinran@.yahoo.com> wrote in message
news:OJ6TXgHTDHA.2260@.TK2MSFTNGP12.phx.gbl...
> sysjobhistory is not updated after the completion of the job. It gets an
> update (or insert) after the completion of every step of every job.
> "Karthik" <nkvatsan@.hotmail.com> wrote in message
> news:eoOSuHHTDHA.2120@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > Is there anyway that we would be able to get the start time of the job
> > when it was started manually..I already looked into all the tables
related
> > to jobs and everything gets updated only after the completion of the
> job.But
> > i would like to know the start time as soon as the job is started..
> >
> >
>|||The reason i wanted to know the start time is that i want to send
notification to the right group about the jobs that are taking more time
than the average run duration.I did already write scripts for that. But it
works only if the jobs are on their normal schedule because i can get the
next_run_time from sysjobschedule table. But if somebody starts the job
manually, i would like to know the start time for that so that i would be
able to calculate the rest of the information based on the start time of the
job..I hope i am clear on what i said...
"Quentin Ran" <quentinran@.yahoo.com> wrote in message
news:u2wkO$HTDHA.2148@.TK2MSFTNGP11.phx.gbl...
> Don't see why you need to do such check for so many jobs. A couple or
many,
> you may look at sp_add_jobstep to add the dummy step if so desire.
> I believe you are aware of the fact that if you need to start a job after
> another one, you can do it in a separate job to launch them one after the
> other.
> Quentin
>
> "Karthik" <nkvatsan@.hotmail.com> wrote in message
> news:e26Rx4HTDHA.1576@.TK2MSFTNGP12.phx.gbl...
> > Thanks for the reply...Well thats what i meant.I dont want to add a step
> at
> > the beginning of every job because we have 300 jobs...But i was thinking
> of
> > modifying the sp_start_job proc..But was just wondering is there any
> > undocumented sp that will give the start time of the job as soon as it
is
> > started..
> > "Quentin Ran" <quentinran@.yahoo.com> wrote in message
> > news:OJ6TXgHTDHA.2260@.TK2MSFTNGP12.phx.gbl...
> > > sysjobhistory is not updated after the completion of the job. It gets
> an
> > > update (or insert) after the completion of every step of every job.
> > >
> > > "Karthik" <nkvatsan@.hotmail.com> wrote in message
> > > news:eoOSuHHTDHA.2120@.TK2MSFTNGP10.phx.gbl...
> > > > Hi,
> > > > Is there anyway that we would be able to get the start time of the
> job
> > > > when it was started manually..I already looked into all the tables
> > related
> > > > to jobs and everything gets updated only after the completion of the
> > > job.But
> > > > i would like to know the start time as soon as the job is started..
> > > >
> > > >
> > >
> > >
> >
> >
>

Friday, March 23, 2012

Refresh Execution of last run status

Hi,
I have a lot of jobs that was schedule in SQL Server Agent and since
2008-03-13, the status won't be update. But, i know that the job still
running well. But the status of the next run date was OK.
I have search on the web and i don't see anything that can help me.
SQL2000 Standard Edition (two instance) (SP4)
Windows 2003 ServerYou have no error messages in the sql server or sql server agent log file?
Does the sql server agent service account have sysadmin rights?
Ola Hallengren
http://ola.hallengren.com
"inf0m286" wrote:
> Hi,
> I have a lot of jobs that was schedule in SQL Server Agent and since
> 2008-03-13, the status won't be update. But, i know that the job still
> running well. But the status of the next run date was OK.
> I have search on the web and i don't see anything that can help me.
>
> SQL2000 Standard Edition (two instance) (SP4)
> Windows 2003 Server
>
>
>|||Hi Ola,
I see no error in the server and agent log file. But, there is something
strange. At each night, an automated backup is schedule and i stop/start sql
server during that task. Normally, it create a new SQL log file and it's not
doing that since 03/17 .
For the account that own SQL Server and Agent, it is the local system
account (in the definition on these services), so i think it has sysadmin
rights .
Thanks
"Ola Hallengren" wrote:
> You have no error messages in the sql server or sql server agent log file?
> Does the sql server agent service account have sysadmin rights?
> Ola Hallengren
> http://ola.hallengren.com
>
> "inf0m286" wrote:
> > Hi,
> >
> > I have a lot of jobs that was schedule in SQL Server Agent and since
> > 2008-03-13, the status won't be update. But, i know that the job still
> > running well. But the status of the next run date was OK.
> >
> > I have search on the web and i don't see anything that can help me.
> >
> >
> > SQL2000 Standard Edition (two instance) (SP4)
> > Windows 2003 Server
> >
> >
> >
> >
> >|||>At each night, an automated backup is schedule and i stop/start sql server
during that task.
Could you give me some more information about how this is working. Is this a
sql server agent job?
Could you also give me some background for this backup strategy. In sql
server it is possible to backup a database with the BACKUP DATABASE command
while it is online.
>I have a lot of jobs that was schedule in SQL Server Agent and since 2008-03-13 the status won't be update
What if you just create a simple sql server agent tsql job and execute that?
/Ola
"inf0m286" wrote:
> Hi Ola,
> I see no error in the server and agent log file. But, there is something
> strange. At each night, an automated backup is schedule and i stop/start sql
> server during that task. Normally, it create a new SQL log file and it's not
> doing that since 03/17 .
> For the account that own SQL Server and Agent, it is the local system
> account (in the definition on these services), so i think it has sysadmin
> rights .
> Thanks
>
> "Ola Hallengren" wrote:
> > You have no error messages in the sql server or sql server agent log file?
> >
> > Does the sql server agent service account have sysadmin rights?
> >
> > Ola Hallengren
> > http://ola.hallengren.com
> >
> >
> >
> > "inf0m286" wrote:
> >
> > > Hi,
> > >
> > > I have a lot of jobs that was schedule in SQL Server Agent and since
> > > 2008-03-13, the status won't be update. But, i know that the job still
> > > running well. But the status of the next run date was OK.
> > >
> > > I have search on the web and i don't see anything that can help me.
> > >
> > >
> > > SQL2000 Standard Edition (two instance) (SP4)
> > > Windows 2003 Server
> > >
> > >
> > >
> > >
> > >|||A job for the tivoli server is scheduled and a we can run a pre-backup and
post-backup set. In the pre-backup step, a batch file is running In that
batch file, some services (SQL Server and SQL Server agent) is stopped (by
the dos command net stop). After that, the backup was taken and the services
is restarted.
I can take a backup while the database is online. i have make a tets with
SQL Query Analyzer.
Thanks.
AndrÃ©
"Ola Hallengren" wrote:
> >At each night, an automated backup is schedule and i stop/start sql server
> during that task.
> Could you give me some more information about how this is working. Is this a
> sql server agent job?
> Could you also give me some background for this backup strategy. In sql
> server it is possible to backup a database with the BACKUP DATABASE command
> while it is online.
> >I have a lot of jobs that was schedule in SQL Server Agent and since 2008-03-13 the status won't be update
> What if you just create a simple sql server agent tsql job and execute that?
> /Ola
>
> "inf0m286" wrote:
> > Hi Ola,
> >
> > I see no error in the server and agent log file. But, there is something
> > strange. At each night, an automated backup is schedule and i stop/start sql
> > server during that task. Normally, it create a new SQL log file and it's not
> > doing that since 03/17 .
> >
> > For the account that own SQL Server and Agent, it is the local system
> > account (in the definition on these services), so i think it has sysadmin
> > rights .
> >
> > Thanks
> >
> >
> > "Ola Hallengren" wrote:
> >
> > > You have no error messages in the sql server or sql server agent log file?
> > >
> > > Does the sql server agent service account have sysadmin rights?
> > >
> > > Ola Hallengren
> > > http://ola.hallengren.com
> > >
> > >
> > >
> > > "inf0m286" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a lot of jobs that was schedule in SQL Server Agent and since
> > > > 2008-03-13, the status won't be update. But, i know that the job still
> > > > running well. But the status of the next run date was OK.
> > > >
> > > > I have search on the web and i don't see anything that can help me.
> > > >
> > > >
> > > > SQL2000 Standard Edition (two instance) (SP4)
> > > > Windows 2003 Server
> > > >
> > > >
> > > >
> > > >
> > > >|||I think that you should edit this batch file so that you don't stop and start
the sql server and sql server agent services.
Instead you should add some sqlcmd commands to the batch file that performs
that sql backups.
sqlcmd -E -S Server1\Instance1 -d master -Q "BACKUP DATABASE master TO DISK
= 'C:\master.bak'" -b
If you would like to have a more dynamic solution you could use a solution
that I have made for this.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
Ola Hallengren
http://ola.hallengren.com
"inf0m286" wrote:
> A job for the tivoli server is scheduled and a we can run a pre-backup and
> post-backup set. In the pre-backup step, a batch file is running In that
> batch file, some services (SQL Server and SQL Server agent) is stopped (by
> the dos command net stop). After that, the backup was taken and the services
> is restarted.
> I can take a backup while the database is online. i have make a tets with
> SQL Query Analyzer.
>
> Thanks.
> AndrÃ©
>
>
> "Ola Hallengren" wrote:
> > >At each night, an automated backup is schedule and i stop/start sql server
> > during that task.
> >
> > Could you give me some more information about how this is working. Is this a
> > sql server agent job?
> >
> > Could you also give me some background for this backup strategy. In sql
> > server it is possible to backup a database with the BACKUP DATABASE command
> > while it is online.
> >
> > >I have a lot of jobs that was schedule in SQL Server Agent and since 2008-03-13 the status won't be update
> >
> > What if you just create a simple sql server agent tsql job and execute that?
> >
> > /Ola
> >
> >
> >
> > "inf0m286" wrote:
> >
> > > Hi Ola,
> > >
> > > I see no error in the server and agent log file. But, there is something
> > > strange. At each night, an automated backup is schedule and i stop/start sql
> > > server during that task. Normally, it create a new SQL log file and it's not
> > > doing that since 03/17 .
> > >
> > > For the account that own SQL Server and Agent, it is the local system
> > > account (in the definition on these services), so i think it has sysadmin
> > > rights .
> > >
> > > Thanks
> > >
> > >
> > > "Ola Hallengren" wrote:
> > >
> > > > You have no error messages in the sql server or sql server agent log file?
> > > >
> > > > Does the sql server agent service account have sysadmin rights?
> > > >
> > > > Ola Hallengren
> > > > http://ola.hallengren.com
> > > >
> > > >
> > > >
> > > > "inf0m286" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a lot of jobs that was schedule in SQL Server Agent and since
> > > > > 2008-03-13, the status won't be update. But, i know that the job still
> > > > > running well. But the status of the next run date was OK.
> > > > >
> > > > > I have search on the web and i don't see anything that can help me.
> > > > >
> > > > >
> > > > > SQL2000 Standard Edition (two instance) (SP4)
> > > > > Windows 2003 Server
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >|||Hi Ola,
I know that i can take a backup online of each database and take these
databases for the backup. But, some of the jobs that was not update are the
maintenance plan that was created by the Database Maintenance plan wizard.And
that these jobs was running well before 2008-03-13 and also some job that we
created. The data
The only thing that was made on the server is the add of another instance of
SQL. That installation was made in mid-february and nothing was wrong with
that. The problem is coming up when i detached one database of the first
instance and move it on the second one. And i created a maintenance plan for
the new instance and the status is OK
It's really a strange error.
Thanks.
"Ola Hallengren" wrote:
> I think that you should edit this batch file so that you don't stop and start
> the sql server and sql server agent services.
> Instead you should add some sqlcmd commands to the batch file that performs
> that sql backups.
> sqlcmd -E -S Server1\Instance1 -d master -Q "BACKUP DATABASE master TO DISK
> = 'C:\master.bak'" -b
> If you would like to have a more dynamic solution you could use a solution
> that I have made for this.
> http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
> Ola Hallengren
> http://ola.hallengren.com
>
> "inf0m286" wrote:
> > A job for the tivoli server is scheduled and a we can run a pre-backup and
> > post-backup set. In the pre-backup step, a batch file is running In that
> > batch file, some services (SQL Server and SQL Server agent) is stopped (by
> > the dos command net stop). After that, the backup was taken and the services
> > is restarted.
> >
> > I can take a backup while the database is online. i have make a tets with
> > SQL Query Analyzer.
> >
> >
> > Thanks.
> >
> > AndrÃ©
> >
> >
> >
> >
> >
> > "Ola Hallengren" wrote:
> >
> > > >At each night, an automated backup is schedule and i stop/start sql server
> > > during that task.
> > >
> > > Could you give me some more information about how this is working. Is this a
> > > sql server agent job?
> > >
> > > Could you also give me some background for this backup strategy. In sql
> > > server it is possible to backup a database with the BACKUP DATABASE command
> > > while it is online.
> > >
> > > >I have a lot of jobs that was schedule in SQL Server Agent and since 2008-03-13 the status won't be update
> > >
> > > What if you just create a simple sql server agent tsql job and execute that?
> > >
> > > /Ola
> > >
> > >
> > >
> > > "inf0m286" wrote:
> > >
> > > > Hi Ola,
> > > >
> > > > I see no error in the server and agent log file. But, there is something
> > > > strange. At each night, an automated backup is schedule and i stop/start sql
> > > > server during that task. Normally, it create a new SQL log file and it's not
> > > > doing that since 03/17 .
> > > >
> > > > For the account that own SQL Server and Agent, it is the local system
> > > > account (in the definition on these services), so i think it has sysadmin
> > > > rights .
> > > >
> > > > Thanks
> > > >
> > > >
> > > > "Ola Hallengren" wrote:
> > > >
> > > > > You have no error messages in the sql server or sql server agent log file?
> > > > >
> > > > > Does the sql server agent service account have sysadmin rights?
> > > > >
> > > > > Ola Hallengren
> > > > > http://ola.hallengren.com
> > > > >
> > > > >
> > > > >
> > > > > "inf0m286" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have a lot of jobs that was schedule in SQL Server Agent and since
> > > > > > 2008-03-13, the status won't be update. But, i know that the job still
> > > > > > running well. But the status of the next run date was OK.
> > > > > >
> > > > > > I have search on the web and i don't see anything that can help me.
> > > > > >
> > > > > >
> > > > > > SQL2000 Standard Edition (two instance) (SP4)
> > > > > > Windows 2003 Server
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >|||Did you do this test on the instance where you have the problem?
What if you just create a simple sql server agent tsql job and execute that?
/Ola
"inf0m286" wrote:
> Hi Ola,
> I know that i can take a backup online of each database and take these
> databases for the backup. But, some of the jobs that was not update are the
> maintenance plan that was created by the Database Maintenance plan wizard.And
> that these jobs was running well before 2008-03-13 and also some job that we
> created. The data
> The only thing that was made on the server is the add of another instance of
> SQL. That installation was made in mid-february and nothing was wrong with
> that. The problem is coming up when i detached one database of the first
> instance and move it on the second one. And i created a maintenance plan for
> the new instance and the status is OK
> It's really a strange error.
> Thanks.
>
> "Ola Hallengren" wrote:
> > I think that you should edit this batch file so that you don't stop and start
> > the sql server and sql server agent services.
> >
> > Instead you should add some sqlcmd commands to the batch file that performs
> > that sql backups.
> >
> > sqlcmd -E -S Server1\Instance1 -d master -Q "BACKUP DATABASE master TO DISK
> > = 'C:\master.bak'" -b
> >
> > If you would like to have a more dynamic solution you could use a solution
> > that I have made for this.
> > http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
> >
> > Ola Hallengren
> > http://ola.hallengren.com
> >
> >
> >
> > "inf0m286" wrote:
> >
> > > A job for the tivoli server is scheduled and a we can run a pre-backup and
> > > post-backup set. In the pre-backup step, a batch file is running In that
> > > batch file, some services (SQL Server and SQL Server agent) is stopped (by
> > > the dos command net stop). After that, the backup was taken and the services
> > > is restarted.
> > >
> > > I can take a backup while the database is online. i have make a tets with
> > > SQL Query Analyzer.
> > >
> > >
> > > Thanks.
> > >
> > > AndrÃ©
> > >
> > >
> > >
> > >
> > >
> > > "Ola Hallengren" wrote:
> > >
> > > > >At each night, an automated backup is schedule and i stop/start sql server
> > > > during that task.
> > > >
> > > > Could you give me some more information about how this is working. Is this a
> > > > sql server agent job?
> > > >
> > > > Could you also give me some background for this backup strategy. In sql
> > > > server it is possible to backup a database with the BACKUP DATABASE command
> > > > while it is online.
> > > >
> > > > >I have a lot of jobs that was schedule in SQL Server Agent and since 2008-03-13 the status won't be update
> > > >
> > > > What if you just create a simple sql server agent tsql job and execute that?
> > > >
> > > > /Ola
> > > >
> > > >
> > > >
> > > > "inf0m286" wrote:
> > > >
> > > > > Hi Ola,
> > > > >
> > > > > I see no error in the server and agent log file. But, there is something
> > > > > strange. At each night, an automated backup is schedule and i stop/start sql
> > > > > server during that task. Normally, it create a new SQL log file and it's not
> > > > > doing that since 03/17 .
> > > > >
> > > > > For the account that own SQL Server and Agent, it is the local system
> > > > > account (in the definition on these services), so i think it has sysadmin
> > > > > rights .
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > > "Ola Hallengren" wrote:
> > > > >
> > > > > > You have no error messages in the sql server or sql server agent log file?
> > > > > >
> > > > > > Does the sql server agent service account have sysadmin rights?
> > > > > >
> > > > > > Ola Hallengren
> > > > > > http://ola.hallengren.com
> > > > > >
> > > > > >
> > > > > >
> > > > > > "inf0m286" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I have a lot of jobs that was schedule in SQL Server Agent and since
> > > > > > > 2008-03-13, the status won't be update. But, i know that the job still
> > > > > > > running well. But the status of the next run date was OK.
> > > > > > >
> > > > > > > I have search on the web and i don't see anything that can help me.
> > > > > > >
> > > > > > >
> > > > > > > SQL2000 Standard Edition (two instance) (SP4)
> > > > > > > Windows 2003 Server
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >|||Hi Ola,
I created two simple job (one on each instance) that executedd a DOS command
and on the default instance, noting was updated and in the new instance, all
is right.
I also make a test on a virtual machine with 2 instances and i have the same
problem
Thanks
"Ola Hallengren" wrote:
> Did you do this test on the instance where you have the problem?
> What if you just create a simple sql server agent tsql job and execute that?
> /Ola
>
>
> "inf0m286" wrote:
> > Hi Ola,
> >
> > I know that i can take a backup online of each database and take these
> > databases for the backup. But, some of the jobs that was not update are the
> > maintenance plan that was created by the Database Maintenance plan wizard.And
> > that these jobs was running well before 2008-03-13 and also some job that we
> > created. The data
> >
> > The only thing that was made on the server is the add of another instance of
> > SQL. That installation was made in mid-february and nothing was wrong with
> > that. The problem is coming up when i detached one database of the first
> > instance and move it on the second one. And i created a maintenance plan for
> > the new instance and the status is OK
> >
> > It's really a strange error.
> >
> > Thanks.
> >
> >
> > "Ola Hallengren" wrote:
> >
> > > I think that you should edit this batch file so that you don't stop and start
> > > the sql server and sql server agent services.
> > >
> > > Instead you should add some sqlcmd commands to the batch file that performs
> > > that sql backups.
> > >
> > > sqlcmd -E -S Server1\Instance1 -d master -Q "BACKUP DATABASE master TO DISK
> > > = 'C:\master.bak'" -b
> > >
> > > If you would like to have a more dynamic solution you could use a solution
> > > that I have made for this.
> > > http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
> > >
> > > Ola Hallengren
> > > http://ola.hallengren.com
> > >
> > >
> > >
> > > "inf0m286" wrote:
> > >
> > > > A job for the tivoli server is scheduled and a we can run a pre-backup and
> > > > post-backup set. In the pre-backup step, a batch file is running In that
> > > > batch file, some services (SQL Server and SQL Server agent) is stopped (by
> > > > the dos command net stop). After that, the backup was taken and the services
> > > > is restarted.
> > > >
> > > > I can take a backup while the database is online. i have make a tets with
> > > > SQL Query Analyzer.
> > > >
> > > >
> > > > Thanks.
> > > >
> > > > AndrÃ©
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Ola Hallengren" wrote:
> > > >
> > > > > >At each night, an automated backup is schedule and i stop/start sql server
> > > > > during that task.
> > > > >
> > > > > Could you give me some more information about how this is working. Is this a
> > > > > sql server agent job?
> > > > >
> > > > > Could you also give me some background for this backup strategy. In sql
> > > > > server it is possible to backup a database with the BACKUP DATABASE command
> > > > > while it is online.
> > > > >
> > > > > >I have a lot of jobs that was schedule in SQL Server Agent and since 2008-03-13 the status won't be update
> > > > >
> > > > > What if you just create a simple sql server agent tsql job and execute that?
> > > > >
> > > > > /Ola
> > > > >
> > > > >
> > > > >
> > > > > "inf0m286" wrote:
> > > > >
> > > > > > Hi Ola,
> > > > > >
> > > > > > I see no error in the server and agent log file. But, there is something
> > > > > > strange. At each night, an automated backup is schedule and i stop/start sql
> > > > > > server during that task. Normally, it create a new SQL log file and it's not
> > > > > > doing that since 03/17 .
> > > > > >
> > > > > > For the account that own SQL Server and Agent, it is the local system
> > > > > > account (in the definition on these services), so i think it has sysadmin
> > > > > > rights .
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > >
> > > > > > "Ola Hallengren" wrote:
> > > > > >
> > > > > > > You have no error messages in the sql server or sql server agent log file?
> > > > > > >
> > > > > > > Does the sql server agent service account have sysadmin rights?
> > > > > > >
> > > > > > > Ola Hallengren
> > > > > > > http://ola.hallengren.com
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "inf0m286" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I have a lot of jobs that was schedule in SQL Server Agent and since
> > > > > > > > 2008-03-13, the status won't be update. But, i know that the job still
> > > > > > > > running well. But the status of the next run date was OK.
> > > > > > > >
> > > > > > > > I have search on the web and i don't see anything that can help me.
> > > > > > > >
> > > > > > > >
> > > > > > > > SQL2000 Standard Edition (two instance) (SP4)
> > > > > > > > Windows 2003 Server
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >|||I'm sorry, but I don't know how to solve this problem.
Ola Hallengren
http://ola.hallengren.com
"inf0m286" wrote:
> Hi Ola,
> I created two simple job (one on each instance) that executedd a DOS command
> and on the default instance, noting was updated and in the new instance, all
> is right.
> I also make a test on a virtual machine with 2 instances and i have the same
> problem
> Thanks
> "Ola Hallengren" wrote:
> > Did you do this test on the instance where you have the problem?
> >
> > What if you just create a simple sql server agent tsql job and execute that?
> >
> > /Ola
> >
> >
> >
> >
> > "inf0m286" wrote:
> >
> > > Hi Ola,
> > >
> > > I know that i can take a backup online of each database and take these
> > > databases for the backup. But, some of the jobs that was not update are the
> > > maintenance plan that was created by the Database Maintenance plan wizard.And
> > > that these jobs was running well before 2008-03-13 and also some job that we
> > > created. The data
> > >
> > > The only thing that was made on the server is the add of another instance of
> > > SQL. That installation was made in mid-february and nothing was wrong with
> > > that. The problem is coming up when i detached one database of the first
> > > instance and move it on the second one. And i created a maintenance plan for
> > > the new instance and the status is OK
> > >
> > > It's really a strange error.
> > >
> > > Thanks.
> > >
> > >
> > > "Ola Hallengren" wrote:
> > >
> > > > I think that you should edit this batch file so that you don't stop and start
> > > > the sql server and sql server agent services.
> > > >
> > > > Instead you should add some sqlcmd commands to the batch file that performs
> > > > that sql backups.
> > > >
> > > > sqlcmd -E -S Server1\Instance1 -d master -Q "BACKUP DATABASE master TO DISK
> > > > = 'C:\master.bak'" -b
> > > >
> > > > If you would like to have a more dynamic solution you could use a solution
> > > > that I have made for this.
> > > > http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
> > > >
> > > > Ola Hallengren
> > > > http://ola.hallengren.com
> > > >
> > > >
> > > >
> > > > "inf0m286" wrote:
> > > >
> > > > > A job for the tivoli server is scheduled and a we can run a pre-backup and
> > > > > post-backup set. In the pre-backup step, a batch file is running In that
> > > > > batch file, some services (SQL Server and SQL Server agent) is stopped (by
> > > > > the dos command net stop). After that, the backup was taken and the services
> > > > > is restarted.
> > > > >
> > > > > I can take a backup while the database is online. i have make a tets with
> > > > > SQL Query Analyzer.
> > > > >
> > > > >
> > > > > Thanks.
> > > > >
> > > > > AndrÃ©
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Ola Hallengren" wrote:
> > > > >
> > > > > > >At each night, an automated backup is schedule and i stop/start sql server
> > > > > > during that task.
> > > > > >
> > > > > > Could you give me some more information about how this is working. Is this a
> > > > > > sql server agent job?
> > > > > >
> > > > > > Could you also give me some background for this backup strategy. In sql
> > > > > > server it is possible to backup a database with the BACKUP DATABASE command
> > > > > > while it is online.
> > > > > >
> > > > > > >I have a lot of jobs that was schedule in SQL Server Agent and since 2008-03-13 the status won't be update
> > > > > >
> > > > > > What if you just create a simple sql server agent tsql job and execute that?
> > > > > >
> > > > > > /Ola
> > > > > >
> > > > > >
> > > > > >
> > > > > > "inf0m286" wrote:
> > > > > >
> > > > > > > Hi Ola,
> > > > > > >
> > > > > > > I see no error in the server and agent log file. But, there is something
> > > > > > > strange. At each night, an automated backup is schedule and i stop/start sql
> > > > > > > server during that task. Normally, it create a new SQL log file and it's not
> > > > > > > doing that since 03/17 .
> > > > > > >
> > > > > > > For the account that own SQL Server and Agent, it is the local system
> > > > > > > account (in the definition on these services), so i think it has sysadmin
> > > > > > > rights .
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > >
> > > > > > > "Ola Hallengren" wrote:
> > > > > > >
> > > > > > > > You have no error messages in the sql server or sql server agent log file?
> > > > > > > >
> > > > > > > > Does the sql server agent service account have sysadmin rights?
> > > > > > > >
> > > > > > > > Ola Hallengren
> > > > > > > > http://ola.hallengren.com
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "inf0m286" wrote:
> > > > > > > >
> > > > > > > > > Hi,
> > > > > > > > >
> > > > > > > > > I have a lot of jobs that was schedule in SQL Server Agent and since
> > > > > > > > > 2008-03-13, the status won't be update. But, i know that the job still
> > > > > > > > > running well. But the status of the next run date was OK.
> > > > > > > > >
> > > > > > > > > I have search on the web and i don't see anything that can help me.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > SQL2000 Standard Edition (two instance) (SP4)
> > > > > > > > > Windows 2003 Server
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >|||Hi Ola,
Thanks for your support. If someone else find a solution, would you like to
know it '
Bye.
AndrÃ©
"Ola Hallengren" wrote:
> I'm sorry, but I don't know how to solve this problem.
> Ola Hallengren
> http://ola.hallengren.com
>
> "inf0m286" wrote:
> > Hi Ola,
> >
> > I created two simple job (one on each instance) that executedd a DOS command
> > and on the default instance, noting was updated and in the new instance, all
> > is right.
> >
> > I also make a test on a virtual machine with 2 instances and i have the same
> > problem
> >
> > Thanks
> >
> > "Ola Hallengren" wrote:
> >
> > > Did you do this test on the instance where you have the problem?
> > >
> > > What if you just create a simple sql server agent tsql job and execute that?
> > >
> > > /Ola
> > >
> > >
> > >
> > >
> > > "inf0m286" wrote:
> > >
> > > > Hi Ola,
> > > >
> > > > I know that i can take a backup online of each database and take these
> > > > databases for the backup. But, some of the jobs that was not update are the
> > > > maintenance plan that was created by the Database Maintenance plan wizard.And
> > > > that these jobs was running well before 2008-03-13 and also some job that we
> > > > created. The data
> > > >
> > > > The only thing that was made on the server is the add of another instance of
> > > > SQL. That installation was made in mid-february and nothing was wrong with
> > > > that. The problem is coming up when i detached one database of the first
> > > > instance and move it on the second one. And i created a maintenance plan for
> > > > the new instance and the status is OK
> > > >
> > > > It's really a strange error.
> > > >
> > > > Thanks.
> > > >
> > > >
> > > > "Ola Hallengren" wrote:
> > > >
> > > > > I think that you should edit this batch file so that you don't stop and start
> > > > > the sql server and sql server agent services.
> > > > >
> > > > > Instead you should add some sqlcmd commands to the batch file that performs
> > > > > that sql backups.
> > > > >
> > > > > sqlcmd -E -S Server1\Instance1 -d master -Q "BACKUP DATABASE master TO DISK
> > > > > = 'C:\master.bak'" -b
> > > > >
> > > > > If you would like to have a more dynamic solution you could use a solution
> > > > > that I have made for this.
> > > > > http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
> > > > >
> > > > > Ola Hallengren
> > > > > http://ola.hallengren.com
> > > > >
> > > > >
> > > > >
> > > > > "inf0m286" wrote:
> > > > >
> > > > > > A job for the tivoli server is scheduled and a we can run a pre-backup and
> > > > > > post-backup set. In the pre-backup step, a batch file is running In that
> > > > > > batch file, some services (SQL Server and SQL Server agent) is stopped (by
> > > > > > the dos command net stop). After that, the backup was taken and the services
> > > > > > is restarted.
> > > > > >
> > > > > > I can take a backup while the database is online. i have make a tets with
> > > > > > SQL Query Analyzer.
> > > > > >
> > > > > >
> > > > > > Thanks.
> > > > > >
> > > > > > AndrÃ©
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Ola Hallengren" wrote:
> > > > > >
> > > > > > > >At each night, an automated backup is schedule and i stop/start sql server
> > > > > > > during that task.
> > > > > > >
> > > > > > > Could you give me some more information about how this is working. Is this a
> > > > > > > sql server agent job?
> > > > > > >
> > > > > > > Could you also give me some background for this backup strategy. In sql
> > > > > > > server it is possible to backup a database with the BACKUP DATABASE command
> > > > > > > while it is online.
> > > > > > >
> > > > > > > >I have a lot of jobs that was schedule in SQL Server Agent and since 2008-03-13 the status won't be update
> > > > > > >
> > > > > > > What if you just create a simple sql server agent tsql job and execute that?
> > > > > > >
> > > > > > > /Ola
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "inf0m286" wrote:
> > > > > > >
> > > > > > > > Hi Ola,
> > > > > > > >
> > > > > > > > I see no error in the server and agent log file. But, there is something
> > > > > > > > strange. At each night, an automated backup is schedule and i stop/start sql
> > > > > > > > server during that task. Normally, it create a new SQL log file and it's not
> > > > > > > > doing that since 03/17 .
> > > > > > > >
> > > > > > > > For the account that own SQL Server and Agent, it is the local system
> > > > > > > > account (in the definition on these services), so i think it has sysadmin
> > > > > > > > rights .
> > > > > > > >
> > > > > > > > Thanks
> > > > > > > >
> > > > > > > >
> > > > > > > > "Ola Hallengren" wrote:
> > > > > > > >
> > > > > > > > > You have no error messages in the sql server or sql server agent log file?
> > > > > > > > >
> > > > > > > > > Does the sql server agent service account have sysadmin rights?
> > > > > > > > >
> > > > > > > > > Ola Hallengren
> > > > > > > > > http://ola.hallengren.com
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "inf0m286" wrote:
> > > > > > > > >
> > > > > > > > > > Hi,
> > > > > > > > > >
> > > > > > > > > > I have a lot of jobs that was schedule in SQL Server Agent and since
> > > > > > > > > > 2008-03-13, the status won't be update. But, i know that the job still
> > > > > > > > > > running well. But the status of the next run date was OK.
> > > > > > > > > >
> > > > > > > > > > I have search on the web and i don't see anything that can help me.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > SQL2000 Standard Edition (two instance) (SP4)
> > > > > > > > > > Windows 2003 Server
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >|||Sure. Just post it to this thread then. I have notification enabled.
Ola Hallengren
http://ola.hallengren.com
"inf0m286" wrote:
> Hi Ola,
> Thanks for your support. If someone else find a solution, would you like to
> know it '
> Bye.
> AndrÃ©
>
> "Ola Hallengren" wrote:
> > I'm sorry, but I don't know how to solve this problem.
> >
> > Ola Hallengren
> > http://ola.hallengren.com
> >
> >
> >
> > "inf0m286" wrote:
> >
> > > Hi Ola,
> > >
> > > I created two simple job (one on each instance) that executedd a DOS command
> > > and on the default instance, noting was updated and in the new instance, all
> > > is right.
> > >
> > > I also make a test on a virtual machine with 2 instances and i have the same
> > > problem
> > >
> > > Thanks
> > >
> > > "Ola Hallengren" wrote:
> > >
> > > > Did you do this test on the instance where you have the problem?
> > > >
> > > > What if you just create a simple sql server agent tsql job and execute that?
> > > >
> > > > /Ola
> > > >
> > > >
> > > >
> > > >
> > > > "inf0m286" wrote:
> > > >
> > > > > Hi Ola,
> > > > >
> > > > > I know that i can take a backup online of each database and take these
> > > > > databases for the backup. But, some of the jobs that was not update are the
> > > > > maintenance plan that was created by the Database Maintenance plan wizard.And
> > > > > that these jobs was running well before 2008-03-13 and also some job that we
> > > > > created. The data
> > > > >
> > > > > The only thing that was made on the server is the add of another instance of
> > > > > SQL. That installation was made in mid-february and nothing was wrong with
> > > > > that. The problem is coming up when i detached one database of the first
> > > > > instance and move it on the second one. And i created a maintenance plan for
> > > > > the new instance and the status is OK
> > > > >
> > > > > It's really a strange error.
> > > > >
> > > > > Thanks.
> > > > >
> > > > >
> > > > > "Ola Hallengren" wrote:
> > > > >
> > > > > > I think that you should edit this batch file so that you don't stop and start
> > > > > > the sql server and sql server agent services.
> > > > > >
> > > > > > Instead you should add some sqlcmd commands to the batch file that performs
> > > > > > that sql backups.
> > > > > >
> > > > > > sqlcmd -E -S Server1\Instance1 -d master -Q "BACKUP DATABASE master TO DISK
> > > > > > = 'C:\master.bak'" -b
> > > > > >
> > > > > > If you would like to have a more dynamic solution you could use a solution
> > > > > > that I have made for this.
> > > > > > http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
> > > > > >
> > > > > > Ola Hallengren
> > > > > > http://ola.hallengren.com
> > > > > >
> > > > > >
> > > > > >
> > > > > > "inf0m286" wrote:
> > > > > >
> > > > > > > A job for the tivoli server is scheduled and a we can run a pre-backup and
> > > > > > > post-backup set. In the pre-backup step, a batch file is running In that
> > > > > > > batch file, some services (SQL Server and SQL Server agent) is stopped (by
> > > > > > > the dos command net stop). After that, the backup was taken and the services
> > > > > > > is restarted.
> > > > > > >
> > > > > > > I can take a backup while the database is online. i have make a tets with
> > > > > > > SQL Query Analyzer.
> > > > > > >
> > > > > > >
> > > > > > > Thanks.
> > > > > > >
> > > > > > > AndrÃ©
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Ola Hallengren" wrote:
> > > > > > >
> > > > > > > > >At each night, an automated backup is schedule and i stop/start sql server
> > > > > > > > during that task.
> > > > > > > >
> > > > > > > > Could you give me some more information about how this is working. Is this a
> > > > > > > > sql server agent job?
> > > > > > > >
> > > > > > > > Could you also give me some background for this backup strategy. In sql
> > > > > > > > server it is possible to backup a database with the BACKUP DATABASE command
> > > > > > > > while it is online.
> > > > > > > >
> > > > > > > > >I have a lot of jobs that was schedule in SQL Server Agent and since 2008-03-13 the status won't be update
> > > > > > > >
> > > > > > > > What if you just create a simple sql server agent tsql job and execute that?
> > > > > > > >
> > > > > > > > /Ola
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "inf0m286" wrote:
> > > > > > > >
> > > > > > > > > Hi Ola,
> > > > > > > > >
> > > > > > > > > I see no error in the server and agent log file. But, there is something
> > > > > > > > > strange. At each night, an automated backup is schedule and i stop/start sql
> > > > > > > > > server during that task. Normally, it create a new SQL log file and it's not
> > > > > > > > > doing that since 03/17 .
> > > > > > > > >
> > > > > > > > > For the account that own SQL Server and Agent, it is the local system
> > > > > > > > > account (in the definition on these services), so i think it has sysadmin
> > > > > > > > > rights .
> > > > > > > > >
> > > > > > > > > Thanks
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Ola Hallengren" wrote:
> > > > > > > > >
> > > > > > > > > > You have no error messages in the sql server or sql server agent log file?
> > > > > > > > > >
> > > > > > > > > > Does the sql server agent service account have sysadmin rights?
> > > > > > > > > >
> > > > > > > > > > Ola Hallengren
> > > > > > > > > > http://ola.hallengren.com
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "inf0m286" wrote:
> > > > > > > > > >
> > > > > > > > > > > Hi,
> > > > > > > > > > >
> > > > > > > > > > > I have a lot of jobs that was schedule in SQL Server Agent and since
> > > > > > > > > > > 2008-03-13, the status won't be update. But, i know that the job still
> > > > > > > > > > > running well. But the status of the next run date was OK.
> > > > > > > > > > >
> > > > > > > > > > > I have search on the web and i don't see anything that can help me.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > SQL2000 Standard Edition (two instance) (SP4)
> > > > > > > > > > > Windows 2003 Server
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >|||Hi Ola,
Finally, there is a solution. Someone find that there is too much entry in
the alias tab in the SQL Server Network utility. So i delete all these alias,
create only the one i need and the problem was gone.
Bye.
AndrÃ©.
"Ola Hallengren" wrote:
> Sure. Just post it to this thread then. I have notification enabled.
> Ola Hallengren
> http://ola.hallengren.com
>
>
> "inf0m286" wrote:
> > Hi Ola,
> >
> > Thanks for your support. If someone else find a solution, would you like to
> > know it '
> >
> > Bye.
> >
> > AndrÃ©
> >
> >
> >
> > "Ola Hallengren" wrote:
> >
> > > I'm sorry, but I don't know how to solve this problem.
> > >
> > > Ola Hallengren
> > > http://ola.hallengren.com
> > >
> > >
> > >
> > > "inf0m286" wrote:
> > >
> > > > Hi Ola,
> > > >
> > > > I created two simple job (one on each instance) that executedd a DOS command
> > > > and on the default instance, noting was updated and in the new instance, all
> > > > is right.
> > > >
> > > > I also make a test on a virtual machine with 2 instances and i have the same
> > > > problem
> > > >
> > > > Thanks
> > > >
> > > > "Ola Hallengren" wrote:
> > > >
> > > > > Did you do this test on the instance where you have the problem?
> > > > >
> > > > > What if you just create a simple sql server agent tsql job and execute that?
> > > > >
> > > > > /Ola
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "inf0m286" wrote:
> > > > >
> > > > > > Hi Ola,
> > > > > >
> > > > > > I know that i can take a backup online of each database and take these
> > > > > > databases for the backup. But, some of the jobs that was not update are the
> > > > > > maintenance plan that was created by the Database Maintenance plan wizard.And
> > > > > > that these jobs was running well before 2008-03-13 and also some job that we
> > > > > > created. The data
> > > > > >
> > > > > > The only thing that was made on the server is the add of another instance of
> > > > > > SQL. That installation was made in mid-february and nothing was wrong with
> > > > > > that. The problem is coming up when i detached one database of the first
> > > > > > instance and move it on the second one. And i created a maintenance plan for
> > > > > > the new instance and the status is OK
> > > > > >
> > > > > > It's really a strange error.
> > > > > >
> > > > > > Thanks.
> > > > > >
> > > > > >
> > > > > > "Ola Hallengren" wrote:
> > > > > >
> > > > > > > I think that you should edit this batch file so that you don't stop and start
> > > > > > > the sql server and sql server agent services.
> > > > > > >
> > > > > > > Instead you should add some sqlcmd commands to the batch file that performs
> > > > > > > that sql backups.
> > > > > > >
> > > > > > > sqlcmd -E -S Server1\Instance1 -d master -Q "BACKUP DATABASE master TO DISK
> > > > > > > = 'C:\master.bak'" -b
> > > > > > >
> > > > > > > If you would like to have a more dynamic solution you could use a solution
> > > > > > > that I have made for this.
> > > > > > > http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
> > > > > > >
> > > > > > > Ola Hallengren
> > > > > > > http://ola.hallengren.com
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "inf0m286" wrote:
> > > > > > >
> > > > > > > > A job for the tivoli server is scheduled and a we can run a pre-backup and
> > > > > > > > post-backup set. In the pre-backup step, a batch file is running In that
> > > > > > > > batch file, some services (SQL Server and SQL Server agent) is stopped (by
> > > > > > > > the dos command net stop). After that, the backup was taken and the services
> > > > > > > > is restarted.
> > > > > > > >
> > > > > > > > I can take a backup while the database is online. i have make a tets with
> > > > > > > > SQL Query Analyzer.
> > > > > > > >
> > > > > > > >
> > > > > > > > Thanks.
> > > > > > > >
> > > > > > > > AndrÃ©
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Ola Hallengren" wrote:
> > > > > > > >
> > > > > > > > > >At each night, an automated backup is schedule and i stop/start sql server
> > > > > > > > > during that task.
> > > > > > > > >
> > > > > > > > > Could you give me some more information about how this is working. Is this a
> > > > > > > > > sql server agent job?
> > > > > > > > >
> > > > > > > > > Could you also give me some background for this backup strategy. In sql
> > > > > > > > > server it is possible to backup a database with the BACKUP DATABASE command
> > > > > > > > > while it is online.
> > > > > > > > >
> > > > > > > > > >I have a lot of jobs that was schedule in SQL Server Agent and since 2008-03-13 the status won't be update
> > > > > > > > >
> > > > > > > > > What if you just create a simple sql server agent tsql job and execute that?
> > > > > > > > >
> > > > > > > > > /Ola
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "inf0m286" wrote:
> > > > > > > > >
> > > > > > > > > > Hi Ola,
> > > > > > > > > >
> > > > > > > > > > I see no error in the server and agent log file. But, there is something
> > > > > > > > > > strange. At each night, an automated backup is schedule and i stop/start sql
> > > > > > > > > > server during that task. Normally, it create a new SQL log file and it's not
> > > > > > > > > > doing that since 03/17 .
> > > > > > > > > >
> > > > > > > > > > For the account that own SQL Server and Agent, it is the local system
> > > > > > > > > > account (in the definition on these services), so i think it has sysadmin
> > > > > > > > > > rights .
> > > > > > > > > >
> > > > > > > > > > Thanks
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "Ola Hallengren" wrote:
> > > > > > > > > >
> > > > > > > > > > > You have no error messages in the sql server or sql server agent log file?
> > > > > > > > > > >
> > > > > > > > > > > Does the sql server agent service account have sysadmin rights?
> > > > > > > > > > >
> > > > > > > > > > > Ola Hallengren
> > > > > > > > > > > http://ola.hallengren.com
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > "inf0m286" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > Hi,
> > > > > > > > > > > >
> > > > > > > > > > > > I have a lot of jobs that was schedule in SQL Server Agent and since
> > > > > > > > > > > > 2008-03-13, the status won't be update. But, i know that the job still
> > > > > > > > > > > > running well. But the status of the next run date was OK.
> > > > > > > > > > > >
> > > > > > > > > > > > I have search on the web and i don't see anything that can help me.
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > SQL2000 Standard Edition (two instance) (SP4)
> > > > > > > > > > > > Windows 2003 Server
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >

Wednesday, March 7, 2012

Re-Establishing Maintenance Jobs?

I have a client that hasn't had a successful run of any of the four
"standard" maintenance jobs in over 4 months. (Integrity Check,
Optimization, DB Backup, and Transaction Log Backup).
I know what the problem is, but I'm not his DBA (in fact I'm a mere contract
software developer). Apparently my client doesn't have a DBA, or this
wouldn't be the case, and he may turn to me to fix the problem. (I suspect
that they renamed the server, and the job owner is listed with the old
server name).
I'm logging in remotely, so I'm somewhat apprehensive of simply turning the
four jobs on overnight. I can envision problems with temp space, disk
space, and possibly excessive time required for index rebuilds. I don't
know if a CHECKDB takes longer if it hasn't been done in a while.
Is there anything else I should be prepared for?
Is there a recommended approach to "softly" bring a potentially ailing (no
symptoms that I know of) database back into a verified and backed up state?
Should I do DBCC CHECKTABLE on a table at a time before doing CHECKDB, or is
it best to do DBCC CHECKDB to start with?
Would the following be a good sequence:
1: Verify disk space available compared to backup space required (how do I
estimate the backup space required?).
1: Manually perform backup of the database and Transaction log.
2: Manually perform DBCC CHECKDB (Estimate Only first to verify sufficient
space in tempdb). I assume I should do it as REPAIR_FAST?
3: Manually perform DBCC INDEXDEFRAG before or instead of DBCC REINDEX
4: Re-Establish the Agent Jobs.
TIA,
Tore.Thanks.
This is a 24/7 system (medium traffic web site), so my rationale for doing
the initial backup before anything else was to make sure that a copy of the
database would be available just in case something went very wrong during
the subsequent operations. I know the (a) backup really needs to be not
just on another drive, but offline and preferably off-site. However, the
initial backup would only be for the time it took to get everything back
into normal working order - at what point their overall server backup
procedures would (hopefully) address the proper security of the database
backups.
From your response, I guess I don't need to worry about problems during
checkdb or dbreindex or whether the database will remain in working order
should an error be found?
Should I specify FAST_REPAIR on the initial run of CHECKDB?
Tore.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:uW6MFHCWDHA.1512@.TK2MSFTNGP11.phx.gbl...
> See inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it
community
> of SQL Server professionals.
> www.sqlpass.org
>
> "Tore Bostrup" <newspost_at_bostrup.us> wrote in message
> news:#KnN$7#VDHA.1620@.TK2MSFTNGP12.phx.gbl...
> > I have a client that hasn't had a successful run of any of the four
> > "standard" maintenance jobs in over 4 months. (Integrity Check,
> > Optimization, DB Backup, and Transaction Log Backup).
> >
> > I know what the problem is, but I'm not his DBA (in fact I'm a mere
> contract
> > software developer). Apparently my client doesn't have a DBA, or this
> > wouldn't be the case, and he may turn to me to fix the problem. (I
> suspect
> > that they renamed the server, and the job owner is listed with the old
> > server name).
> >
> > I'm logging in remotely, so I'm somewhat apprehensive of simply turning
> the
> > four jobs on overnight. I can envision problems with temp space, disk
> > space, and possibly excessive time required for index rebuilds. I don't
> > know if a CHECKDB takes longer if it hasn't been done in a while.
> The time checkdb takes is related to the amount of IO required to read the
> records.
> >
> > Is there anything else I should be prepared for?
> >
> > Is there a recommended approach to "softly" bring a potentially ailing
(no
> > symptoms that I know of) database back into a verified and backed up
> state?
> > Should I do DBCC CHECKTABLE on a table at a time before doing CHECKDB,
or
> is
> > it best to do DBCC CHECKDB to start with?
> I would IMMEDIATELY implement backups - period...
> THen add CHECKDB - regardless of how long it takes...
> >
> > Would the following be a good sequence:
> >
> > 1: Verify disk space available compared to backup space required (how
do
> I
> > estimate the backup space required?).
> Backup the database either to tape or to a remote hard drive, backing up
on
> the local box does not protect from local box burn ups...sp_spaceused
> should show about how big things will be..
> > 1: Manually perform backup of the database and Transaction log.
> > 2: Manually perform DBCC CHECKDB (Estimate Only first to verify
> sufficient
> > space in tempdb). I assume I should do it as REPAIR_FAST?
> > 3: Manually perform DBCC INDEXDEFRAG before or instead of DBCC REINDEX
> I would probably do dbcc dbreindex (at least the first time) since it has
> been forever since maintenance has been done... on highly fragmented
tables
> indexdefrag can take longer than dbreindex. (after the first time, do
> whichever of the two you prefer.)
> > 4: Re-Establish the Agent Jobs.
> When scheduling the jobs, run index maintenance BEFORE the db backups...
The
> backup will then be a copy of a well maintained database...so if you ever
> need to restore, you will NOT have to immediately do index maintenance
after
> the restore..
> >
> > TIA,
> > Tore.
> >
> >
>

Reducing the size of the Transaction Log.

We have a SQL Server 2000 SP3 database with recovery model set to full. I
set up 3 automatic backup jobs as follows:
Each day,Mon thru Fri at 9:00 am
1 step - full backup of database to file A with overwrite
Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
1 step - transaction log backup of database to file A with append
Each day,Mon thru Fri at 5:05 pm
1st step - transaction log backup of database to file A with append
2nd step - execute BACKUP LOG WITH NO_LOG
3rd step - execute DBCC SHRINK (db_file_name,1)
First I tried to shrink the Log file without issuing the "BACKUP LOG WITH
NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want, even
though I back up the Log.
When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
However, I get an error in the Event Viever(Application Log) stating
"Database Log truncated".
My question is:
Should I worry about this error ?
Is any data loss likely will this arrangement ?
After all, I truncate the Transaction Log on purpose, right after backing it
back in the previous step.
Regards,
SezginYou should not use the no_log command if you require point in time recovery,
and if you do not require point in time recovery, set your recovery mode to
simple. I would advise not to be shrinking your log or data file like you
are as if the db or log has to autogrow it costs you at the point it needs
to allocate more space and that is typically when your db is busy.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sezgin Rafet" <srafet@.hotmail.com> wrote in message
news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> We have a SQL Server 2000 SP3 database with recovery model set to full. I
> set up 3 automatic backup jobs as follows:
> Each day,Mon thru Fri at 9:00 am
> 1 step - full backup of database to file A with overwrite
> Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> 1 step - transaction log backup of database to file A with append
> Each day,Mon thru Fri at 5:05 pm
> 1st step - transaction log backup of database to file A with append
> 2nd step - execute BACKUP LOG WITH NO_LOG
> 3rd step - execute DBCC SHRINK (db_file_name,1)
> First I tried to shrink the Log file without issuing the "BACKUP LOG WITH
> NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
even
> though I back up the Log.
>
> When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> However, I get an error in the Event Viever(Application Log) stating
> "Database Log truncated".
> My question is:
> Should I worry about this error ?
> Is any data loss likely will this arrangement ?
> After all, I truncate the Transaction Log on purpose, right after backing
it
> back in the previous step.
> Regards,
> Sezgin
>
>|||Thanks for your reply Ray,
The database has a high number of transactions every day. I have to make
sure any possible data loss is minimized. That is why I used full recovery
model. During tests, in about 5 weeks time, the Transaction Log grew up to
nearly 7 GB (the data file currently is just over 1 GB). I tried my best to
shrink the Transaction Log without using the "Backup Log with no_log"
command, but to no success. Could you offer any advice or alternative
solution.
Best regards,
Sezgin Rafet
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> You should not use the no_log command if you require point in time
recovery,
> and if you do not require point in time recovery, set your recovery mode
to
> simple. I would advise not to be shrinking your log or data file like you
> are as if the db or log has to autogrow it costs you at the point it needs
> to allocate more space and that is typically when your db is busy.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > We have a SQL Server 2000 SP3 database with recovery model set to full.
I
> > set up 3 automatic backup jobs as follows:
> >
> > Each day,Mon thru Fri at 9:00 am
> > 1 step - full backup of database to file A with overwrite
> >
> > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > 1 step - transaction log backup of database to file A with append
> >
> > Each day,Mon thru Fri at 5:05 pm
> > 1st step - transaction log backup of database to file A with append
> > 2nd step - execute BACKUP LOG WITH NO_LOG
> > 3rd step - execute DBCC SHRINK (db_file_name,1)
> >
> > First I tried to shrink the Log file without issuing the "BACKUP LOG
WITH
> > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
> even
> > though I back up the Log.
> >
> >
> > When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> > However, I get an error in the Event Viever(Application Log) stating
> > "Database Log truncated".
> >
> > My question is:
> > Should I worry about this error ?
> > Is any data loss likely will this arrangement ?
> >
> > After all, I truncate the Transaction Log on purpose, right after
backing
> it
> > back in the previous step.
> >
> > Regards,
> >
> > Sezgin
> >
> >
> >
> >
>|||THe log should eventually shrink, and you should NOT be using backup log
with no_log...
I noticed however that you said you were using dbcc shrink...
The proper command is dbcc shrinkfile, or dbcc shrikdatabase... Could that
be part of the problem?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sezgin Rafet" <srafet@.hotmail.com> wrote in message
news:uvz5lIGyDHA.2308@.TK2MSFTNGP11.phx.gbl...
> Thanks for your reply Ray,
>
> The database has a high number of transactions every day. I have to make
> sure any possible data loss is minimized. That is why I used full recovery
> model. During tests, in about 5 weeks time, the Transaction Log grew up to
> nearly 7 GB (the data file currently is just over 1 GB). I tried my best
to
> shrink the Transaction Log without using the "Backup Log with no_log"
> command, but to no success. Could you offer any advice or alternative
> solution.
>
> Best regards,
>
> Sezgin Rafet
>
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> > You should not use the no_log command if you require point in time
> recovery,
> > and if you do not require point in time recovery, set your recovery mode
> to
> > simple. I would advise not to be shrinking your log or data file like
you
> > are as if the db or log has to autogrow it costs you at the point it
needs
> > to allocate more space and that is typically when your db is busy.
> >
> > HTH
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> > news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > > We have a SQL Server 2000 SP3 database with recovery model set to
full.
> I
> > > set up 3 automatic backup jobs as follows:
> > >
> > > Each day,Mon thru Fri at 9:00 am
> > > 1 step - full backup of database to file A with overwrite
> > >
> > > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > > 1 step - transaction log backup of database to file A with append
> > >
> > > Each day,Mon thru Fri at 5:05 pm
> > > 1st step - transaction log backup of database to file A with append
> > > 2nd step - execute BACKUP LOG WITH NO_LOG
> > > 3rd step - execute DBCC SHRINK (db_file_name,1)
> > >
> > > First I tried to shrink the Log file without issuing the "BACKUP LOG
> WITH
> > > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
> > even
> > > though I back up the Log.
> > >
> > >
> > > When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> > > However, I get an error in the Event Viever(Application Log) stating
> > > "Database Log truncated".
> > >
> > > My question is:
> > > Should I worry about this error ?
> > > Is any data loss likely will this arrangement ?
> > >
> > > After all, I truncate the Transaction Log on purpose, right after
> backing
> > it
> > > back in the previous step.
> > >
> > > Regards,
> > >
> > > Sezgin
> > >
> > >
> > >
> > >
> >
> >
>|||Thanks Wayne,
I used DBCC SHRINKFILE(db_log_file_name,1) , just have mistyped it in the
post.
Sezgin Rafet
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23G0pjgIyDHA.2440@.TK2MSFTNGP12.phx.gbl...
> THe log should eventually shrink, and you should NOT be using backup log
> with no_log...
> I noticed however that you said you were using dbcc shrink...
> The proper command is dbcc shrinkfile, or dbcc shrikdatabase... Could that
> be part of the problem?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> news:uvz5lIGyDHA.2308@.TK2MSFTNGP11.phx.gbl...
> > Thanks for your reply Ray,
> >
> >
> >
> > The database has a high number of transactions every day. I have to make
> > sure any possible data loss is minimized. That is why I used full
recovery
> > model. During tests, in about 5 weeks time, the Transaction Log grew up
to
> > nearly 7 GB (the data file currently is just over 1 GB). I tried my best
> to
> > shrink the Transaction Log without using the "Backup Log with no_log"
> > command, but to no success. Could you offer any advice or alternative
> > solution.
> >
> >
> >
> > Best regards,
> >
> >
> >
> > Sezgin Rafet
> >
> >
> > "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> > news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> > > You should not use the no_log command if you require point in time
> > recovery,
> > > and if you do not require point in time recovery, set your recovery
mode
> > to
> > > simple. I would advise not to be shrinking your log or data file like
> you
> > > are as if the db or log has to autogrow it costs you at the point it
> needs
> > > to allocate more space and that is typically when your db is busy.
> > >
> > > HTH
> > >
> > > --
> > > Ray Higdon MCSE, MCDBA, CCNA
> > > --
> > > "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> > > news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > > > We have a SQL Server 2000 SP3 database with recovery model set to
> full.
> > I
> > > > set up 3 automatic backup jobs as follows:
> > > >
> > > > Each day,Mon thru Fri at 9:00 am
> > > > 1 step - full backup of database to file A with overwrite
> > > >
> > > > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > > > 1 step - transaction log backup of database to file A with append
> > > >
> > > > Each day,Mon thru Fri at 5:05 pm
> > > > 1st step - transaction log backup of database to file A with append
> > > > 2nd step - execute BACKUP LOG WITH NO_LOG
> > > > 3rd step - execute DBCC SHRINK (db_file_name,1)
> > > >
> > > > First I tried to shrink the Log file without issuing the "BACKUP LOG
> > WITH
> > > > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I
want,
> > > even
> > > > though I back up the Log.
> > > >
> > > >
> > > > When I use BACKUP LOG WITH NO_LOG everything seems to be working
OK.
> > > > However, I get an error in the Event Viever(Application Log) stating
> > > > "Database Log truncated".
> > > >
> > > > My question is:
> > > > Should I worry about this error ?
> > > > Is any data loss likely will this arrangement ?
> > > >
> > > > After all, I truncate the Transaction Log on purpose, right after
> > backing
> > > it
> > > > back in the previous step.
> > > >
> > > > Regards,
> > > >
> > > > Sezgin
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Wednesday, March 28, 2012

Reg. Log Shipping.

Reg. Log Shipping.

Reg. Log Shipping.

Monday, March 26, 2012

REG : SQL JOBS

Friday, March 23, 2012

Refresh Execution of last run status

Refresh Execution of last run status

Wednesday, March 7, 2012

Re-Establishing Maintenance Jobs?

Reducing the size of the Transaction Log.

reduce databse size

Blog Archive

About Me