Read more: http://www.blogsmonetize.com/2010/10/how-to-use-syntax-highlighter-3083-in.html#ixzz1DHzvEgBA

Wednesday, March 23, 2011

DBCC CHECK* would cause blocking

DBCC CHECK* would never cause a blocking as early days.
SQL Server 2005 ownwards, DBCC CHECK* works on snapshot.DBCC CHECK* will not cause any blocking on concurrency as user process is never going to affect by DBCC and vice versa.However, as long as we do DBCC operation on Snapshot, SQL server has to preserve some space for snapshot in the database server.If we do not have enough space then DBCC command would fail. Alternative is to run DBCC against a table, so that SQL server only needs to preserve space for table. However again we will not be able to perform all check apart from the table level information. There is an option to use the TABLOCK keyword which is kind of old way of doing DBCC which is not based on snapshot instead on table lock. This would cause issues with concurrency.

No comments: