:::: MENU ::::

How to truncate the transactional log

  • Mar 30 / 2009
  • 1
DBCC Commands, dbDigger, T-SQL Scripts, T-SQL Tips and Tricks, Transaction log files

How to truncate the transactional log

In one of previous posts about Dealing the growth of Transactional Log Files i discussed briefly about function of transactional log files in SQL Server and also there were some prevention measures to prevent growth of transactional log file on large amount.
There may be situation that we have to get rid of grown file. In that case obvious work around is to truncate the transactional log. To truncate the transaction log for AdventureWorks database

ALTER DATABASE AdvetureWorks  
 SET recovery SIMPLE  
 GO  
 DBCC shrinkfile (LogFileNameofAdventureWorks)  
 GO  
 ALTER DATABASE AdventureWorks  
 SET recovery FULL  
 GO  

As in case of simple recovery mode SQL Server transaction log is not maintained for that database. So the transactional log will be dropped for database when recovery model is set to simple.
It is important to mention that on going transactions should be considered before changing recovery models.

  • Atif Shehzad

    If you plan index rebuild then it is good idea to change recovery mode of your database to simple and after the rebuild completes you may switch back to full recovery mode.

Consult us to explore the Databases. Contact us