DB options: – Auto Shrink = OFF

The use of AUTO SHRINK I think needs no explanation. Almost anybody who has worked on SQL Server understands the way databases can quickly grow. In these cases we can see growth happens in two places, the database file and the log file. The database file gets highly fragmented when it is shrunk. The log file being sequential doesn’t suffer from this issue. Therefore it is ok to shrink the log file when needed but data files should not be shrink unless you’re trying to recover space as a last resort or empty a file. Having understood what should and should not be shrunk we next explore if this should be setup as a database option.

The quick answer is NO. We have no control over when the automatic shrink will occur and because shrinking causes massive amounts of IO, we encounter performance issues. Shrinking can also run for long duration and cause locks making things worse. Shrinking the database is something we should have full control over since it causes performances issues. Shrinking the log file is acceptable since its write heavy and sequential so we do not encounter any performance issues on the Log file. However the database file rearranges pages by moving pages from the end of the file to empty slots in between. Consider the below table

T-0 – INSERT Rows

1

2

3

4

5

6

Operation DML

 

Delete

 

Delete

  

T-1

1

NULL

3

NULL

5

6

Operation SHRINK

 

5 to 2

 

6 to 4

 

Empty

T-2

1

5

3

6

FREED TO OS

FREED TO OS

 

As we can see that by T-2 the order is 1,5,3,6 and the index is now fragmented and no longer suited to efficient scans etc. So we can now agree that shrinking the MDF and NDF files is highly discouraged because it causes performance issues while and even after running. Shrinking the log file is acceptable because in Full Recovery model as the operations are fully logged the size of the transaction log file will grow quickly for certain one-time operations like ETL package loads etc. This space needs to be reclaimed since it’s unlikely the database will encounter similar ETL operations in the near future.

In summary shrinking needs to be manually triggered by the DBA during off peak hours and only for the T-log. Everything else is only to be performed under rare cases and usually as a last resort.