SQL Server Database Maintenance: File Size

To Shrink the size of the database data file or the transaction Log by using Query Analyzer. Let’s say The database name is called BNET.

Use BNET

DBCC ShrinkFile (BNET_Data)

DBCC ShrinkFile(BNET_Log, 2)

The parameter 2 means you hope you can shrink the Translation Log to 2 MB.

The transaction log can grow very big, and the DBCC ShrinkFile will not be able to Shrink, if the system thinks there are incomplete Transactions in the Transactions Log. If you don’t not care about the pending transactions, or if you think all the transactions you need is already in BNET_Data, you can use the following command:

Backup LOG BNET with Truncate_Only

After this is done, run:

DBCC ShrinkFile(BNET_Log, 2)

again to make sure the size shrinks to 2 MB.