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

 

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:

 

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

 

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

 

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

 

Log file renamed as shown below:

 

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

 

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

 

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

 

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:

 

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.

 

Database TestDB successfully attached, as shown below:

 

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.