Hossein Zahed

Web Developer, Entrepreneur, Software Educator

Transaction log is full. How do I shrink it?

This KB article describes how MS SQL database transaction log can be shrunk in case if one day your system has stopped working showing the error:

'Transaction Log is Full' (Error 9002) 

The error means that restricted maximum size of the transaction log file of your Easy Projects database was reached. 

You must shrink the transaction log by executing one of the following scripts depending on the version of your MS SQL Server.

 

For MS SQL Server 2005 the script is as follows:

USE <DatabaseName>
GO
DBCC SHRINKFILE (<TransactionLog>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLog>, 1)
GO

 

For MS SQL Server 2008 / 2008R2:

ALTER DATABASE <DatabaseName> set recovery simple
GO
CHECKPOINT
GO
DBCC SHRINKFILE (<TransactionLog>,1)
GO
ALTER DATABASE <DatabaseName> set recovery full
GO

where:

<DatabaseName> - the name of your EP database, e.g. "EasyProjects",

<TransactionLog> - logical name of EP database log file, e.g. "EasyProjects_log".

 

In order to find out what is the logical name of your EP database log file:

1) Open the SQL Server management studio,

2) Right-click on your EP database and choose Properties,

3) Click on the 'Files' tab.

Comments are closed