April 7, 2011 at 2:19 pm
Hi,
Sql Server 2005 SP2
Small databases
I have a maintenance plan to perform CHECKDB, Reindex, Backup, Cleanup tasks in that order. Lately, its been running very very long. For more than 2 days.
Initially it was because of a bunch of reasons i.e. disk ran out of space, TRN and BAK jobs running on top of each other, etc.
But now, I created a new plan with just backup. Even that runs forever. Just simple backup, nothing else (small database).
Now, if I create a job with T-Sql command and run it, it runs fast in under a second. But if I create a Maintenance Plan on the same database it takes forever.
How do I troubleshoot this? I do not get any errors because, I stop the job after running for a few hours.
Any ideas,
_To
April 7, 2011 at 5:13 pm
Hi,
For the beginning maybe you should check what is hapenning with that process.
You have plenty of solutions for that (activity monitor,DMV,etc.).
My advice:
--Run job again.
-- use master..sysprocesses
select *
from master..sysprocesses
where kpid <> 0 and spid > 50
-- find your job and post results here (wait_type,wait_resource,blocked,cmd)
April 10, 2011 at 2:10 am
Execute the following query while the Maintenance Plan is running and check if there are any blocks or anything that the process is waiting on.
SELECT * FROM sys.dm_exec_requests
Also since the instance is running on SP2, it would be a good idea to upgrade to SP3 or above especially from Maintenance Plan perspective.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply