Microsoft – SQLServer – Database \ Datafile Management – Data\Log File Shrinking

Microsoft – SQLServer – Database \ Datafile Management – Data\Log File Shrinking

Sometimes one ends with MS SQL Server Log \ Data files growing quite a bit.

This is especially true with log files.

The reasons for big log files are a bit plentiful:

  1. Un-committed Transactions
  2. Database Mirroring partner in-admissibility

Well, once the original reason why the database data\log file got so big is addressed; you may now be emboldened to shrink the data\log files.

What to do, how to address ?

You can try using dbcc shrinkfile.

Over the years, MS has done quite a bit of Engineering on it.

Some of those enhancements are brought to light by Bob Dorr.  At the time of his Blog

posting, Bob worked has a “Senior SQL Server Escalation Engineer” in the “Microsoft

Customer Service and Support (CSS) SQL Escalation Services” group.

The areas that were most relevant to the issue that I was addressing (at the time) were:

1) Increase DBA visibility.  As DBCC Shrink works it avails its status via the sys.dm_exec_requests; specifically the command column

a] DbccSpaceReclaim – Clean up deferred allocations and purge empty extents
preparing for data moves.

b] DbccFilesCompact – Move pages beyond the target to before the target and
truncate file as required.

c]  DbccLOBCompact – Compacting the LOB data

2) Progress data is also visible in the same DMV (sys.dm_exec_requests).  The

columns\attributes shown are percent_complete, estimated_completion_time,

cpu_time, total_elapsed_time.

3) The shrinkage is performed & committed in batches.  This is beneficial in terms of if

you are a bit queasy or frustrated and terminated your work, you do not lose everything.

The current unit of work is 32 pages.

As named transactions are used, you can follow along by querying the

sys.dm_tran-active_transactions view.

4) As “DBCC Shrinkfile” is a physical operation, it directly interacts with the Hardware;

specifically the Storage Sub-system.  As it moves data around, other operations are quite

impacted and so if possible suspend other I/O “aggressive” operations.

5) Also, DBCC Shrinkfile is single-threaded and non-reentrant.  That is for each database,

only a single dbcc shrinkfile operation can be on-going at any one time.

References:

1) How It Works: SQL Server 2005 DBCC Shrink* May Take Longer Than SQL Server 2000
http://blogs.msdn.com/b/psssql/archive/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s