SQL Server Truncate Transaction Log script

Friday, May 2, 2008 |

Many time, it happens in live sql server which has big transaction log file, you run DBCC SHRINKFILE command and see no effect of it on the transaction log file. You can use following magical five line at that time and you will done. It shrinks the trucated log File to minimum size possible.
-------------------------------------------------------------------------
USE DBName
GO
DBCC SHRINKFILE(transactionlogfilename, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(transactionlogfilename, 1)
------------------------------------------------------------------------
Happy Working!!!!

6 comments:

Anonymous said...

Thank you. I had an 18GB transaction log that grew by ~1GB per day (netflow statistics from a router).

Your script allowed me to go from 18GB to 3.4MB.

Ritesh Shah said...

you are welcome.

Rizwan Naik said...

Thanks it helped me too to reduce 40 GB log to 25 mb.... Thanks a lot.

Rizwan Naik said...

Hey thanks... worked from 40GB to 25 MB... in seconds.

Rizwan Naik said...

Hey thanks... worked from 40GB to 25 MB... in seconds.

Rizwan Naik said...

Hey thanks... worked from 40GB to 25 MB... in seconds.