SQL 2005 SP2 Long Running Maintenance Job

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???
LVL 20
compdigit44Asked:
Who is Participating?
 
EvilPostItConnect With a Mentor Commented:
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
 
AsheenthCommented:
Please make sure that you are having sufficient space in the hard disk.
0
 
compdigit44Author Commented:
I have over 1TB of free space!!
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
EvilPostItCommented:
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
 
compdigit44Author Commented:
Where can i find this report?
0
 
compdigit44Author Commented:
how can I tell when the last time a DB grew..

All of te DB are set to grow by 1MB and unlimited
0
 
EvilPostItCommented:
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
 
compdigit44Author Commented:
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
 
compdigit44Author Commented:
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
 
EvilPostItCommented:
What tasks are carried out in this maintenance task?
0
 
compdigit44Author Commented:
I did not build the job but it appears to update Stats if that makes sence..
0
 
EvilPostItCommented:
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
 
compdigit44Author Commented:
I did create the job and so not feel comforable changing it..

Is there anything else I could check?
0
 
EvilPostItCommented:
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
 
compdigit44Author Commented:
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
 
EvilPostItCommented:
It could, is there a reason for not changing the compatibility level?
0
 
compdigit44Author Commented:
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
 
EvilPostItCommented:
Not that I know of, would just be doing the same as you at this point ie trying google...
0
 
compdigit44Author Commented:
thanks for trying
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.