Shrinking databases

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%
BCC 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

23 Responses to “Shrinking databases”

  1. 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.

    Imran Mohammed said this on October 18th, 2008 at 21:30

  2. Excellent article. Many thanks

    Nick said this on October 21st, 2008 at 10:51

  3. Nice Article. Thanks

    Chintan said this on October 23rd, 2008 at 06:01

  4. This article is well explained and elaborated. it makes a difficult subject easy to understand and follow. My congratulation!

    Herbert said this on December 18th, 2008 at 14:42

  5. The article is very useful and clearly advised shrinking databases importanncy. Many Thanks.

    Japhter Edzisani Tshihatu said this on February 23rd, 2009 at 13:41

  6. This article is fine and easy too understand..

    Thulasi said this on July 29th, 2009 at 06:40

  7. [...] Gail Shaw – “Shrinking Databases” [...]

    Stop Shrinking Your Database Files. Seriously. Now. | Brent Ozar - SQL Server DBA said this on August 19th, 2009 at 22:37

  8. Thanks, you made it really simple

    zek said this on November 20th, 2009 at 03:20

  9. Very good article, it helps me a lot. Thank you very much!

    Leon said this on March 2nd, 2010 at 14:15

  10. Does running DBCC INDEXDEFRAG after the shrink bring the indexes’ contig % back to what they were immediately after the index rebuild?

    Chris said this on June 23rd, 2010 at 18:13

  11. 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.

    Gail said this on June 23rd, 2010 at 20:16

  12. really nice article, it make sense shrinking is not feasible after index Rebuild

    Fawwad said this on August 13th, 2010 at 18:58

  13. 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.

    Jason said this on September 28th, 2010 at 23:03

  14. 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?

    Shaun said this on December 27th, 2010 at 18:44

  15. 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.

    Gail said this on December 27th, 2010 at 19:00

  16. 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.

    Shaun said this on December 27th, 2010 at 22:57

  17. 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.

    Gail said this on December 27th, 2010 at 23:29

  18. 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!

    Shaun said this on December 27th, 2010 at 23:53

  19. Maybe take this to a a forum (like SSC)? Blog comments not really the place to help out on a complex problem

    Gail said this on December 28th, 2010 at 00:33

  20. 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

    GONZI said this on February 7th, 2012 at 10:31

  21. 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?

    Gail said this on February 7th, 2012 at 10:36

  22. 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

    GONZI said this on February 7th, 2012 at 11:31

  23. Very good article..

    prasad said this on April 19th, 2013 at 11:18

Leave a Reply