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.
The Check Database Integrity Task provided in the maintenance plan issue DBCC CHECKDB WITH NO_INFOMSGS on the database selected. You can view its command by clicking the view-SQL in the task setup. If you doubt the generated SQL command, you can use SQL profiler to see its SQL command. If corruption was found, the agent job with this maintenance task will generate error and fail (with proper job step setup).
One thing to point out, running DBCC CHECKDB is equal as performing DBCC CHECKALLOC, DBCC CHECKTABLE, DBCC CHECKCATALOG and other validation. If you are running DBCC CHECKDB, you do not have to run them separately. Running them separately usually is to perform specific integrity check or need to spread out the integrity check to smaller task due to limited time to perform entire DBCC CHECKDB. More information can be found here on MSDN.
Best Answer
Two ways come to mind, there may be more. I prefer the second but that's just my opinion
The first line below loads the 2012 assembly since that's the version I'm running on my laptop, adjust to suit your needs.