Shrinking a Database

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.

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 points will illustrate that shrinking a database will

•  Causes pages being moved from the front of file to the end
•  It will cause massive fragmentation
•  It will not reduce the size of the data file, but actually increase it
•  That shrinking a database will cause unnecessary I/O hits
•  That shrinking a database was a futile endeavor because of poor planning     regarding sizing of database
•  And that shrinking a database should be very rarely done