compdigit44
asked on
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???
-No Errors in the SQL or WIndows logs
-No AV
Any thoughts???
Please make sure that you are having sufficient space in the hard disk.
ASKER
I have over 1TB of free space!!
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.
ASKER
Where can i find this report?
ASKER
how can I tell when the last time a DB grew..
All of te DB are set to grow by 1MB and unlimited
All of te DB are set to grow by 1MB and unlimited
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Yeah you will want to change that 1mb to something bigger. The previously mentioned report will show you the date & time of the autogrowth.
ASKER
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.
It states it cannot run the report becuase the DB is in 2000 mode.
ASKER
OK I was able todo some more poking around and my DB did not grow significaly before my maintenace job ran.
Any more idea?
Any more idea?
What tasks are carried out in this maintenance task?
ASKER
I did not build the job but it appears to update Stats if that makes sence..
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.
You could update the maintenance plan to include a text report. It will make it easier to find out exactly what is being done.
ASKER
I did create the job and so not feel comforable changing it..
Is there anything else I could check?
Is there anything else I could check?
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.
ASKER
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??
Could the fact that my sql 2005 DB is running in 2000 mode cause these random slow jobs??
It could, is there a reason for not changing the compatibility level?
ASKER
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?
Are there any articles you know of that talk about performance problem with 2000 mode?
Not that I know of, would just be doing the same as you at this point ie trying google...
ASKER
thanks for trying