Shrink database operation in maintenance plan failed

See updates below

I have a nightly DB maintenance plan and it's causing some strange behaviour.

About 1 out of 10 runs it causes the DB to not response to queries. It takes about 5 minutes for the DBM plan to complete at which point it starts responding.

The error logs don't seem to point to any problem at all:

```
2013-01-13 00:00:56.73 spid23s This instance of SQL Server has been using a process ID of 1488 since 12/28/2012 10:06:36 AM (local) 12/28/2012 6:06:36 PM (UTC). This is an informational message only; no user action is required.
2013-01-13 22:00:06.07 spid60 Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.
2013-01-13 22:00:06.07 spid60 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2013-01-13 22:01:03.78 spid64 DBCC CHECKDB (RackAttackNorthAmerica) WITH no_infomsgs executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 57 seconds. Internal database snapshot has split point LSN = 0000942b:000000d9:0001 and first LSN = 0000942b:000000d8:0001.
2013-01-13 22:02:33.13 spid80 I/O is frozen on database model. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2013-01-13 22:02:33.13 spid81 I/O is frozen on database msdb. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2013-01-13 22:02:33.13 spid82 I/O is frozen on database RackAttackNorthAmerica. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2013-01-13 22:02:34.13 spid83 I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2013-01-13 22:02:34.48 spid83 I/O was resumed on database master. No user action is required.
2013-01-13 22:02:34.48 spid80 I/O was resumed on database model. No user action is requir

What you're seeing is blocking as a result of the DBCC SHRINKDB operation. That is causing your database not to respond. Shrink operations move your data pages around on disk in order to create space within the data files to shrink. As a result, the engine must take either page or table locks in order to move the appropriate data pages. While these should be short term, they can block queries.

Additionally, shrink operations are file manipulations (just as adding, modifying, or removing data files) and can not run while a backup of any sort is executing. This is why you see your other error regarding file commands must be serialized.

To observe the blocking, you can run sp_who2 (or better yet, sp_whoisactive) while your DBCC SHRINKDB is running. Identify the spid the command is running under and you should see it causing blocking (if there's enough activity).

Unfortunately, there's no way to avoid the blocking. This, along with other reasons, is why you should not perform any sort of automated shrink operation. The best plan for resolution is to remove any "Shrink Database" tasks from your maintenance plans.

Best practices are to avoid shrinking databases whenever possible, per Paul Randall's excellent article that was linked by JohnS. If you must shrink your files, you should use the following rules of thumb:

• Perform shrinking manually so you can monitor it's progress and manage as necessary. If it is interrupting user processes, you can kill the shrink with minimal impact. Interrupting a shrink will not "rollback" any of the page movement and you will retain any progress the shrink has already made.

• Keep your shrink increments to small values so as to minimize page movement and keep your maintenance/shrink period manageable.

StackExchange Database Administrators Q#32905, answer score: 10