SQL

SQL Server Database Shrink or Compress

5
(1)

We are always see that our database file(.mdf) is very low, but the log file is too high nearly 10-100% high or more. One day i saw in our company database, the .mdf file is nearly 500MB but the log file is 35GB. So i tried to clean the log file and got the solution from one of sql server ebooks. So i place that code here to help those people who are having this problem.

Syntax :

USE <DatabaseName>
GO
DBCC SHRINKFILE(<TransactionLogName>, <file size>)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, <file size>)

You can change database name, TransactionLogName and file size as per your requirement.

For Example :

USE dbproduct
GO
DBCC SHRINKFILE(dbproduct_log, 10)
BACKUP LOG dbproduct WITH TRUNCATE_ONLY
DBCC SHRINKFILE(dbproduct_log, 10)

Thank You.

How useful was this post?

Click on a star to rate it!

Leave a Reply

Your email address will not be published. Required fields are marked *