Monthly Archive for December, 2008

SQL SERVER - Full Transaction Log (What to do)

Published
by
rs
on December 4, 2008
in SQL and Technical
. 0 Comments

Transaction log mechanism enables SQL Server to record all transactions before writing them to the database. This capability enables SQL Server to automatically recover the database to a consistent state when experiencing a power failure, operating system crash, and so forth.

The SQL Server transaction log can become full, which prevents further UPDATE, DELETE, or INSERT activity in the database.

One way to fix this is to back up and truncate the transaction logs.

BACKUP LOG <DbName> WITH TRUNCATE_ONLY

To save more disk space, we could shrink the truncated log file to it’s minimum size.

DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DbName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)