Techniques to Shrink a SQL Database

Sometimes the problem is not an inflated logfile or dbfile, but the data itself. In that case, cleaning out old data will help regain that valuable disk space.

A way to gain a little space without purging data is to reindex the database. Indexes, like many other things in SQL grow over time and a reindex will shrink them back down to their fighting weight.

 

 

To shrink the indexes, use the following script…

• USE MyDatabase
• GO
• EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 85)"
• GO
• EXEC sp_updatestats
• GO

This script will take some time to process. After it is completed, you’ll need to shrink the actual file to regain that lost space.

• DBCC SHRINKFILE (N'DB_Logical_Name' , 100, TRUNCATEONLY)