Live Situation #3 : Clearing the transaction log in SQL Server 2005

5 months ago – in one of my project, Mirth integration server was linked to SQL Server to store the processing data. One of the channel in Mirth has a flat file reader, parses it and at  every major point it inserts the data to a table.
With a live demand, Mirth was asked to process a file of more than 2lacs of lines. This resulted a large log file in SQL server.
Situation came to somehow empty the content of this log file(.ldf) without affecting the actual database file(.mdf).
One of the frequent answer you get after Googling by several bloggers – Detach the database, rename the log file and again attach the database. But please don’t do this. This can damage your database so badly that you cant retrieve it even. Yes I have struggled with one of the database badly.

Here is the way that sounds pretty smooth toward the solution:
This is very quick and reduced the log file size from 1 GB to 1MB in just 10 seconds. Amazing!!!

1. Select a database.
Right Click -> Properties -> Options -> Database Recovery Model -> Simple [change it to Simple from anything else]

2. Right Click on database -> Tasks -> Shrink -> Files
From dropdown select file type as Log
Release unused space
or Shrink to file size – Give your own choice e.g 1 MB

3. Right Click -> Properties -> Options -> Database Recovery Model -> Full [change it to Full or whatever you had initially from Simple]