SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – Troubleshoot DBCC SHRINKDATABASE slow performance

18 November,2012 by Jack Vamvas

When observing  a DBCC DHRINKDATABASE on the DMV sys.dm_exec_requests  command column , you’ll notice different commands appearing.

Step 1 – DbccSpaceReclaim   - Prepares for data moves by cleaning up space –e.g deferred allocations and manage empty extents

Step 2 – DbccFileCompact - Adjusting pages beyond the target to before the target.

Step 3 – DbccLOBCompact - LOB data compact

 Sometimes it may appear the command is taking a long time. Some reasons may be:

1) If a heap is moved all Non Clustered index physical link references to a record number on a data page need to be updated. If there are large NonClustered Indexes , the Query Processor  needs to process all the updates.

2)  Analyse for other processes blocking the lock required to move the page SQL Server – Current queries executing on SQL Server

3)  Analyse IO subsystem

Read More

SQL Server – How to Predict the DBCC SHRINKDATABASE finish time

SQL Server - Monitoring a Rollback and sys.dm_exec_requests ...

SQL Server Storage and IO performance - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer