Shrinking Databases and Logs - SQL 7 and Higher

SQL 7 and SQL 2000 allow automatic file growth. Therefore, the files can grow large. It is important to understand how to reduce the database and log files sizes.

There are two DBCC commands to help shrink databases and files. Both are needed to reduce and maintain file sizes.

DBCC shrinkdatabase
DBCC shrinkfile

The following links provide a lot of information about these commands as well as general information and code for performing shrinks.

DBCC SHRINKDATABASE
http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_3pd1.asp

DBCC SHRINKFILE
http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_8b51.asp

Truncating the Transaction Log
http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_7vaf.asp

Shrinking the Transaction Log
http://msdn.microsoft.com/library/psdk/sql/8_ar_da2_1uzr.htm

Check out the FAQ posted by FOXDEV - FAQ183-345 - to find more information about shrinking the log and the log file.

You can also read the following articles.

Transaction Log Backups
http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_565v.asp

Shrinking Active Log FilesùRevisited
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9011

Quickly Shrinking the Transaction Log
http://www.swynk.com/friends/krishnan/tranlogshrink.asp

Note: the article above suggests using sp_detach_db and sp_attach_single_file_db to quickly shrink the log file. However, if a database has more than one physical log, this technique should not be used. Every database requires at least one log file. Additional log files can be added and removed using the ALTER database statement in T-SQL, from Enterprise Manager or with SQL-DMO. See the Microsoft online article about adding and deleting files.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_aa-az_4e5h.asp

The same information can be found in SQL BOL.