Search This Blog

Thursday, December 6, 2007

Shrinking the Transaction Log

Most of the time the transaction log file growth to very huge size until your harddisk not able to take it. What can i do is shrinking the log file.

Remember: Always take full backup before this activity.

There are few ways to do this but i like to use detach and attach method. You can choose whether using enterprise manager or T-SQL. I like to use T-SQL.

t-sql
  • sp_detach_db [dbname]
  • Delete the disk log file.
  • sp_attach_single_file_db [dbname], [filename]where [filename] is the name of the physical data file (.mdf).






Example:
I have a database name RatePost and the .mdf and .ldf physical location
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePost.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePost_log.ldf

  1. Open my enterprise manager and open a new query window.
  2. Type sp_detach_db RatePost in the query window and run the script
  3. Go to "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" and delete RatePost_log.ldf
  4. Clear the query window and type
    sp_attach_single_file_db RateMyPosts, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePost.mdf' and run the script
  5. After you have run your script, you can see in Message window
    File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePost_log.ldf" may be incorrect.
    New log file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePost_log.ldf' was created.

Now you go to check your log file size reduce and back to around 500Kbyte.

Done.

1 comment:

Anonymous said...

Bravo, this idea is necessary just by the way