none
The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait

    Question

  • Every time I get this error, at different points of testing inserts and deletions on my table:

    The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

    Why do I keep getting this?  All I'm doing is deleting several hundred thousand records and inserting them into a couple of tables.  i shouldn't have to truncate my log every time or my application bombs out!

    sys.databases only gives me this info for log_reuse_wait_desc which does nothing for me:

    LOG_BACKUP
    Monday, March 06, 2006 4:37 AM

Answers

  • I agree.

    If you need to be able to recover your database to a point in time, then you need to keep your logs and back them up if you don't need this level of recovery then you can set your DB to simple. Be aware that you will only be able to restore to the point of you last full backup followed by any differential backup you have performed since.

    Monday, March 06, 2006 11:05 PM
  • sp_helpdb BizTalkDTADb

    ALTER DATABASE BiztalkDTADb
    SET RECOVERY SIMPLE;
    GO

    DBCC SHRINKFILE (BiztalkDTADb_log, 1);
    GO
    sp_helpdb BizTalkDTADb
    GO
    ALTER DATABASE BiztalkDTADb
    SET RECOVERY FULL
    GO

    Why on Earth are you posting a reply to a thread that started in 2006, and which has not been touched for over two years? On top of that you solution is a bad one for several reasons.

    1) You suggest shrinking the log file to 1 MB, which unlikely to be correct. What is correct is difficult to tell, because it depends on the system in question. For a developer database 50 MB can do. For a production database that itself is 1TB, a log file of 100 GB is not unreasonable.

    2) You suggest flipping the recovery model from full to simple and back again. This breaks the log chain, and you will not be able to do point-in-time recovery from this point and on. There are two options here:
    a) You don't care about point-in-recovery. (For instance, because it is a development database on your workstation). In this case keep the database in simple recovery.
    b) Immediately take a full backup, so that you can meet the RPO (Recovery Point Objective) in your SLA (Service Level Agreement.)

    And overall, shrinking database files is a very exceptional thing which you should only do when you know that the file has grown to a size that will not achieve again. This is particularly true for log files, since growing a log file takes time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 15, 2013 10:54 AM

