DB options: – Auto Shrink = OFF Why you shouldn’t shrink if possible.

By | 2020-03-01

https://youtu.be/ph070n6lLU4

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.

 

Category: Uncategorized

About admin

This post was written by Jayanth Kurup. A Microsoft SQL Server Consultant and Trainer based out of Bangalore, India. Jayanth has been working on MS SQL Server for over 15 years. He is a performance tuning and Business Intelligence expert. Having worked with companies like Microsoft, DELL, Wells Fargo, Thomson Reuters and many other fortune 100 companies. Some other technologies Jayanth works on include Microsoft Azure, PowerBI, Python and AWS. When he isn’t consulting or training, Jayanth like to travel, paint and read. He is also very active in social causes and the founder of Enabled Business Solutions. Visit his company by clicking the link in the menu or email him directly.