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 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:
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