All replies

  • Why don´t you specify a the log bigger, that this message won´t come during the modification of your database ? If you are doing large inserts in one transaction this could be the problem. So, increase your log or set the log to autogrowth (or both) that SQL Server can get more space doing the transactions.

    HTH, jens Suessmeyer,
    Monday, March 06, 2006 7:36 AM
  • What recovery model for the database? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:bcd591c6-d168-4d8c-9536-c739d06f1e78@discussions.microsoft.com... > Every time I get this error, at different points of testing inserts and > deletions on my table: > > The transaction log for database 'mydatabase' is full. To find out why > space in the log cannot be reused, see the log_reuse_wait_desc column in > sys.databases > > Why do I keep getting this? All I'm doing is deleting several hundred > thousand records and inserting them into a couple of tables. i > shouldn't have to truncate my log every time or my application bombs > out! > > sys.databases only gives me this info for log_reuse_wait_desc which does > nothing for me: > > LOG_BACKUP >
    Monday, March 06, 2006 8:21 AM
  • Hi

    Just set the Recovery model to Simple
    that should help.

    Best regards
    Frank Uray

    Monday, March 06, 2006 12:17 PM
  • Wait a minute, wait a minute. You can´t suggest setting the Recorvery mode to simple, only because the log is growing and reaching its capacity. That like your backup software is throwing errors all the time and you tell: "Just deinstall the backup software, that should solve the problem with the errors". Perhaps the original poster needs the Full recovery mode, and wants to do a point in time recovery in the near future.

    -jens Suessmeyer.

    Monday, March 06, 2006 1:02 PM
  • I agree.

    If you need to be able to recover your database to a point in time, then you need to keep your logs and back them up if you don't need this level of recovery then you can set your DB to simple. Be aware that you will only be able to restore to the point of you last full backup followed by any differential backup you have performed since.

    Monday, March 06, 2006 11:05 PM
  • We got the same problem but because the Symantec Backup Exec Agent is going into the database and changed the "log_resue_wait" from 0 to 2. Meaning the log file space will not be reused until a Transaction Log file is backup. Transaction Log is reused on the basic of transaction-to-transaction. If the "log_reuse_wait" is set to 2, the log file will not be resued until the Transaction Log file is backup.

    In your case, just perform a transaction log back up. You probably have to back it up more than once. After you back up the transaction log, run the dbcc utility:

    dbcc shrinkdatabase ('Your-Data-Base-Name', TRUNCATEONLY)

    This will reduce your log file size.

    I believe that the only wait to get your transaction log back to normal is to set the option "log_reuse_wait" to 0 meaning nothing. But I search everywhere on the net and SQL 2005 documentation but found nothing.

    Anyone knows how to set this option in sys.databases, please post your solution.

    Sunday, April 22, 2007 9:35 PM
  • Thanks a lot , it saved much of the time & tension, when we are switching to GP .

     

    Asma

    Sunday, June 03, 2007 8:57 AM
  • Hi,

         Can anyone please help me out here?

    I am also getting the same error when trying to carry out the maiantenance plan for backing up my db in SQL 2005.

    My Maintenance plan backs up the database fine, but the scheduled job fails every single time.

    All I get is the error "The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases".

     

    I am doing the following in my maintenance plan:

    check db integrity

    shrink db

    re-organize index

    update statistics

    backup db

    maintenance cleanup

     

    The db uses SIMPLE recovery model.

     

    Any URGENt help is really apprecaited !

     

    Thanks in advance,

    Meena.

     

    Thursday, July 12, 2007 6:32 AM
  • Try This which is mentioned above.

    Take the transaction log back 2 times.

    then run the dbcc shrinkdatabase("dbname', TRUNCATEONLY.)

     

    Thursday, July 12, 2007 6:36 AM
  • Hi Asma,

     Thanks for your prompt reply.

    But my db uses simple recovery model (Earlier it was set to FULL, and later chnaged to SIMPLE).

    So, I am afraid I cannot take a transaction log backup.

     

    I am getting this error :

     

    The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP LOG is terminating abnormally.

     

    Please suggest as what should I try next.

     

    Thanks in advance!

    Meena.

     

    Thursday, July 12, 2007 6:49 AM
  • Hi Meena

     

    Is it ok if the model is changed to full recovery model for ur company.

    Thursday, July 12, 2007 6:57 AM
  • Hi

    I guess it should be okay.

      But just now I found out that model db was using FULL recovery model.

    Also, when I looked at the log_reuse_wait_desc column for model db, it was ahowing "Log Backup".

    Could that have anything to do with the error messages?

     

    So, you are suggesting that I should change the recovery model back to FULL, perform transaction log backup atleast twice, and do the dbcc shrink db command.

     

    Will that solve the problem?

     

    What if again tomorrow the maintenance plans (scheduled job) fails?

     

    Or should I simply change the recovery model for MODEL databas also to be SIMPLE?

     

    Which is more efficient?

     

    Thanks for your reply!

    Meena.

     

     

    Thursday, July 12, 2007 7:06 AM
  • Hi

    Full recovery model are preferred in a scenario when  you want to  restore the db till the point of failure. while that is not the case with simple recovery . Check the details of recovery model .

    http://msdn2.microsoft.com/en-us/library/aa173678(SQL.80).aspx

     

    For the current scenario as mentioned above ; we need to change the log_reuse_wait  from 2 (waiting for log back) to 0 .

    that is the reason the log files space is not realeased. Its waiting for the transaction log backup to be taken

    follow this link for more details. http://msdn2.microsoft.com/en-us/library/ms345414.aspx

     

    Thursday, July 12, 2007 7:32 AM
  • You can use the following steps on certiane frequency to keep your log file small.

     

    Note:

    You have to take a manual backup of your database for disaster recovery.

     

    Code Snippet

    USE YourDatabase;

    GO

    ALTER DATABASE YourDatabase SET RECOVERY SIMPLE;

    GO

    DBCC SHRINKFILE (YourDatabase_Log, 10); --10MB

    GO

    ALTER DATABASE AdventureWorks SET RECOVERY FULL;

    GO

     

    If you really worried about your job execution. At every time execute the job call the following line. It will remove the unwanted memory from the LOG file & you may get some free space on log file.

    Code Snippet

     

    CHECKPOINT;

     

     

    Thursday, July 12, 2007 7:52 AM
  • Hi,

      Thanks for your reply.

     

    I am totally confused now.

     

    Currently my db recovery model is set to be SIMPLE.

    Also the log_reuse_wait_desc  value = NOTHING from the sys.databases.

     

    So, why should I change it back to FULL recovery? (I do understand the need for FULL recovery model so that the db can be restored to the point of failure.

     

    But e are OK with the SIMPLE recovery model.

     

    In that case, why my maintenance plans are failing?

     

    All I am doing is ;

     

    check db integrity

    shrink db

    re-organize index

    update statistics

    backup db

    maintenance cleanup

     

    Is there any additional T-SQL commands I should be adding to any of these tasks mentioned above?

     

    This issue is driving me crazy with the bakcup scheduled job failing each night and not to mention the.page I am receiving.

     

    Any help is appreciated.

     

    Thanks in advance!

    Meena.

     

    Friday, July 13, 2007 5:20 PM
  • HOW does one do that? I have a MSDE2005 db and am getting the same error. Now I cannot log into my AV server.

    • Proposed as answer by Kcolraw Wednesday, August 05, 2009 1:00 AM
    • Unproposed as answer by David DyeModerator Wednesday, June 23, 2010 11:27 AM
    Thursday, September 25, 2008 11:06 AM
  • I got the same error, My database is Simple (always has been), but I figured it out!  My hard drive was full, the log hit the physical drive limit and quit!
    I cleared some room and ran it again, no issue yet!
    Wednesday, August 05, 2009 1:01 AM
  • Hi Frank,

    It works for me. Thanks a lot


    Tuesday, October 11, 2011 12:10 AM
  • sp_helpdb BizTalkDTADb
    ALTER DATABASE BiztalkDTADb
    SET RECOVERY SIMPLE;
    GO
    DBCC SHRINKFILE (BiztalkDTADb_log, 1);
    GO
    sp_helpdb BizTalkDTADb
    GO
    ALTER DATABASE BiztalkDTADb
    SET RECOVERY FULL
    GO
    Sunday, December 15, 2013 6:42 AM
  • sp_helpdb BizTalkDTADb

    ALTER DATABASE BiztalkDTADb
    SET RECOVERY SIMPLE;
    GO

    DBCC SHRINKFILE (BiztalkDTADb_log, 1);
    GO
    sp_helpdb BizTalkDTADb
    GO
    ALTER DATABASE BiztalkDTADb
    SET RECOVERY FULL
    GO

    Why on Earth are you posting a reply to a thread that started in 2006, and which has not been touched for over two years? On top of that you solution is a bad one for several reasons.

    1) You suggest shrinking the log file to 1 MB, which unlikely to be correct. What is correct is difficult to tell, because it depends on the system in question. For a developer database 50 MB can do. For a production database that itself is 1TB, a log file of 100 GB is not unreasonable.

    2) You suggest flipping the recovery model from full to simple and back again. This breaks the log chain, and you will not be able to do point-in-time recovery from this point and on. There are two options here:
    a) You don't care about point-in-recovery. (For instance, because it is a development database on your workstation). In this case keep the database in simple recovery.
    b) Immediately take a full backup, so that you can meet the RPO (Recovery Point Objective) in your SLA (Service Level Agreement.)

    And overall, shrinking database files is a very exceptional thing which you should only do when you know that the file has grown to a size that will not achieve again. This is particularly true for log files, since growing a log file takes time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 15, 2013 10:54 AM