skreebydba

Yesterday, I was running a health assessment for a client. They are running a weekly maintenance plan that is shrinking all of their data files. After I picked myself up off the floor, I searched the web for "Paul Randal shrink" and hit on Paul's excellent post Why you should not shrink your data files. In the post, Paul (b|t) demonstrates the effect of DBCC SHRINKDATABASE on index fragmentation. After the demo script, Paul writes, "As well as introducing index fragmentation, data file shrink also generates a lot of I/O, uses a lot of CPU and generates *loads* (emphasis Paul's) of transaction log."

This led me to ask the question, "How much is *loads*?". To find an answer, I made the following modification to Paul's script:

DECLARE @maxlsn NVARCHAR(46);
SELECT @maxlsn = CONCAT(N'0x',MAX([Current LSN])) FROM fn_dblog(NULL,NULL);

-- Shrink the database
DBCC SHRINKDATABASE ([DBMaint2008]);

SELECT SUM([Log Record Length]) AS ShrinkLogSpace
FROM fn_dblog(@maxlsn,NULL);

My additions use the table-valued function fn_dblog to return information from the transaction log.

DECLARE @maxlsn NVARCHAR(46);
SELECT @maxlsn = CONCAT(N'0x',MAX([Current LSN])) FROM fn_dblog(NULL,NULL);

The code above returns the log sequence number (LSN) of the last record in the transaction log and stores it in variable @maxlsn.

SELECT SUM([Log Record Length]) AS ShrinkLogSpace
FROM fn_dblog(@maxlsn,NULL);

This code uses @maxlsn from the first query to return all log records resulting from the DBCC SHRINKDATABASE command, and return the total log space used in bytes.  In Paul's demo script, there is 10MB of space available to shrink when DBCC SHRINKDATABASE runs.  The total log space used by the shrink is 16MB.

So, in this example, *loads* is 60% of the space you are reclaiming with the shrink operation.  Yet another reason to not shrink your data files.

Many thanks to Paul for his post.