Skip to content

SQL Server Shrinking a Database

         The primary purpose of shrinking a database (or individual files) is to reclaim the SPACE by removing unused pages when a file no longer needs to be as large as it once was; shrinking the file may then become necessary, but as we will demonstrate, this process is highly discouraged and is an extremely poor practice in the production database environment.

    100mb mdf files    1000mb mdf files    100mb    

    Things to note

    • Both data and transaction log files can be shrunk individually or collectively
    • When using the DBCC SHRINKDATABASE statement, you cannot shrink a whole database to be smaller than its original size. However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement.
    • The size of the virtual log files within the log determines the possible reductions in size.
    • Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added.  When that happens, the entire system will slow down as the file is expanded.

    Automatic Database Shrinking

         When the AUTO_SHRINK database option has been set to ON, the Database Engine automatically shrinks databases that have free space. By default, it is set to OFF. Leave it as is.

    Database Shrinking Commands

    • DBCC SHRINKDATABASE (ShrinkDB, 10)
    • DBCC SHRINKFILE (ShrinkDB, 10)

    Best Practices

    • Size the database accordingly so as to prevent shrinking and expanding the database
    • When unused space is created due to dropping of tables, shrinking of the database may be needed – but rarely
    • Don’t constantly shrink the database as it will grow again
    • When you shrink a database, the indexes on tables are not preserved and as such will causes massive fragmentation and will have to be rebuilt
    • Shrinking the log file may be necessary if your log has grown out of control however, shrinking the log should be rare also
    • Regular maintenance plans should never have shrink database job
    • Other issues that shrinking causes are lot of I/O, of CPU usage, and transaction log gets larger – as everything it does is fully logged.

    The following example will illustrate that shrinking a database will

    1. Causes pages being moved from the front of file to the end

    2. It will cause massive fragmentation

    3. It will not reduce the size of the data file, but actually increase it

    4. That shrinking a database will cause unnecessary I/O hits

    5. That shrinking a database was a futile endeavor because of poor planning regarding sizing of database

    6. And that shrinking a database should be very rarely done

    USE [master];
    GO
     
    CREATE DATABASE ShrinkDB;
    GO
    
    USE [ShrinkDB];
    GO
     
    
    -- Create an initial table at the 'front' of the data file
    CREATE TABLE Initial (
        [col1] INT IDENTITY,
        [col2] CHAR (8000) DEFAULT 'Front');
    GO
    
    
    -- Insert data into Initial table
    INSERT INTO Initial DEFAULT VALUES;
    GO 1500
    
    select * from Initial
    
    --check the size of the database
    sp_helpdb [ShrinkDB]
    --14.83 MB
     
    -- Create the second table, which will be created 'after' the initial table in the data file
    CREATE TABLE second (
        [col1] INT IDENTITY,
        [col2] CHAR (8000) DEFAULT 'after');
    
    Create a clusterd index on the second table
    
    CREATE CLUSTERED INDEX [coll] ON second ([col1]);
    GO
     
    
    Insert data into second table
    
    INSERT INTO second DEFAULT VALUES;
    GO 1500
    
    
    select * from second
    
    Check db size 
    sp_helpdb [ShrinkDB]
    
    Database expanded due to insert of data in the second table (26.83 MB)
     
    Check the fragmentation of the second table
    
    SELECT
        [avg_fragmentation_in_percent]
    FROM sys.dm_db_index_physical_stats (
        DB_ID (N'ShrinkDB'), OBJECT_ID (N'second'), 1, NULL, 'LIMITED');
    GO
    
    Notice that the fragmentation of the clustered index for the second table is almost zero before the shrink
    
    We will now drop the initial table we created and execute the shrink command to reclaim the SPACE at the front of the data file
     then see what happens to the fragmentaion.
    
    DROP TABLE Initial;
    GO
    
    
    sp_helpdb [ShrinkDB]
    -- 26.83 MB the data file has not shrunk due to the deletion of the initial table
     
    -- Shrink the database
    DBCC SHRINKDATABASE ([ShrinkDB]);
    GO
    
    Notice that the SPACE after the shrink went down from 26.83 to 15.02 mb
    
    sp_helpdb [ShrinkDB]
    --15.02 MB
     
    But notice what happened to the fragmentation of the data file because of the shrinking of the database???
    When Checking the index fragmentation again, we notice that the fragmentation has drastically increased to almost 100%!!!
    This is because we have shuffled all the data pages and the index is not in a sorted position
    
    SELECT
        [avg_fragmentation_in_percent]
    FROM sys.dm_db_index_physical_stats (
        DB_ID (N'ShrinkDB'), OBJECT_ID (N'second'), 1, NULL, 'LIMITED');
    GO
    
    --99.6
    
    While the database has shrunk, and we have reclaimed space from the data file, we MUST now the fix the fragmented index of the table by rebuilding the index!!!
    
    Rebuild the Clustered Index
    
    ALTER INDEX [coll] ON second REBUILD
    GO
    
    
    Checking the index fragmentation again indicates that the fragmentaion of the index has been restored, but notice the size of the data
    
    File when we run the sp_helpdb [ShrinkDB] - it has actually GROWN even more than it started from!!!
    
    SELECT
        [avg_fragmentation_in_percent]
    FROM sys.dm_db_index_physical_stats (
        DB_ID (N'ShrinkDB'), OBJECT_ID (N'second'), 1, NULL, 'LIMITED');
    GO
    
    --0.2%
    
    sp_helpdb [ShrinkDB]
    --41.81 MB
    
    The database file has grown because of the rebuilding of the index and the logging of the index
    
    use master
    go
    select * from sys.databases
    
    sp_helpdb ShrinkDB
    --transaction log = 13888 KB
    
    --individual file shrink
    USE ShrinkDB
    GO
    DBCC SHRINKFILE (N'ShrinkDB_Log' , 0, TRUNCATEONLY)
    GO
    
    sp_helpdb ShrinkDB
    
    --784 KB
    
    USE ShrinkDB
    
    GO
    DBCC SHRINKFILE (N'ShrinkDB_Data' , 0, TRUNCATEONLY)
    GO
    
    USE ShrinkDB
    
    GO
    DBCC SHRINKDATABASE(N'ShrinkDB' )
    GO