31 October, 2020

What does "SHRINK DATABASE" do ?

 My current Database Size as reported by SSMS

SSMS Option to show Database Size



The sizes of the Data and Log Files as reported by SSMS

File Sizes shown by SSMS



The sizes of the Data and Log Files as reported by Windows Explorer

DataFile Size in Windows

LogFile Size in Windows


The database is in Full Recovery Mode

Full Recovery Model configured


I run a Database Backup

Full Database Backup


I then use the SHRINK DATABASE option

Shrink Database Executed


The updated Database Size as reported by SSMS

Updated Size reported after Shrink


The updated sizes of the Data and Log Files as reported by SSMS

Update File Sizes for Data and Log


The updated sizes of the Data and Log Files as reported by Windows Explorer

Updated Data File Size in Windows

Updated Log File Size in Windows



The reported 720MB shown by SSMS actually included a 520MB Transaction Log.

On Shrinking to 625MB, 488MB is still the Transaction Log.  The Data File did  shrink by about 62MB.  The Transaction Log did shrink by about 32MB.

For this database, it was and still is the fact the the Transction Log is large.

So be careful about assuming that the "SHRINK DATABASE" option will actually Shrink your Datafile !!




No comments:

Post a Comment