Blitz Result: Auto-Shrink Enabled or Job has Shrink Steps
Running out of disk space sucks. SQL Server can help: it can automatically shrink databases to remove unused space. However, this option has way more drawbacks than positives. This part of our SQL Server sp_Blitz script checks sys.databases to see if is_auto_shrink_on = 1 for any of the databases.
We can also create our own Agent jobs with DBCC SHRINKDATABASE or SHRINKFILE commands, too. sp_Blitz also checks for jobs with those key words in the job steps. It’s not perfect – for example, it doesn’t catch maintenance plans set up to shrink databases.
Shrinking databases is the fastest way to achieve fragmentation. SQL Server goes to the last page in the database, moves it to the first free available space, and then repeats the process again. This shuffles the deck, putting your pages out of order.
To make things worse, this is often combined with a nightly index rebuild process that puts indexes back in order again – but leaves free space behind. The very next time shrink runs, it refragments everything, and your storage is stuck on the Hamster Wheel of Death. (That’s a technical term.)
To Fix the Problem
If we’re dealing with auto-shrink, in SQL Server Management Studio, you can right-click on each database and go into its properties to turn off auto-shrink. This takes effect immediately. You can also change this setting with an ALTER DATABASE script setting AUTO_SHRINK OFF.
If we’re dealing with an Agent job that does a shrink, comment out that line of the job step.
After the change, you should see faster runtimes for index reorg/rebuild jobs and faster storage response times.