46

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?

dev4life
  • 10,785
  • 6
  • 60
  • 73
  • Small ;) Not very large. Definitely NOT vary large as long as it fits into the memory of a mid range server. – TomTom May 23 '14 at 16:00

2 Answers2

36

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.

dev4life
  • 10,785
  • 6
  • 60
  • 73
  • In my case neither restarting SQL Server nor shrinking the database worked. The db was restored from a backup and I think something was wrong with backup itself. I ended up detaching the database, removing the LDF file, attaching it removing the expected LDF file row and letting SQL Server to create a new log file. It got fixed this way. – hamid reza Dec 12 '21 at 07:53
15

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.

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • 5
    DBCC SQLPERF('logspace') will show you "Log Size (MB)" and "Log Space Used (%)". Keep in mind that the log size doesn't indicate the actual size of the written logs but the size of your log file on disk. So to get the true size of the log in MB, multiply the two columns. E.g., log size of 2.00MB and 70% used means you have 1.4MB of log data. – brentlightsey Dec 22 '17 at 20:38