Home > Backup and Recovery > Quick way to handle “Transaction log file full” issue

Quick way to handle “Transaction log file full” issue


In our day to day work life very frequently we are getting the error that transaction log grows unexpectedly or becomes full. In most of the production system it is happening frequently.

Errors from SQL server error log:

Error: 9002, Severity: 17, State: 2.

The transaction log for database ‘test’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

If you get an error “Error: 9002, Severity: 17, State: 2.” like this go and check the log_reuse_wait_desc column in sys.databases by running below command and see what value it returns.

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases

log_reuse_wait_desc “NOTHING” means there is no open transaction. If any other value is mentioned then we should take the transaction log backup for the respective database.

Script To shrink the all user database if database associate with single log file:

sp_msforeachdb “use [?]; dbcc shrinkfile(2,TRUNCATEONLY) WITH NO_INFOMSGS ”

Note:The command will work fine only if every user database only have single log file.

If we are using multiple log files in different drive, then it is bit tough to quickly shrink the all user database log files together. It is very hectic. Please use the below command file to shrink all user database log file at the same time.

Script if databases have multiple log files:

DECLARE @DBName varchar(255)

DECLARE @DBLogicalFileName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR

select DATABASE_NAME = db_name(MaTableMasterFiles.database_id), MaTableMasterFiles.name

from sys.master_files MaTableMasterFiles

where

– ONLINE

MaTableMasterFiles.state = 0

– Only look at databases to which we have access

and has_dbaccess(db_name(MaTableMasterFiles.database_id)) = 1

– Not master, tempdb or model

and db_name(MaTableMasterFiles.database_id) not in (‘Master’,’tempdb’,’model’)

AND type_desc LIKE ‘log’

group by MaTableMasterFiles.database_id, MaTableMasterFiles.name

order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName

WHILE @@FETCH_STATUS = 0

BEGIN

Exec (‘Use [‘ + @DBName + ‘] DBCC SHRINKFILE (“‘ + @DBLogicalFileName + ‘”)’)

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName

END

CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR

Advertisements
Categories: Backup and Recovery
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: