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]

Advertisements

One comment on “Live Situation #3 : Clearing the transaction log in SQL Server 2005

  1. If you desire to grow your familiarity just keep visiting this web site and be updated
    with the most recent gossip posted here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s