Sql server - log is full due to ACTIVE_TRANSACTION [duplicate]

Question

I have a very large database (50+ GB). In order to free space in my hard drive, I tried deleting old records from one of the tables . I ran the command:

delete from Table1 where TheDate<'2004-01-01'; 

However, SQL Server 2012 said:

Msg 9002, Level 17, State 4, Line 1  The transaction log for database 'MyDb' is full due to 'ACTIVE_TRANSACTION'. 

and it did not delete a thing. What does that message mean? How can I delete the records?

Answer 1

Here is what I ended up doing to work around the error.

First, I set up the database recovery model as SIMPLE. More information here.

Then, by deleting some old files I was able to make 5GB of free space which gave the log file more space to grow.

I reran the DELETE statement sucessfully without any warning.

I thought that by running the DELETE statement the database would inmediately become smaller thus freeing space in my hard drive. But that was not true. The space freed after a DELETE statement is not returned to the operating system inmediatedly unless you run the following command:

DBCC SHRINKDATABASE (MyDb, 0); GO 

More information about that command here.

Answer 2

Restarting the SQL Server will clear up the log space used by your database. If this however is not an option, you can try the following:

* Issue a CHECKPOINT command to free up log space in the log file.  * Check the available log space with DBCC SQLPERF('logspace'). If only a small    percentage of your log file is actually been used, you can try a DBCC SHRINKFILE    command. This can however possibly introduce corruption in your database.   * If you have another drive with space available you can try to add a file there in    order to get enough space to attempt to resolve the issue. 

Hope this will help you in finding your solution.