{"id":27,"date":"2007-09-08T22:46:50","date_gmt":"2007-09-08T20:46:50","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=27"},"modified":"2013-05-27T16:50:41","modified_gmt":"2013-05-27T14:50:41","slug":"shrinking-databases","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2007\/09\/08\/shrinking-databases\/","title":{"rendered":"Shrinking databases"},"content":{"rendered":"

Or “Order the pages, shuffle the pages.<\/em>”<\/p>\n

Do you ever shrink your data files? I’ve personally never been fond of it, especially for production databases. After all, they’ll simply have to grow again and, especially if the data files are on independent drives, there’s little difference between space free on the drive or space free in the data file. There is also a more insidious reason for not shrinking a database.<\/p>\n

Let’s take a very simple database (The creation code is at the end of the post). I have two tables, both with a tens of thousands of rows. Both tables have a clustered index on a uniqueidentifier and are heavily fragmented (>99%).<\/p>\n

DBCC SHOWCONTIG(LargeTable1) -- 99.30%\r\nDBCC SHOWCONTIG(LargeTable2) -- 99.21%<\/pre>\n
To fix the fragmentation, rebuild both indexes. That fixes the fragmentation, but now the data file is using almost twice the space necessary.<\/p>\n
DBCC ShowFileStats -- 3363 extents total, 1697 used (215 MB total, 106 MB free)<\/pre>\n
So, shrink the database to release the wasted space back to the OS<\/p>\n
DBCC SHRINKDATABASE (TestingShrink, 10) -- Shrink to 10% free<\/pre>\n
That’s fixed the space issue. But now, have another look at those two indexes that were just rebuilt.<\/p>\n

\nDBCC SHOWCONTIG(LargeTable1)
\n– Logical Scan Fragmentation ………………: 99.99%<\/p>\n
DBCC SHOWCONTIG(LargeTable2)
\n– Logical Scan Fragmentation ………………: 7.08%
\nOops. Not exactly a desired outcome.<\/p><\/blockquote>\n
When SQL shrinks a data file, it takes extents that are towards the end of the file and moves them to empty places further forward. It does this with no concern over logical order of pages or indexes. Net result, after shrinking a database, many of the indexes in that database will be badly fragmented.<\/p>\n
For this reason mainly I always recommend that, especially for production databases, the data files get grown as necessary and not shrunk. The space that can be reclaimed from the data file is not worth what the shrink does to page ordering. Especially since, as production databases tend to do, the file will simply be growing again sometime in the future.<\/p>\n
All too often I hear of maintenance plans that first rebuild all the indexes, then shrink the data files. That kind of maintenance is worse than useless. The index rebuild uses cpu and time to arrange indexes in logical order and in the process often grows the data file. The shrink then uses more time and cpu and often will leave the indexes more fragmented than they were before the rebuild.<\/p>\n
Basically, if you’re going to rebuild indexes, don’t shrink the data files. If you’re going to shrink data files, either don’t waste time rebuilding indexes, or do them after the shrink.<\/p>\n
Paul Randal<\/a> wrote a very nice post on the downsides of shrink, entitled “Turn Auto Shrink Off!<\/a>” Pretty much says it all.<\/p>\n
Caveat<\/span>: There are cases where shrinking data files does make sense. When a process created lots of tables for processing then dropped them again, after a massive archiving job, after changing data types in a table to release a large amount of wasted space (more on that another time). Just be aware of the effect of a shrink on the fragmentation of indexes.<\/p>\n
Edit: Some more thoughts from Paul Randal on shrinking databases: Autoshrink. Turn it OFF!<\/a><\/p>\n
Sample Code:<\/p>\n
SET NOCOUNT ON\r\nGO\r\n\r\nCREATE DATABASE TestingShrink\r\nGO\r\n\r\nALTER DATABASE TestingShrink SET RECOVERY SIMPLE\r\nGO\r\n\r\nUSE TestingShrink\r\nGO\r\n\r\nCreate Table LargeTable1 ( -- row size of ~700 (10 rows per page)\r\nID BIGINT,\r\nSomeString CHAR(600),\r\nRow_ID UNIQUEIDENTIFIER,\r\nAValue NUMERIC(30,8),\r\nRandomDate DATETIME\r\n)\r\n\r\nCreate Table LargeTable2 ( -- row size of ~700 (10 rows per page)\r\nID BIGINT,\r\nSomeString CHAR(600),\r\nRow_ID UNIQUEIDENTIFIER,\r\nAValue NUMERIC(30,8),\r\nRandomDate DATETIME\r\n)\r\nGO\r\n\r\n-- ensuring high fragmentation\r\nCREATE CLUSTERED INDEX idx_Large1 on LargeTable1 (Row_ID)\r\nCREATE CLUSTERED INDEX idx_Large2 on LargeTable2 (Row_ID)\r\nGO\r\n\r\nDECLARE @i SMALLINT\r\nSET @i = 0\r\nWHILE (@i&lt;8)\r\nBEGIN\r\n;WITH DataPopulate (RowNo, Strng,Uniqueid,Num,ADate) AS (\r\nSELECT 1 AS RowNo, 'abc' as Strng, NewID() AS Uniqueid, rand()*856542 AS Num, DATEADD(dd, FLOOR(RAND()*75454),'1753\/01\/01')\r\nUNION ALL\r\nSELECT rowNo+1, 'abc' as Strng, NewID() AS Uniqueid, rand(RowNo*25411)*856542 AS Num, DATEADD(dd, FLOOR(RAND(RowNo*96322)*85454),'1753\/01\/01')\r\nFROM DataPopulate WHERE RowNo<10000\r\n)\r\nINSERT INTO LargeTable1\r\nSELECT * FROM DataPopulate\r\nOPTION (MAXRECURSION 10000)\r\n\r\n;WITH DataPopulate (RowNo, Strng,Uniqueid,Num,ADate) AS (\r\nSELECT 1 AS RowNo, 'abc' as Strng, NewID() AS Uniqueid, rand()*856542 AS Num, DATEADD(dd, FLOOR(RAND()*75454),'1753\/01\/01')\r\nUNION ALL\r\nSELECT rowNo+1, 'abc' as Strng, NewID() AS Uniqueid, rand(RowNo*25411)*856542 AS Num, DATEADD(dd, FLOOR(RAND(RowNo*96322)*85454),'1753\/01\/01')\r\nFROM DataPopulate WHERE RowNo<10000\r\n)\r\nINSERT INTO LargeTable2\r\nSELECT * FROM DataPopulate\r\nOPTION (MAXRECURSION 10000)\r\nSET @i = @i+1\r\nEND\r\nGO\r\n\r\nDBCC SHOWCONTIG(LargeTable1) -- 99.30%\r\nDBCC SHOWCONTIG(LargeTable2) -- 99.21%\r\nDBCC showfilestats -- 2467 extents total, 2463 used (157 MB total, 256kb free)\r\nGO\r\n-- Rebuild the indexes. This should grow the database quite a bit.\r\nAlter Index idx_Large1 on LargeTable1 rebuild\r\nAlter Index idx_Large2 on LargeTable2 rebuild\r\ngo\r\n\r\nDBCC SHOWCONTIG(LargeTable1) -- 0%\r\nDBCC SHOWCONTIG(LargeTable2) -- 1%\r\nDBCC ShowFileStats -- 3363 extents total, 1697 used (215 MB total, 106 MB free)\r\nGO\r\n\r\nUSE Master\r\ngo\r\nDBCC SHRINKDATABASE (TestingShrink, 10) -- Shrink to 10% free\r\ngo\r\nuse TestingShrink\r\nGO\r\n\r\nDBCC ShowFileStats -- 1885 extents total, 1695 used (120 MB total, 12 MB free)\r\nDBCC SHOWCONTIG(LargeTable1) -- 99.99%\r\nDBCC SHOWCONTIG(LargeTable2) --7.08%\r\nGO\r\n\r\nUSE master\r\nGO\r\n\r\nDROP DATABASE TestingShrink\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Or “Order the pages, shuffle the pages.” Do you ever shrink your data files? I’ve personally never been fond of it, especially for production databases. After all, they’ll simply have to grow again and, especially if the data files are… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[20,15],"tags":[],"class_list":["post-27","post","type-post","status-publish","format-standard","hentry","category-admin","category-sql-server"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-r","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/27","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=27"}],"version-history":[{"count":2,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/27\/revisions"}],"predecessor-version":[{"id":1431,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/27\/revisions\/1431"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=27"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=27"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=27"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}