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 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.
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%).
DBCC SHOWCONTIG(LargeTable1) -- 99.30% DBCC SHOWCONTIG(LargeTable2) -- 99.21%
To fix the fragmentation, rebuild both indexes. That fixes the fragmentation, but now the data file is using almost twice the space necessary.
DBCC ShowFileStats -- 3363 extents total, 1697 used (215 MB total, 106 MB free)
So, shrink the database to release the wasted space back to the OS
DBCC SHRINKDATABASE (TestingShrink, 10) -- Shrink to 10% free
That’s fixed the space issue. But now, have another look at those two indexes that were just rebuilt.
DBCC SHOWCONTIG(LargeTable1)
– Logical Scan Fragmentation ………………: 99.99%DBCC SHOWCONTIG(LargeTable2)
– Logical Scan Fragmentation ………………: 7.08%
Oops. Not exactly a desired outcome.
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.
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.
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.
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.
Paul Randal wrote a very nice post on the downsides of shrink, entitled “Turn Auto Shrink Off!” Pretty much says it all.
Caveat: 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.
Edit: Some more thoughts from Paul Randal on shrinking databases: Autoshrink. Turn it OFF!
Sample Code:
SET NOCOUNT ON GO CREATE DATABASE TestingShrink GO ALTER DATABASE TestingShrink SET RECOVERY SIMPLE GO USE TestingShrink GO Create Table LargeTable1 ( -- row size of ~700 (10 rows per page) ID BIGINT, SomeString CHAR(600), Row_ID UNIQUEIDENTIFIER, AValue NUMERIC(30,8), RandomDate DATETIME ) Create Table LargeTable2 ( -- row size of ~700 (10 rows per page) ID BIGINT, SomeString CHAR(600), Row_ID UNIQUEIDENTIFIER, AValue NUMERIC(30,8), RandomDate DATETIME ) GO -- ensuring high fragmentation CREATE CLUSTERED INDEX idx_Large1 on LargeTable1 (Row_ID) CREATE CLUSTERED INDEX idx_Large2 on LargeTable2 (Row_ID) GO DECLARE @i SMALLINT SET @i = 0 WHILE (@i<8) BEGIN ;WITH DataPopulate (RowNo, Strng,Uniqueid,Num,ADate) AS ( SELECT 1 AS RowNo, 'abc' as Strng, NewID() AS Uniqueid, rand()*856542 AS Num, DATEADD(dd, FLOOR(RAND()*75454),'1753/01/01') UNION ALL SELECT rowNo+1, 'abc' as Strng, NewID() AS Uniqueid, rand(RowNo*25411)*856542 AS Num, DATEADD(dd, FLOOR(RAND(RowNo*96322)*85454),'1753/01/01') FROM DataPopulate WHERE RowNo<10000 ) INSERT INTO LargeTable1 SELECT * FROM DataPopulate OPTION (MAXRECURSION 10000) ;WITH DataPopulate (RowNo, Strng,Uniqueid,Num,ADate) AS ( SELECT 1 AS RowNo, 'abc' as Strng, NewID() AS Uniqueid, rand()*856542 AS Num, DATEADD(dd, FLOOR(RAND()*75454),'1753/01/01') UNION ALL SELECT rowNo+1, 'abc' as Strng, NewID() AS Uniqueid, rand(RowNo*25411)*856542 AS Num, DATEADD(dd, FLOOR(RAND(RowNo*96322)*85454),'1753/01/01') FROM DataPopulate WHERE RowNo<10000 ) INSERT INTO LargeTable2 SELECT * FROM DataPopulate OPTION (MAXRECURSION 10000) SET @i = @i+1 END GO DBCC SHOWCONTIG(LargeTable1) -- 99.30% DBCC SHOWCONTIG(LargeTable2) -- 99.21% DBCC showfilestats -- 2467 extents total, 2463 used (157 MB total, 256kb free) GO -- Rebuild the indexes. This should grow the database quite a bit. Alter Index idx_Large1 on LargeTable1 rebuild Alter Index idx_Large2 on LargeTable2 rebuild go DBCC SHOWCONTIG(LargeTable1) -- 0% DBCC SHOWCONTIG(LargeTable2) -- 1% DBCC ShowFileStats -- 3363 extents total, 1697 used (215 MB total, 106 MB free) GO USE Master go DBCC SHRINKDATABASE (TestingShrink, 10) -- Shrink to 10% free go use TestingShrink GO DBCC ShowFileStats -- 1885 extents total, 1695 used (120 MB total, 12 MB free) DBCC SHOWCONTIG(LargeTable1) -- 99.99% DBCC SHOWCONTIG(LargeTable2) --7.08% GO USE master GO DROP DATABASE TestingShrink GO
For the first time, I came to know that Shrinking database will spoil fragmentation level of Indexes… Surely there is so much learn about SQL Server.
Thanks, good article.
Excellent article. Many thanks
Nice Article. Thanks
This article is well explained and elaborated. it makes a difficult subject easy to understand and follow. My congratulation!
The article is very useful and clearly advised shrinking databases importanncy. Many Thanks.
This article is fine and easy too understand..
Pingback: Stop Shrinking Your Database Files. Seriously. Now. | Brent Ozar - SQL Server DBA
Thanks, you made it really simple
Very good article, it helps me a lot. Thank you very much!
Does running DBCC INDEXDEFRAG after the shrink bring the indexes’ contig % back to what they were immediately after the index rebuild?
Typically, yes, but it may grow the file again. Bear in mind that indexdefrag (alter index .. reorganise on SQL 2005+) is recommended for indexes with low fragmentation (10-30%) with rebuild recommended for indexes with higher fragmentations.
really nice article, it make sense shrinking is not feasible after index Rebuild
Auto-shrink is default off. Just logically thinking auto-growth is default on, definitely auto-shrink should be off. You should index defrag no matter you shrink or not. There are times shrink makes huge sense especially when you see the sapce removed is not coming back. Run index defrag after shrink, not before shrink. When I shrink, depending on scenario, I always shrink to extra 20% (automatic script generation) and I always know it is not the space that going to immediately grow back. Run shrink before index rebuild/reorg. When you have unlimited space, you don’t even need to shrink. We lease space so I am saving company money by maintaining a good balance.
Any idea why SQL can’t reuse the space used by tables that were created then dropped and we have to shrink the DB instead?
SQL can and does reuse the space that was made available by dropping tables.
What it won’t do automatically is release that space to the OS. That’s what shrinking does, and you don’t have to do it, in most cases you shouldn’t do it.
Interesting. I have a db where tables are created and deleted very frequently – like every minute or two. DB size kept growing. 60 GB db with 20 GB free space, yet the OS file size kept increasing (as did the free space). I’ll be blogging about it in a couple weeks.
Could be that the ghost cleanup’s not keeping up with the deallocation of the pages. Drops complete immediatly, pages are deallocated in the background afterwards.
Some stuff on Paul Randal’s blog on ghost cleanup.
Yeah, don’t think it’s that. Used his script and don’t see any ghost processing going on. Doing some more digging. Narrowed the growth down to a couple of filegroups which only contain indexes – and not ones on the created/dropped tables. Seeing some highly fragmented indexes there but not sure why that would cause file growth with empty space. Pages are fragmented, but pretty full. Will have to dig some more. Thanks for your help!
Maybe take this to a a forum (like SSC)? Blog comments not really the place to help out on a complex problem
so what do you recommend, for keeping the DB as small as possible? I have to restore a 40 GB big Database and it takes more than one hour š (i even compressed the bak File).
I thought shrinking the data Files before i make my Backup, would speed up the restore afterwards!
thanks in advance
I don’t recommend keeping databases as small as possible, it’s inefficient causing SQL to grow DBs more often than it should and has a whole set of issues.
If a 40GB backup takes an hour, I suggest checking the performance and throughput of the IO subsystem, I have before backed a 1TB database up in 10 hours uncompressed, 4 hours compressed.
Shrinking the DB before backup won’t reduce the size of the backup or the time the backup takes, because a backup does not include empty space within the data file.
Have you considered a 3rd party tool that creates compressed backups?
Hi Gail,
wow thanks for the fast reply š
No i didnĀ“t consider using a 3rd party tool that creates compresed backups, because i designed 3 Maintenance Plans doing FULL/DIFF/TRN backups.
I really have to check the performance while doing the restore …. because (as i said) the bak File is compressed and should be fast, there should be something wiht the Server.
I am going to check the Performance and write back.
Thanks for the Feedback!
greetings
GONZI
Very good article..
Yes i do accept on this . But How do we free space after removing crores of data from a table and free the unused space. Please let me have a clear picture on that.
Regards,
Krishnan
Why do you want to remove free space?
not really related but a clustered index on a unique identifier isn’t ideal when writing to the table. But as this is a test database I guess it doesn’t matter
Hi, I once got a tip to set up a maintenance plan to run a Shink Database task whith the āRetain space in database filesā option (NOTRUNCATE), followed by a Rebuild index task that is then followed by a update stats task.
Will this not then compact the database, eliminating hollow space ( I know this will fragment the indexes) but by then running the Rebuild index job correcting the pointers of the indexes?
Then ending up whit a less fragmented database file and at the same time not ha fragmented indexes?
That’s a huge amount of resources wasted to do very little. Lots of CPU and IO to do the shrink, then you’ll need to rebuild every single index, another massive amount of IO and CPU, huge logs, long delays if there’s log shipping or mirroring. Better to just rebuild the indexes that need rebuilding and not waste time first fragmenting all the indexes then defragmenting them all.
PS: it is not to reclaim space for the OS, just to compact the content of the mdf file.
Pingback: Shrink a log fileā¦AUTOMATICALLY? | The MidnightDBA Star-Times
Pingback: Shrink SQL Server Databases for Mirroring & Availability Groups
It really comes in very handy when migrating database from one host to another. Thanks !