{"id":1130,"date":"2007-11-13T04:28:41","date_gmt":"2007-11-13T04:28:41","guid":{"rendered":"\/blogs\/paul\/post\/Auto-shrink-e28093-turn-it-OFF!.aspx"},"modified":"2017-06-03T08:22:01","modified_gmt":"2017-06-03T15:22:01","slug":"auto-shrink-turn-it-off","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/auto-shrink-turn-it-off\/","title":{"rendered":"Auto-shrink \u2013 turn it OFF!"},"content":{"rendered":"

I\u2019m in the middle of a flight from Washington D.C. to Zurich on the way to Barcelona for TechEd IT Forum<\/a> and I can\u2019t sleep \u2013 Kimberly\u2019s out like a light so what else is there to do except write another blog post?<\/em> :-)OK – actually posting this from Barcelona on Tuesday before our first of 12 sessions here!<\/em><\/p>\n

In the Database Maintenance workshop we did at SQL Connections last week I promised to blog a script I used to show how data file shrink operations cause massive fragmentation of indexes. The reason is that data file shrink starts at the end of the data file and moves a single page at a time to a free space below the shrink threshold. In the process of doing so, it perfectly reverses the physical order of the pages comprising the leaf level of an index \u2013 thus perfectly fragmenting it!!<\/p>\n

Let\u2019s try out my simple script that demonstrates this. First thing I\u2019m going to do is create a new database and create a 10MB \u2018filler\u2019 table, which I\u2019m going to then drop later to create a space that shrink can use.<\/p>\n

\r\nUSE MASTER;\r\nGO\r\n \r\nIF DATABASEPROPERTYEX ('shrinktest', 'Version') > 0\r\n      DROP DATABASE shrinktest;\r\n \r\nCREATE DATABASE shrinktest;\r\nGO\r\nUSE shrinktest;\r\nGO\r\n \r\nSET NOCOUNT ON;\r\nGO\r\n \r\n-- Create and fill the filler table\r\nCREATE TABLE filler (c1 INT IDENTITY, c2 VARCHAR(8000))\r\nGO\r\nDECLARE @a INT;\r\nSELECT @a = 1;\r\nWHILE (@a < 1280) -- insert 10MB\r\nBEGIN\r\n      INSERT INTO filler VALUES (REPLICATE ('a', 5000));\r\n      SELECT @a = @a + 1;\r\nEND;\r\nGO\r\n<\/pre>\n
Next I\u2019ll create the \u2018production\u2019 table that we\u2019d really like to keep in optimal shape for performance:<\/p>\n
\r\n-- Create and fill the production table\r\nCREATE TABLE production (c1 INT IDENTITY, c2 VARCHAR (8000));\r\nCREATE CLUSTERED INDEX prod_cl ON production (c1);\r\nGO\r\nDECLARE @a INT;\r\nSELECT @a = 1;\r\nWHILE (@a < 1280) -- insert 10MB\r\nBEGIN\r\n      INSERT INTO production VALUES (REPLICATE ('a', 5000));\r\n      SELECT @a = @a + 1;\r\nEND;\r\nGO \r\n<\/pre>\n
Now I\u2019ll use the sys.dm_db_index_physical_stats<\/em> DMV to check the fragmentation of the production table\u2019s clustered index \u2013 it should be almost zero:<\/p>\n
\r\n-- check the fragmentation of the production table\r\nSELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (\r\n      DB_ID ('shrinktest'), OBJECT_ID ('production'), 1, NULL, 'LIMITED');\r\nGO\r\n<\/pre>\n
\r\navg_fragmentation_in_percent\r\n----------------------------\r\n0.390930414386239\r\n<\/pre>\n
This is what I expected. Now I\u2019m going to drop the filler table, run a shrink operation (auto-shrink, DBCC SHRINKDATABASE<\/em> and DBCC SHRINKFILE<\/em> all use the exact same code under the covers) and then check the fragmentation again:<\/p>\n
\r\n-- drop the filler table and shrink the database\r\nDROP TABLE filler;\r\nGO\r\n \r\n-- shrink the database\r\nDBCC SHRINKDATABASE (shrinktest);\r\nGO\r\n \r\n-- check the index fragmentation again\r\nSELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (\r\n      DB_ID ('shrinktest'), OBJECT_ID ('production'), 1, NULL, 'LIMITED');\r\nGO\r\n<\/pre>\n
\r\navg_fragmentation_in_percent\r\n\r\n----------------------------\r\n99.7654417513683\r\n<\/pre>\n
Wow! The index went from almost 0% fragmented to almost 100% fragmented \u2013 the shrink operation totally reversed the physical ordering of the leaf level of the clustered index \u2013 nasty.<\/p>\n
One of the common maintenance operations I see at customer sites is to run a database shrink at some interval, and I always advise against it \u2013 now you can see why. Running a regular shrink operation can cause horrible fragmentation problems. The worst problems I see are those customers with maintenance plans that rebuild all indexes and then run a shrink to remove the extra space necessary for the index rebuilds \u2013 completely undoing the effects of the index rebuild!<\/p>\n
One other common thing I see is to have auto-shrink set on for one or databases. This is bad for several reasons:<\/p>\n