Skyscraper

martes, 11 de enero de 2011

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

No hay comentarios:

Publicar un comentario