Sql server - log is full due to ACTIVE_TRANSACTION

sql sql-server sql-server-2012
oabarca picture oabarca · May 23, 2014 · Viewed 209.7k times · Source

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

oabarca picture oabarca · May 23, 2014

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.

Related questions

Convert Date format into DD/MMM/YYYY format in SQL Server

I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server?

Read More
Recover unsaved SQL query scripts

How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?

Read More
SQL Server IF EXISTS THEN 1 ELSE 2

Using Sql Server 2012. I have a stored procedure and part of it checks if a username is in a table. If it is, return a 1, if not, return a 2. This is my code: IF EXISTS (SELECT * FROM tblGLUserAccess WHERE GLUserName =…

Read More