reduce databse size: execution

Showing posts with label execution. Show all posts

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

Reduction of Query Execution Time of Larger Database in SQL

I am having a database in SQL which is of size 6GB. My application will be
logging the data into database, which makes increases the size of
database.(currently it is growing by 10%).
I am running a query againest the database, which refers many tables, which
is taking around 5 Minutes. I am having many such queries which i use to
generate the reports. Now i want to reduce the Query Execution time, without
deleting the data. I am thinking of the below 4 possibilities:
1) Indexing has to be introduced/modified.
2.) We may restrict the database growth by specifying the size (currently it
is growing by 10%).
3.) Moving the inline sql to the database level as stored procedures
4. )Breaking the large and complex queries into fragments (Needs some
database expert
Which of the above options is better. Is there is any other way to reduce
the Query Execution time?> 1) Indexing has to be introduced/modified.
You can never have efficient queries on tables of that size without
proper indexing. This is a must.

> 2.) We may restrict the database growth by specifying the size (currently
> it
> is growing by 10%).
What are you going to do about new rows? You can't simply stop growth by
setting a limit on the size. Well you can but you will get errors when you
attempt to insert new rows after you reached that size limit.

> 3.) Moving the inline sql to the database level as stored procedures
That is always a good idea.

> 4. )Breaking the large and complex queries into fragments (Needs some
> database expert
Hard to say. I would tackle the others first.
Andrew J. Kelly SQL MVP
"uday" <uday@.discussions.microsoft.com> wrote in message
news:90B8A1F9-07A5-45E0-BB63-605BB4F86634@.microsoft.com...
>I am having a database in SQL which is of size 6GB. My application will be
> logging the data into database, which makes increases the size of
> database.(currently it is growing by 10%).
> I am running a query againest the database, which refers many tables,
> which
> is taking around 5 Minutes. I am having many such queries which i use to
> generate the reports. Now i want to reduce the Query Execution time,
> without
> deleting the data. I am thinking of the below 4 possibilities:
> 1) Indexing has to be introduced/modified.
> 2.) We may restrict the database growth by specifying the size (currently
> it
> is growing by 10%).
> 3.) Moving the inline sql to the database level as stored procedures
> 4. )Breaking the large and complex queries into fragments (Needs some
> database expert
> Which of the above options is better. Is there is any other way to reduce
> the Query Execution time?|||> 1) Indexing has to be introduced/modified.
Definitely a good idea. Use SQL Profiler and the Index Tuning Wizard on a
typical DML load.

> 2.) We may restrict the database growth by specifying the size (currently
it
> is growing by 10%).
Why? Just make sure to backup your data regularly, including frequent
Transaction Log backups.

> 3.) Moving the inline sql to the database level as stored procedures
That might seriously improve performance.

> 4. )Breaking the large and complex queries into fragments (Needs some
> database expert
Be careful not to break the ACID properties of any transaction. Read more
here:
http://msdn.microsoft.com/library/d...r />
_2it2.asp
ML|||Hi actually my database has the indexes andprimary keys. Only 1 0r 2 tables
are not having indexes , but they are having proper primary keys and foreign
keys. I only meant to modify them also. After proper indexing also it is
taking too long. For Ex one query took 25 minutes to execute today. I want t
o
reduce that execution time. So can u please say what are the other measures
which i can use.
"ML" wrote:

> Definitely a good idea. Use SQL Profiler and the Index Tuning Wizard on a
> typical DML load.
>
> Why? Just make sure to backup your data regularly, including frequent
> Transaction Log backups.
>
> That might seriously improve performance.
>
> Be careful not to break the ACID properties of any transaction. Read more
> here:
> http://msdn.microsoft.com/library/d.../>
06_2it2.asp
>
> ML|||For this we'd have to see the DDL and some sample data.
ML

Friday, March 23, 2012

Refresh Execution of last run status

Refresh Execution of last run status

Wednesday, March 7, 2012

Reduction of Query Execution Time of Larger Database in SQL

reduce databse size

Blog Archive

About Me