Thursday, May 1, 2008

FAQ : How to truncate and shrink Transaction Log file in SQL Server

First of all truncation of transaction log is not a recommended practice. But it is unavoidable if you have not kept proper backup policy and recovery model for your database. Its always better to know the cause and prevention for the transaction log size issue. Refer the following KB for more info

Managing the Size of the Transaction Log File
ahttp://msdn.microsoft.com/en-us/library/ms365418(SQL.100).aspx
Transaction Log Physical Architecture
http://msdn.microsoft.com/en-us/library/ms179355(SQL.100).aspx
Factors That Can Delay Log Truncation
http://msdn.microsoft.com/en-us/library/ms345414(SQL.100).aspx


Now coming to the point. If you have no space left with the drive where the Log file is kept and the size of the Transaction Log file is not manageable then its better to shrink the log.

Broadly , you have two steps here.
(a) Mark the inactive part of Trasaction log to release.
(b) Release the marked release portion of Transaction log to OS.

SQL Server 2005

-- Step 1 – Mark the inactive part of the log for release

Use YourDatabaseName
Go
Backup Log YourDatabaseName With Truncate_Only
GO

-- Step 2 - Release the marked space to OS


Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)

Note : If you have single log file the above mentioned script will work. IF you have multiple log file the change the script accordingly

SQl Server 2000

-- Step 1 – Mark the inactive part of the log for release

Use YourDatabaseName
Go
Backup Log YourDatabaseName With Truncate_Only
GO

-- Step 2 - Release the marked space to OS

Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sysfiles where filename like '%.ldf'
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)


Note : If you have single log file and the extension of the log file is .LDF the above mentioned script will work. IF you have multiple log file the change the script accordingly

SQL Server 2008

In SQL Server this process have been changed. In 20008, just change the recovery model to simple and then use DBCC SHrinkfile command.

select name,recovery_model_desc from sys.databases
GO
Alter database YourDatabaseName Recovery simple
GO
Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)

6 comments:

Bill P. said...

Thank you Madhu. This is a nice clear post. I especially like how you wrote it for each version of SQL Server.

I use this to explain how to dump the tran logs to all of my network engineers (who aren't 100% trained in SQL Server).

Bryan Phillips said...

Minor error in script for SQL 2008.

Should read:

Alter database YourDatabaseName Set Recovery simple

Instead of:

Alter database YourDatabaseName Recovery simple

GC said...

Hi. Thanks for the info but this doesn not work as I expect in 2008. My db is already in simple mode and has 50% of logspace used. I issue checkpoint and run full backup of db. I would expect % of logspace used to go down. It doesn't. Shrinkfile on the log has no effect either. I disconnect session and go back in and try the same thing and get the same results. After disconnection the session there should be no active part of the log. Why does % free not increase as it should after checkpoint and full backup in 08?

Madhu K Nair said...

Run DBCC OPENTRAN and see if there is any Open transactions. If yes kill the process.

Madhu

Kavitha said...

Hello All,
I have a SQL Server 2008 production environment. Since, we have Mirroring / Log Shipping inour environment, I cannot change the recovery mode to "simple" for database. Hence, I came up with this code, and works really well

-- Replace the "DatabaseName" with Name of the Database
Use DatabaseName
go
SP_helpDB DatabaseName
Go
---Get the Log FileName and replace "LogFileName"
DBCC ShrinkFile(LogFileName,1)
Go
Backup Log DatabaseName with Truncate_Only
Go
DBCC ShrinkFile(LogFileName,1)
Go
Backup Log DatabaseName with Truncate_Only
Go
DBCC ShrinkFile(LogFileName,1)
Go

John Croson said...

Thanks Madhu! This helped me out with my hosted website db.

 
Locations of visitors to this page