locked
MOSS 2007 Content DB Transaction Log - Unexpected Growth RRS feed

  • Question

  • Well established MOSS environment with nightly scheduled database and transaction log backups.  Roughly 150 GB Content database with 300 odd users and 100 GB allocated to the ContentDB transaction log. 
    Over the past long weekend there appears to have been little user activity.  Saturday night's backup failed. The first Transaction Log Full messages indicate old content was being expired

    "Error running the expiration action on item itemurl  "

    xception from HRESULT: 0x80040E14

       at Microsoft.SharePoint.Library.SPRequest.AddOrUpdateItem(String bstrUrl, String bstrListName, Boolean bAdd, Boolean bSystemUpdate, Boolean bPreserveItemVersion, Boolean bUpdateNoVersion, Int32& plID, String& pbstrGuid, Guid pbstrNewDocId, Boolean bHasNewDocId, String bstrVersion, Object& pvarAttachmentNames, Object& pvarAttachmentContents, Object& pvarProperties, Boolean bCheckOut, Boolean bCheckin, Boolean bMigration, Boolean bPublish)

       at Microsoft.SharePoint.SPListItem.AddOrUpdateItem(Boolean bAdd, Boolean bSystem, Boolean bPreserveItemVersion, Boolean bNoVersion, Boolean bMigration, Boolean bPublish, Boolean bCheckOut, Boolean bCheckin, Guid newGuidOnAdd, Int32& ulID, Object& objAttachmentNames, Object& objAttachmentContents, Boolean suppressAfterEvents)

       at Microsoft.SharePoint.SPListItem.UpdateInternal(Boolean bSystem, Boolean bPreserveItemVersion, Guid newGuidOnAdd, Boolean bMigration, Boolean bPublish, Boolean bNoVersion, Boolean bCheckOut, Boolean bCheckin, Boolean suppressAfterEvents)

       at Microsoft.SharePoint.SPListItem.SystemUpdate()

       at Microsoft.Office.RecordsManagement.PolicyFeatures.ExpirationTask.Run(SPSite site)

    Even with this activity our normal daily Transaction Log utilization is under 20 GB.  After a successful Full backup of the database and transaction log this morning there is only 15 GB free space in the data files, so I find it difficult to account for 100 GB transaction log writes on a low activity day even oif all 15 GB wsa expired (which it wasn't) 

    What other activity could contribute to this excessive utilization?



    David McKenzie

    Tuesday, May 28, 2013 7:21 PM

Answers

  • Hi David McKenzie,

    The cause of a full transaction log may vary and depends as well on several other conditions and relations such as high loaded content to the database, concurrent backup jobs <br> and in relation to the recovery model and its according configuration. Therefore there is no “one-fits-all” answer or a fixed formula on certain settings for the transaction logs.

    The transaction log file ensures the consistency of the database. Operations in an SQL database are logged (or recorded) at the physical and logical level in terms of what happens in the storage structures of the database. Each change to the storage structures has its own log record, which describes the structure being changed and what the change was. This is done in such a way that the change can be replayed or reversed, if necessary. The log records are stored in a special file called the transaction log.

    This means that even on power loss the database integrity will remain because of only writing committed transaction to the database. If for any reason a transaction is not completed it will not be written to the database. Typically, the size of the transaction log file stabilizes when it can hold the maximum number of transactions that can occur between transaction log truncations that either checkpoints or transaction log backups trigger. However, in some situations the transaction log may become very large and run out of space or become full as in your case.

    Such a problem may occur because of some of the following reasons:
    • Uncommitted transactions
    • Extremely large transactions
    • Operations: DBCC DBREINDEX and CREATE INDEX (in general: indexing of the database)
    • While restoring from transaction log backups
    • Client applications do not process all results
    • Queries time out before a transaction log completes the expansion

    One other likely cause of the log filling up is a long-running transaction. A long running transaction keeps the transaction log active from the virtual log file containing the first log record of the transaction. Truncation cannot occur from that virtual log file onward and therefore the log keeps on growing up to the limit of the available disk space. On very first start of SharePoint, it might happen that huge uploads and/or changes in content causing the transaction logs to grow faster than the database operations could be truncated. In that case, the log file will grow up extremely and will end up in a full disk. On normal usage of SharePoint and with a frequent full backup of databases and logfiles the growing of the transactional log files should be in relation to the database changes and no problem is expected.

    A transaction log can also grow extremely when the database recovery model is not set appropriate or a full log file backup has not taken place yet. From our experience on daily business we may recommend you to have at least 2.5 times more free space on your disk as your databases are in size. This is a “best practice” value we discovered especially when change logs (Content database) and transaction logs are being processed. It can happen that a temporarily processing of the database changes and/or transaction may take a multiple of disk space than the original database is, to commit all transactions. Once this happened and a backup of the log files have been done, the usual size should not exceeding that extremely anymore.

    To find the correct settings, you need to have go through the provided and published resources, links and papers to determine your best applicable settings. SharePoint configures while setup the best and correct values for your databases when created via the wizard and from the SharePoint central admin page.

    We also recommend to check your event and other log files for long running operations that may have started around the timeframe your logs started growing.

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

    if you need to trace your process for this transaction log process, you may open a professional case to us, then we may have more detail information and better solution for your issue.


    Regards,
    Aries
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Emir Liu Thursday, June 6, 2013 9:08 AM
    Thursday, May 30, 2013 3:17 AM

All replies

  • Hi,

    Thank you for your post.

    I'm trying to involve someone familiar with this topic to further look at this issue.

    Thanks & Regards,
    Emir Liu
    TechNet Subscriber Support in forum
    If you have any feedback on our support, please click here.


    Emir Liu
    TechNet Community Support

    Wednesday, May 29, 2013 2:50 AM
  • hi David McKenzie,

    there are some articles regarding this excessive usage of transaction logs,

    as I know, transaction logs write all the activity/transaction to sql and vice versa, so it may become excessive.

    a workaround that may help is to put transaction log to recovery mode,

    here is some example to put as recovery mode for the transaction logs.

    http://geekswithblogs.net/RogueCoder/archive/2008/06/03/122588.aspx


    Regards,
    Aries
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, May 29, 2013 3:26 AM
  • I am looking for some more specific guidance as to what activity generated 100 GB of writes to the SharePoint Content DB in24 hours.  This is abnormal behavior.

    Setting recovery mode to Simple is neither a Microsoft recomended circumvention (see the MS response in the blog cited) or a viable option for the production environment here.  Our recovery objective is no more than two minutes data loss.

    We backup the transaction logs nightly and in normal circumstances 100 GB would be adequate for at least a week, more likely two.


    David McKenzie

    Wednesday, May 29, 2013 12:32 PM
  • Hi David McKenzie,

    The cause of a full transaction log may vary and depends as well on several other conditions and relations such as high loaded content to the database, concurrent backup jobs <br> and in relation to the recovery model and its according configuration. Therefore there is no “one-fits-all” answer or a fixed formula on certain settings for the transaction logs.

    The transaction log file ensures the consistency of the database. Operations in an SQL database are logged (or recorded) at the physical and logical level in terms of what happens in the storage structures of the database. Each change to the storage structures has its own log record, which describes the structure being changed and what the change was. This is done in such a way that the change can be replayed or reversed, if necessary. The log records are stored in a special file called the transaction log.

    This means that even on power loss the database integrity will remain because of only writing committed transaction to the database. If for any reason a transaction is not completed it will not be written to the database. Typically, the size of the transaction log file stabilizes when it can hold the maximum number of transactions that can occur between transaction log truncations that either checkpoints or transaction log backups trigger. However, in some situations the transaction log may become very large and run out of space or become full as in your case.

    Such a problem may occur because of some of the following reasons:
    • Uncommitted transactions
    • Extremely large transactions
    • Operations: DBCC DBREINDEX and CREATE INDEX (in general: indexing of the database)
    • While restoring from transaction log backups
    • Client applications do not process all results
    • Queries time out before a transaction log completes the expansion

    One other likely cause of the log filling up is a long-running transaction. A long running transaction keeps the transaction log active from the virtual log file containing the first log record of the transaction. Truncation cannot occur from that virtual log file onward and therefore the log keeps on growing up to the limit of the available disk space. On very first start of SharePoint, it might happen that huge uploads and/or changes in content causing the transaction logs to grow faster than the database operations could be truncated. In that case, the log file will grow up extremely and will end up in a full disk. On normal usage of SharePoint and with a frequent full backup of databases and logfiles the growing of the transactional log files should be in relation to the database changes and no problem is expected.

    A transaction log can also grow extremely when the database recovery model is not set appropriate or a full log file backup has not taken place yet. From our experience on daily business we may recommend you to have at least 2.5 times more free space on your disk as your databases are in size. This is a “best practice” value we discovered especially when change logs (Content database) and transaction logs are being processed. It can happen that a temporarily processing of the database changes and/or transaction may take a multiple of disk space than the original database is, to commit all transactions. Once this happened and a backup of the log files have been done, the usual size should not exceeding that extremely anymore.

    To find the correct settings, you need to have go through the provided and published resources, links and papers to determine your best applicable settings. SharePoint configures while setup the best and correct values for your databases when created via the wizard and from the SharePoint central admin page.

    We also recommend to check your event and other log files for long running operations that may have started around the timeframe your logs started growing.

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

    if you need to trace your process for this transaction log process, you may open a professional case to us, then we may have more detail information and better solution for your issue.


    Regards,
    Aries
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Emir Liu Thursday, June 6, 2013 9:08 AM
    Thursday, May 30, 2013 3:17 AM
  • Did you ever determine what was causing the tlog to grow that large?  We have a similar problem with our tlog growing up to 100 GB each night even though a full backup is performed nightly and the content is very static.

    Thanks

    Wednesday, May 20, 2015 2:09 PM
  • Honestly, we never did.  What we did do was convert the Content Database recovery model to Simple.

    Being that we did not ship the transaction logs and have a different hot recovery methodology, we determined that the tlogs were not providing much benefit.

    Your milage may vary


    David McKenzie

    Wednesday, May 20, 2015 9:52 PM