Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL 2005 SP2 Long Running Maintenance Job

Posted on 2010-11-12
19
Medium Priority
?
269 Views
Last Modified: 2012-05-10
I have a 64bit version of SQL 2005 SP2 running on Windows 2008 Enterprise Edition. My DB is running in 2000 mode due to compaitbly issues with the application it host. Anyway on average my maintenace job take 30 minutes to run but every now and then it will take 1 to 3 hours to complete for no reason.

-No Errors in the SQL or WIndows logs
-No AV

Any thoughts???
0
Comment
Question by:compdigit44
  • 10
  • 8
19 Comments
 

Expert Comment

by:Asheenth
ID: 34120447
Please make sure that you are having sufficient space in the hard disk.
0
 
LVL 20

Author Comment

by:compdigit44
ID: 34120617
I have over 1TB of free space!!
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34120873
Have a look at the growth settings of the database and also the when the database has grown. You can see this at the bottom of the database disk usage report.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 20

Author Comment

by:compdigit44
ID: 34121124
Where can i find this report?
0
 
LVL 20

Author Comment

by:compdigit44
ID: 34121192
how can I tell when the last time a DB grew..

All of te DB are set to grow by 1MB and unlimited
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 2000 total points
ID: 34121196
In management studio right click on your database goto Reports > Standard Reports > Disk Usage.

At the bottom there is a table to expand to show you the filegrowths. There is also a column to show you the time it took to grow the datafile on milliseconds.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34121222
Yeah you will want to change that 1mb to something bigger. The previously mentioned report will show you the date & time of the autogrowth.
0
 
LVL 20

Author Comment

by:compdigit44
ID: 34121630
I just got an error when tryinh to run this report..
It states it cannot run the report becuase the DB is in 2000 mode.
0
 
LVL 20

Author Comment

by:compdigit44
ID: 34121729
OK I was able todo some more poking around and my DB did not grow significaly before my maintenace job ran.

Any more idea?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34121745
What tasks are carried out in this maintenance task?
0
 
LVL 20

Author Comment

by:compdigit44
ID: 34121833
I did not build the job but it appears to update Stats if that makes sence..
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34122906
Does it do anything else?

You could update the maintenance plan to include a text report. It will make it easier to find out exactly what is being done.
0
 
LVL 20

Author Comment

by:compdigit44
ID: 34122919
I did create the job and so not feel comforable changing it..

Is there anything else I could check?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34122977
Im not sure why this would be taking so long if it were only rebuilding stats and there has been no database growth. This is why it would be helpful to see an output with what happened at certain times.
0
 
LVL 20

Author Comment

by:compdigit44
ID: 34123028
Besides doing this is there anything else I can check

Could the fact that my sql 2005 DB is running in 2000 mode cause these random slow jobs??
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34123077
It could, is there a reason for not changing the compatibility level?
0
 
LVL 20

Author Comment

by:compdigit44
ID: 34123095
yes the application that uses the DB will only support running in 2000 mode

Are there any articles you know of that talk about performance problem with 2000 mode?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34123444
Not that I know of, would just be doing the same as you at this point ie trying google...
0
 
LVL 20

Author Comment

by:compdigit44
ID: 34123581
thanks for trying
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A safe way to clean winsxs folder from your windows server 2008 R2 editions
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question