Truncate LOG in a SQL Server 2008 Database

Today I've got a problem with a SQL Server 2008 Database again with the transaction log, which was full and generating errors in the business processes.

In an old entry I posted how to solve the problem, but in SQL Server 2005  Using the scripts for 2005 in SQL Server 2008, it will generate this error:

'TRUNCATE_ONLY' is not a recognized BACKUP option.

So, the solution in a SQL Server 2008 Database is to use this script:

USE ReportServerTempDB -- Database

ALTER DATABASE ReportServerTempDB SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE( ReportServerTempDB_log, 1) -- Parameters are: Logical Name of the file, percent occupied
ALTER DATABASE  ReportServerTempDB  SET RECOVERY FULL WITH NO_WAIT
GO

Like the old one, it has been tested and works fine.

To learn more about this, check this link from Pinal's Dave Blog: Original Page

Truncate LOG in a SQL Server 2005 Database

When the transaction log grows without control in a SQL Server 2005 database can reach the point to leave the database unavailable.  To avoid this situations it's recommended to execute this script to erase all transactions and recover some space:


USE ReportServerTempDB -- Database
GO
DBCC SHRINKFILE(ReportServerTempDB_log, 1) -- Parameters are: Logical Name of the file, percent occupied
BACKUP LOG ReportServerTempDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(ReportServerTempDB_log, 1) -- Parameters are: Logical Name of the file, percent occupied
GO 

It has been tested and works fine, another recommendation is never leave the database files with unrestricted Growth enabled.  I was notified about databases with 300 GB transaction log and this situation makes me laugh so much.

To learn more about this, check this link from Pinal's Dave Blog: Original Page