The transaction log for the database is full

Question

I have a long running process that holds open a transaction for the full duration    I have no control over the way this is executed    Because a transaction is held open for the full duration  when the transaction log fills  SQL Server cannot increase the size of the log file    So the process fails with the error  The transaction log for database  xxx  is full    I have attempted to prevent this by increasing the size of the transaction log file in the database properties  but I get the same error   Not sure what I should try next  The process runs for several hours so it s not easy to play trial and error    Any ideas   If anyone is interested  the process is an organisation import in Microsoft Dynamics CRM 4 0     There is plenty of disk space  we have the log in simple logging mode and have backed up the log prior to kicking off the process   - - - - - UPDATE - - - - -  Thanks all for the comments so far   The following is what led me to believe that the log would not grow due to the open transaction   I am getting the following error     Import Organization  Name xxx  Id 560d04e7-98ed-e211-9759-0050569d6d39  failed with Exception  System Data SqlClient SqlException  The transaction log for database  xxx  is full  To find out why space in the log cannot be reused  see the log reuse wait desc column in sys databases   So following that advice I went to  log reuse wait desc column in sys databases  and it held the value  ACTIVE TRANSACTION    According to Microsoft  http   msdn microsoft com en-us library ms345414 v sql 105  aspx  That means the following   A transaction is active  all recovery models       A long-running transaction might exist at the start of the log backup  In this case  freeing the space might require another log backup  For more information  see  Long-Running Active Transactions   later in this topic       A transaction is deferred  SQL Server 2005 Enterprise Edition and later versions only   A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource  For information about the causes of deferred transactions and how to move them out of the deferred state  see Deferred Transactions    Have I misunderstood something   - - - - UPDATE 2 - - - -  Just kicked off the process with initial log file size set to 30GB   This will take a couple of hours to complete   - - - - Final UPDATE - - - -  The issue was actually caused by the log file consuming all available disk space   In the last attempt I freed up 120GB and it still used all of it and ultimately failed   I didn t realise this was happening previously because when the process was running overnight  it was rolling back on failure   This time I was able to check the log file size before the rollback   Thanks all for your input

User · Answer

Do you have Enable Autogrowth and Unrestricted File Growth both enabled for the log file  You can edit these via SSMS in  Database Properties   Files

User · Answer

If your database recovery model is full and you didn t have a log backup maintenance plan  you will get this error because the transaction log becomes full due to LOG BACKUP    This will prevent any action on this database  e g  shrink   and the SQL Server Database Engine will raise a 9002 error   To overcome this behavior I advise you to check this The transaction log for database    SharePoint Config    is full due to LOG BACKUP that shows detailed steps to solve the issue

User · Answer

To fix this problem  change Recovery Model to Simple then Shrink Files Log  1  Database Properties   Options   Recovery Model   Simple  2  Database Tasks   Shrink   Files   Log  Done   Then check your db log file size at Database Properties   Files   Database Files   Path  To check full sql server log  open Log File Viewer at SSMS   Database   Management   SQL Server Logs   Current

User · Answer

Here s my hero code  I ve faced this problem  And use this code to fix this    USE master       SELECT          name  log reuse wait  log reuse wait desc  is cdc enabled      FROM          sys databases      WHERE          name    XX System        SELECT DATABASEPROPERTYEX  XX System    IsPublished          USE XX System      EXEC sp repldone null  null  0 0 1      EXEC sp removedbreplication XX System        DBCC OPENTRAN      DBCC SQLPERF LOGSPACE       EXEC sp replcounters         DBCC SQLPERF LOGSPACE

User · Answer

The following will truncate the log   USE  yourdbname   GO  -- TRUNCATE TRANSACTION LOG -- DBCC SHRINKFILE yourdbname log  1  BACKUP LOG yourdbname WITH TRUNCATE ONLY DBCC SHRINKFILE yourdbname log  1  GO  -- CHECK DATABASE HEALTH -- ALTER FUNCTION  dbo   checker    RETURNS int AS BEGIN  RETURN 0 END GO

User · Answer

Is this a one time script  or regularly occurring job   In the past  for special projects that temporarily require lots of space for the log file  I  created a second log file and made it huge  Once the project is complete we then removed the extra log file

User · Answer

I met the error   The transaction log for database       is full due to  ACTIVE TRANSACTION  while deleting old rows from tables of my database for freeing disk space  I realized that this error would occur if the number of rows to be deleted was bigger than 1000000 in my case  So instead of using 1 DELETE statement  i divided the delete task by using DELETE TOP  1000000      statement   For example   instead of using this statement   DELETE FROM Vt30 WHERE Rt  lt  DATEADD YEAR  -1  GETDATE      using following statement repeatedly   DELETE TOP 1000000  FROM Vt30 WHERE Rt  lt  DATEADD YEAR  -1  GETDATE

User · Answer

The answer to the question is not deleting the rows from a table but it is the the tempDB space that is being taken up due to an active transaction  this happens mostly when there is a merge  upsert  is being run where we try to insert update and delete the transactions  The only option is is to make sure the DB is set to simple recovery model and also increase the file to the maximum space  Add an other file group   Although this has its own advantages and disadvantages these are the only options    The other option that you have is to split the merge upsert  into two operations  one that does the insert and the other that does the update and delete

User · Answer

Try this  If possible restart the services MSSQLSERVER and SQLSERVERAGENT

User · Answer

My problem solved with multiple execute of limited deletes like   Before   DELETE FROM TableName WHERE Condition   After  DELETE TOP 1000  FROM TableName WHERECondition

User · Answer

This is an old school approach  but if you re performing an iterative update or insert operation in SQL  something that runs for a long time  it s a good idea to periodically  programmatically  call  checkpoint   Calling  checkpoint  causes SQL to write to disk all of those memory-only changes  dirty pages  they re called  and items stored in the transaction log  This has the effect of cleaning out your transaction log periodically  thus preventing problems like the one described

User · Answer

I had this error once and it ended up being the server s hard drive that run out of disk space

User · Answer

Try this   USE YourDB    GO   -- Truncate the log by changing the database recovery model to SIMPLE    ALTER DATABASE YourDB SET RECOVERY SIMPLE    GO   -- Shrink the truncated log file to 50 MB    DBCC SHRINKFILE  YourDB log  50     GO   -- Reset the database recovery model    ALTER DATABASE YourDB SET RECOVERY FULL    GO    I hope it helps

[sql] The transaction log for the database is full

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to dynamics-crm