0

I'm a fairly new SQL Server DBA but come from a software engineering background. I've been reading on several blogs that shrinking a database on a regular basis is bad and I understand why. The issue I'm having is that a database maintenance plan that was in place before I started working is failing. There is only 1 step in the plan and it includes a check database integrity task, rebuild index task, update statistics task and lastly a shrink database task. This plan is scheduled to run every Wednesday and Sunday at 10pm.

I need some suggestions on how to further identify where the issue is and wondered if the shrink database task may be causing the problems.

Thank you for your patience in answering this question from a newbie.

user1777238
  • 1
  • Unfortunately, this question is probably too broad for this site and/or the specific question you're asking doesn't include enough detail to help you. Post the specific error you get in your SQL server logs, please. – HopelessN00b Oct 26 '12 at 14:39

1 Answers1

2

Right-clicking on the maintenance plan and selecting View History should provide some meaningful information. You should at least be able to infer which step is failing.

A Shrink task is guaranteed to fail if there isn't enough space. It can also be blocked by commands that execute at a specific isolation level. More information here:

DBCC SHRINKDATABASE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms190488.aspx

enter image description here

Greg Askew
  • 35,880
  • 5
  • 54
  • 82