Just Learned - Script to truncate transaction log for a database

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

Script to truncate transaction log for a database

Nov 15 2011 3:54AM by Latha

If you come across the following error while running a T-SQL query or executing a stored procedure or in a SSIS package execution which states:

"The transaction log for database 'DatabaseName' is full. To find out why space in the log cannot be reused, see the logreusewait_desc column in sys.databases".

You need to truncate the transaction log for the database to proceed further for that you need to find out the physical log file id for the database using its logical file name which is the name we provide while creating the database usually it will be your "DatabaseName_log".

Then the run the script. You need dbo rights to run the script.

Checkpoint
DBCC opentran(DatabaseName)
BACKUP log DatabaseName WITH TRUNCATE_ONLY

DECLARE @dbfileid INT
SELECT @dbfileid = fileid FROM sysfiles WHERE [name] = 'logicallogfilename'   
DBCC SHRINKFILE (@dbfileid)

Find more about sys.files at: http://msdn.microsoft.com/en-us/library/ms178009.aspx

5 Comments

Hi,

Actually in my one project where i have imports so many records like more than 100 million then temp database taken more space so i need to do below step for remove space

declare @dbname nvarchar(255) set @dbname = 'amazigwalkdatacollection' backup log @dbname with truncate_only DBCC SHRINKDATABASE (@dbname, 0)

is it same what i have done. i have done this in sql server 2005

Regards, Bhavik My Blog

commented on Nov 16 2011 12:21AM

Solanki Bhavik A.

155 · 1% · 310

TRUNCATE ONLY is EVIL!!! As far as I know, it either has been removed, or it will be removed in a future version of SQL Server. Just to give you an example: you truncate the log, then something fails and you need to bring the database online to the most recent point possible. You just wiped out that chance.

best regards, calin

commented on Nov 16 2011 2:14AM

sageata2002

1493 · 0% · 13

BACKUP LOG WITH TRUNCATE_ONLY has been removed from SQL Server 2008 R2.

DBCC SHRINKFILE is not necessary. You need to free up space within the transaction log, not shrink the physical file. Taking a transaction log backup of a full recovery model database will accomplish this.

If this doesn't work (the transaction log will not truncate if there are active transactions within a virtual log file), switch the recovery model to SIMPLE, then back to FULL. You will need additional space in the transaction log file, so you may have to temporarily add a second file to the transaction log. This second file may need to live on a different volume than the first.

If you use DBCC SHRINKFILE, the next time you write a transaction, you are just going to AUTOGROW the transaction log. This will lead to physical fragmentation of the transaction log file within the file system, slowing down transaction log backups and database recovery.

Best practice is to pre-allocate the space you need.

Refer to the Books Online Topic "Troubleshooting a Full Transaction Log (Error 9002)"

commented on Nov 16 2011 5:21AM