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.