SQL Server Transaction Log Full?

Transaction logs run out of space for various reasons – most commonly due to long running transactions that grow the log beyond the space available.  This typically results in a SQL Server Error 9002.

, SQL Server Transaction Log Full?

SQL Server 2008 and Above

With SQL Server 2008 and above, the following commands can be used to shrink a transaction log file that has run out of space:

USE <databasename>
 GO
 -- Truncate the log by changing the database recovery model to SIMPLE
 ALTER DATABASE <databasename> SET RECOVERY SIMPLE
 GO
 -- Shrink the truncated log file to 1 MB
 DBCC SHRINKFILE (<logical filename>, 1)
 GO
 -- Put the database recovery model to FULL
 ALTER DATABASE <databasename> SET RECOVERY FULL
 GO

An easy way to find the logical filename for your database’s transaction log is:

SP_HELPDB <databasename>

Legacy SQL Server Versions

For earlier versions of SQL Server:

use master
 go
 dump transaction <databasename> with no_log
 go
 use <databasename>
 go
 DBCC SHRINKFILE (<logical filename>, 100) -- where 100 is the size you may want to shrink it to in MB
 go

Did you find this helpful?

Share it on social media!

THIS BLOG POST IS BY
Dean is one of the co-founders of Adaptive Solutions. He specializes in document and records management migrations and implementations.

Leave a Comment