Home  »  Articles  »  SQL Server  »

SQL Server Database Shrink or Compress

 Posted By : Manoranjan SahooPosted Date : 21/10/2010 11:37:51 AM Category : SQL Server Points : 4

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.

Responses
No responses found for this thread. Be the first to respond this thread.
Post Reply
Login to post Response.