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?

1

2 Answers

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.

1

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.

1