Shrink transaction log SQL 2008 r2

Once transaction log is full, I was unable to shrink it.  All searches in Google did not help.  Finally I found a script which clears the transaction log if and only if the transaction log is not full yet.  I tried it and it worked.

USE DBNAME
GO
ALTER DATABASE DBNAME SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(DBNAME_log, 1)
ALTER DATABASE DBNAME SET RECOVERY FULL WITH NO_WAIT
GO

I still need to figure out what is the way to shrink after your transaction log is already full and database has started giving errors

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.