Frank Gill gives us a demo of how much log space it takes to shrink a database file:
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:
Read on for the answer. There are legitimate reasons to shrink data files, but it comes at a very high cost.