Post reply

DBBC CHECKDB is blocking log backups

  • August 15, 2011 at 12:32 pm

    #255516

    Hi,

    We have SQL Server 2005 SP3 hosting Share Point 2007 SP2 databases. We are taking the log backups every 15 mins.

    While running the weekly DBCC CHECKDB maintenance paln job, blocking is happening. DBCC CHECKDB session blocking the log backup session.

    The DBCC CHECKDB job takes 30 mins to finish.

    Is this normal? Please advice

    Thanks

  • August 15, 2011 at 1:41 pm

    #1368806

    There's a discussion of this topic in the section titled DBCC CheckDB Causes Blocking section at

    http://www.sqlskills.com/blogs/paul/category/CHECKDB-From-Every-Angle.aspx

  • August 21, 2011 at 8:52 pm

    #1371239

    Thank you,

    I went through the link http://www.sqlskills.com/blogs/paul/category/CHECKDB-From-Every-Angle.aspx

    and it says DBCC CHECKDB won't cause blocking.

    But, In my case blocking happening only when DBCC CHECKDB runs.

    So how can we rule out that DBCC CHECKDB won't cause blocking?

    I'm using built in Maintenance plan for running DBCC CHECKDB for all database (having 1 TB size combing all database's size). Is that something I need to look at it?

    and what is the difference between using a Integrity check maintenance plan & using t-sql command DBCC CHECKDB (DB_NAME) WITH NO_INFOMSGS ??

    Please advice..

  • August 24, 2011 at 2:07 pm

    #1372812

    gmamata7 (8/21/2011)

    Thank you,

    I went through the link http://www.sqlskills.com/blogs/paul/category/CHECKDB-From-Every-Angle.aspx

    and it says DBCC CHECKDB won't cause blocking.

    But, In my case blocking happening only when DBCC CHECKDB runs.

    So how can we rule out that DBCC CHECKDB won't cause blocking?

    The article confirms that DBCC CHECKDB in and of itself will not cause any blocking, i.e. if you see blocking in your database while the DBCC is running the SPID running the command will not be a blocker in any of the blocking chains.

    That said, it can contribute to blocking indirectly due to the amount of I/O consumption the DBCC requires. The recommendation is to run DBCC during a maintenance window, or at the very least a time of generally low database utilization. Within that same buffet of DBCC CHECKDB articles on sqlskills.com there is one that talks specifically about how to break down the work effort necessary to run the command on VLDBs. That may be where you need to focus...i.e. you will likely have to say goodbye to Database Maintenance Plans and get your hands into some T-SQL coding.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply