Troubleshoot Maintenance Plan (backup) Long Running

• _UB

  SSCrazy

  Points: 2987

  More actions

  April 7, 2011 at 2:19 pm

  #145070

  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

  SqlTimes Blog on Sql Server

• kladibeeto

  SSCommitted

  Points: 1582

  More actions

  April 7, 2011 at 5:13 pm

  #1308898

  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)

• Adiga

  One Orange Chip

  Points: 27284

  More actions

  April 10, 2011 at 2:10 am

  #1309592

  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