none
The transaction log for database 'BizTalkMsgBoxDb' is full.

    Question

  • Hi All,

    We are getting the following error continously in the event viewer of our UAT servers. I checked the jobs and all the backup jobs were failing on the step to backup the transaction log file and were giving the same error. Our DBA's cleaned the message box manually and backed up the DB but still after some time the jobs starts failing and this error is logged in the event viewer.

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

    Thanks,


    Abdul Rafay http://abdulrafaysbiztalk.wordpress.com/ Please mark this answer if it helps
    Sunday, June 07, 2009 9:13 AM

Answers

  • HI Abdul,

    I too got the same issue in production server.I just ran the below query.

     

    USE BiztalkMsgBoxDb;

    GO

    -- Truncate the log by changing the database recovery model to SIMPLE.

    ALTER DATABASE BiztalkMsgBoxDb

    SET RECOVERY SIMPLE;

    GO

    -- Shrink the truncated log file to 1 MB.

    DBCC SHRINKFILE (BiztalkMsgBoxDb_Log, 2);

    GO

     

    This work safe and perfectly.Tell me if this works for u..

    Tuesday, March 08, 2011 11:04 AM

All replies

  • Is the db/log file size limited on the SQL side?
    What is the load on the system like?
    Is this the only error described in the job's history?


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Monday, June 08, 2009 12:43 PM
  • add following step to job “DBCC SHRINKFILE (BizTalkMsgBoxDb_log, 0)"
    KiranMP
    Monday, June 08, 2009 1:10 PM
  • Hi,

    Thanks for the replies.

    @KiranMP
    Adding the step will change what? Why is the error coming?

    @Jozef
    Yes the file size is limited to , This is our UAT server and the load is not that much nearly about 1000 messages each day. Yes this is the only error in the job history and the same error in the Event Log.

    Thanks,


    Abdul Rafay http://abdulrafaysbiztalk.wordpress.com/ Please mark this answer if it helps
    Monday, June 08, 2009 1:17 PM
  • well that step will shrink the free space but in your case if messages are few then log should not grow much, are they huge files?
    KiranMP
    Monday, June 08, 2009 1:29 PM
  • Hi,

    I am also getting this error "The transaction log for database 'BizTalkMsgBoxDb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"  If I tried to Import 6000 records from Biztalk to CRM. 

    Once I executed the below script & restarted the host instances, Process is running fine.  Please advice on this issue.

     

    sp_helpdb ‘BizTalkMsgBoxDb’

    ALTER DATABASE BiztalkMsgBoxDb

    SET RECOVERY SIMPLE;

    GO

    DBCC SHRINKFILE (BiztalkMsgBoxDb_log, 1);

    GO

    sp_helpdb ‘BizTalkMsgBoxDb’

    GO

    ALTER DATABASE BiztalkMsgBoxDb

    SET RECOVERY FULL

    GO

     

    Thanks,

    Lakshmi

    Thursday, September 16, 2010 11:34 AM
  • Hi Lakshmi,

    The error message clearly says "The transaction log for database 'BizTalkMsgBoxDb' is full" . So you need to reduce the size of this transaction log file to work properly.

    Shrinking the transaction log file for BizTalk message box database named 'BiztalkMsgBoxDb_log' is one way to reduce its physical size by removing one or more inactive virtual log files.

    You are doing the same in your script using the T-SQL statement

    DBCC SHRINKFILE(BiztalkMsgBoxDb_log, 1); to shrink the transaction log file.

    To know more about Shrinking of Transaction log file, please have a look at this link

    http://msdn.microsoft.com/en-us/library/ms178037.aspx

    Regards,

    Abhijit


    Please "Mark as Answer" if Post has Answered the Question
    Thursday, September 16, 2010 12:20 PM
  • Putting the database into simple recovery mode and shrinking the log file isn't going to help: it'll just grow again, it will probably fragment across the disk thereby impacting performance and, eventually, it will fill up again for the same reason as before.  Plus you put yourself in a very vulnerable position for disaster recovery if you change the recovery mode of the database: and that's before we've addressed the distributed transaction aspect of the BizTalkDatabases.

    First, make sure you're backing up the log file using the BizTalk job Backup BizTalk Server (BizTalkMgmtDb).  It might be that the log hasn't been backed up and is full of transactions: and, eventually, it will run out of space.  Configuration instructions at this link: http://msdn.microsoft.com/en-us/library/aa546765(v=bts.70).aspx  Your DBA needs to get the backup job running properly rather than panicking!

    If this is running properly, and backing up (which was the case for me) and the log file is still full, run the following query:

    SELECT Name, log_reuse_wait_desc

    FROM sys.databases

    This will tell you why the log file isn't properly clearing down and why it cannot use the space inside.  When I had this issue, it was due to an active transaction.

    I checked for open transactions on the server using this query:

    SELECT

     

    s_tst.[session_id],

    s_es

    .[login_name] AS [Login Name],

     

    DB_NAME (s_tdt.database_id) AS [Database],

    s_tdt

    .[database_transaction_begin_time] AS [Begin Time],

    s_tdt

    .[database_transaction_log_record_count] AS [Log Records],

    s_tdt

    .[database_transaction_log_bytes_used] AS [Log Bytes],

    s_tdt

    .[database_transaction_log_bytes_reserved] AS [Log Rsvd],

    s_est

    .[text] AS [Last T-SQL Text],

    s_eqp

    .[query_plan] AS [Last Plan]

    FROM

     

    sys.dm_tran_database_transactions s_tdt

     

    JOIN sys.dm_tran_session_transactions s_tst

     

    ON s_tst.[transaction_id] = s_tdt.[transaction_id]

     

    JOIN sys.[dm_exec_sessions] s_es

     

    ON s_es.[session_id] = s_tst.[session_id]

     

    JOIN sys.dm_exec_connections s_ec

     

    ON s_ec.[session_id] = s_tst.[session_id]

     

    LEFT OUTER JOIN sys.dm_exec_requests s_er

     

    ON s_er.[session_id] = s_tst.[session_id]

     

    CROSS APPLY sys.dm_exec_sql_text (s_ec.[most_recent_sql_handle]) AS s_est

     

    OUTER APPLY sys.dm_exec_query_plan (s_er.[plan_handle]) AS s_eqp

    ORDER

     

    BY [Begin Time] ASC;

    GO

     

    And this told me the spid of the process with an open transaction on BizTalkMsgBoxDB (in my case, this was something that had been open for several days).  I killed the transaction using KILL spid, where spid is an integer.  Then I ran the BizTalk Database Backup job again, and the log file backed up and cleared properly.

    Incidentally, just putting the database into simple transaction mode would have emptied the log file: giving it lots of space to fill up again.  But it doesn't deal with the root cause: why the backups were failing in the first place.

    • Proposed as answer by NiklasM Tuesday, September 24, 2013 1:25 PM
    Tuesday, March 08, 2011 10:32 AM
  • HI Abdul,

    I too got the same issue in production server.I just ran the below query.

     

    USE BiztalkMsgBoxDb;

    GO

    -- Truncate the log by changing the database recovery model to SIMPLE.

    ALTER DATABASE BiztalkMsgBoxDb

    SET RECOVERY SIMPLE;

    GO

    -- Shrink the truncated log file to 1 MB.

    DBCC SHRINKFILE (BiztalkMsgBoxDb_Log, 2);

    GO

     

    This work safe and perfectly.Tell me if this works for u..

    Tuesday, March 08, 2011 11:04 AM
  • Hi,

    DBCC SHRINKFILE takes two parameters, file name and size. So to truncate to 1 Mb your command is DBCC SHRINKFILE (BiztalkMsgBoxDb_Log, 1 ); Example shows 2 for file size resulting in log file size of 2 Mb (slight difference). If you execute this in SQL Management Studio it works.

    Regards,

    Steef-Jan Wiggers - MVP & MCTS BizTalk Server
    blog: http://soa-thoughts.blogspot.com/

     


    BizTalk
    Tuesday, March 08, 2011 12:24 PM
    Moderator