Sql server - log is full due to ACTIVE TRANSACTION

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

User · Answer

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.

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2012