Truncate log file up to 504 KB – SQL Server 2008, 2008-R2 & 2012

Yes, It’s true that we can truncate our log file up to the 504 KB with discard old log file & create new log file. This log truncation activity will help DBA in Capacity Management.

Note: 

  1. This can be done with only Database which have been configured with Full Recovery Mode option.
  2. Please consider full database backup before doing this activity so that In worst situation we will have safe backup in our hand.
  3. Refer my one of the post on SQL Server backup configuration to backup your SQL Server database.
  4. TestDB database is used to simulate for this activity.

After successful backup, Consider following step by step guide to truncate your DB log file:

Verify the log file ( .ldf ) location by right-click on Database and then click on Property:

Select Files tab.

In following screenshot: Path of .mdf & .ldf file shown under Database Properties >> Files

Truncate log file up to 504 KB - Database Properties

Truncate log file up to 504 KB – Database Properties

 

After Verifying path of .ldf file, detach database from Microsoft SQL Server Management Studio by right-click on Database you want to detach >> Tasks >> Detach.. & click Ok, as shown below:

Truncate log file up to 504 KB - Detach Database

Truncate log file up to 504 KB – Detach Database

 

On Detach Database wizard select Drop connections & Update Statistics check boxes & click Ok, as shown below:

Truncate log file up to 504 KB - Detach Database

Truncate log file up to 504 KB – Detach Database

 

TestDB detached successfully, as database TestDB not visible in Microsoft SQL Server Management Studio >> Instance, as shown below:

Truncate log file up to 504 KB

Truncate log file up to 504 KB

 

Now rename log file name from TestDB_log.ldf to Old_TestDB_log.ldf, as show below:

Truncate log file up to 504 KB - Data Folder

Truncate log file up to 504 KB – Data Folder

 

Log file renamed as shown below:

Truncate log file up to 504 KB - Old_TestDB_log.ldf

Truncate log file up to 504 KB – Old_TestDB_log.ldf

 

After renaming log file, now attach database by right-click on Databases >> Attach.. , as shown below:

Truncate log file up to 504 KB - Attach

Truncate log file up to 504 KB – Attach

 

On Attach Databases wizard, click on Add button to add database from list, as shown below:

Truncate log file up to 504 KB - Attach Databases

Truncate log file up to 504 KB – Attach Databases

 

Select TestDB.mdf file >> click Ok, as show below:

Truncate log file up to 504 KB - Locate Database Files

Truncate log file up to 504 KB – Locate Database Files

 

Attach Databases wizard will show your .mdf & .ldf files under “TestDB database details” as show below:

You will find Message: TestDB_log.ldf log file Not Found on specified path, because we already renamed it as Old_TestDB_log.ldf

Select TestDB_log.ldf row and click on Remove button as shown below:

Truncate log file up to 504 KB - Attach Databases

Truncate log file up to 504 KB – Attach Databases

 

After successfully attachment of TestDB database, you will find newly created log file ( TestDB_log.ldf ) on specified location with 504 KB of its size, as shown below.

For the sake of your convenience you should consider full database backup after log truncation activity.

After full database backup you can drop old log file ( i.e. Old_TestDB_log.ldf ) so that space will be free.

Truncate log file up to 504 KB - New TestDB_log.ldf file created

Truncate log file up to 504 KB – New TestDB_log.ldf file created

 

Database TestDB successfully attached, as shown below:

Truncate log file up to 504 KB - TestDB Database Attached Successfully

Truncate log file up to 504 KB – TestDB Database Attached Successfully

 

For capacity management: we can schedule this activity every month as a part of Database Administration.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Leave a Reply