Transaction Log Full For Regular DB’s

Posted: April 1, 2008 in Sqlserver

Identifying and Correcting the Problem for Transaction Log Full w.r.t Regular DB’s

Error:

From:                SQLExec

Sent:                Sunday, April 30, 2006 2:00 AM

To:                    #IS CompTechDBA

Subject:            SQL Server Alert System: ‘Full Log’ occurred on \\HAS605

DATE/TIME:      4/30/2006 2:00:04 AM

DESCRIPTION:  Error: 9002, Severity: 17, State: 6

The log file for database ‘AGQuantum’ is full. Back up the transaction log for the database to free up some log space.

COMMENT:       (None)

JOB RUN:         (None)

 Solution:

If the Regular database Transaction logs runs out of space, this is indicated in the SQL ERRORLOG files, use the following process:

1. Free up (unallocated) the space used by the LOG portion of the database with the following command called from the master database:

USE <database>

GO

BACKUP LOG <database> WITH TRUNCATE_ONLY
GO

Notes:

1. After you truncate a database LOG file, the SQL server documentation recommends that you back up your database. In case of a physical failure (for example a power down or hard disk error), the SQL server cannot recover from the transaction log, as it was just truncated.

2. After running this command, the LDF file has been reorganized to have a lot of unallocated space, but the database must be shrunk to release that space to the file system. (It still looks like a large file if you view it from a command prompt directory listing). See next example for how to shrink the database.

Shrinking a Database

You can shrink a database to release the unallocated or unused space (or both) to the file system with the following command:

USE <database>

GO

DBCC SHRINKDATABASE (database)

GO

You can also use the SQL Enterprise Manager to shrink a database by selecting the following menu items: Right click on the Database -> All Tasks -> Shrink Database.

 

 

Advertisements

Comments are closed.