Post reply

Transaction Log file suspicious high

  • December 21, 2011 at 10:05 pm

    #252391

    Hi everybody, thanks in advance for your answers.

    Hey i'm really just new as a DBA but definitely something strange happened here.

    Last night my server was running out of space, quickly i checked the folders where my backups are regularly performed, and suddenly i noticed that one of my transactional backups was really huge (4 Gb) and the rest was around 500 MB, my question is why that specific log backup was different? any ideas?

    My full backup was normal around 7 GB.

    Regards and thanks.

  • December 21, 2011 at 10:58 pm

    #1424577

    You need to know what was going on before that transaction log backup. I've seen that happen when Index Reorganizations were done on some really large indexes. It could also happen if a lot of deletes were being done on a very large table. Basically, you are looking for an unusually large number of fully-logged transactions.

    Note: every statement in SQL is a transaction. Most transactions are implicit. You can define an explicit transaction with BEGIN TRANSACTION / COMMIT. Any transaction which changes something gets saved in the transaction log.

  • December 21, 2011 at 11:14 pm

    #1424582

    Ok, Cool let me start with that and l'll update my results here asap, thanks David.

  • December 22, 2011 at 3:50 am

    #1424687

    My gut feel would be index rebuilds. They're usually the culprit for stuff like this

    Basically, you are looking for an unusually large number of fully-logged transactions.

    Even a huge minimally logged operation could cause the log backups to be large. Minimally logged means the log space used is lower, but the log backups afterwards are larger because they have to include the pages that were affected by the minimally logged operation.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 22, 2011 at 8:10 am

    #1424813

    Ok, l'm researching on this matter, probably in one hour l'll have a better idea, thanks

  • December 22, 2011 at 8:43 am

    #1424832

    I was checking jobs but nothing comes up, any idea how can i know if indexes were rebuild or activity related with high transactional activity.

    Thanks

  • December 22, 2011 at 8:52 am

    #1424836

    islas.tonatiuh (12/22/2011)

    I was checking jobs but nothing comes up, any idea how can i know if indexes were rebuild or activity related with high transactional activity.

    Thanks

    1 case I know of is in Ms Dynamics Nav where you can start a rebuild all indexes from the GUI.

    Maybe you could check the stats dates to see if you have a buttload of them updated almost at the same time (within minutes)

  • December 22, 2011 at 8:59 am

    #1424839

    So, if i'm take a look into the stats dates l'm going to have a better idea about what happened?

  • December 22, 2011 at 9:07 am

    #1424842

    islas.tonatiuh (12/22/2011)

    I was checking jobs but nothing comes up, any idea how can i know if indexes were rebuild or activity related with high transactional activity.

    Thanks

    This query will summarize what objects and operations are currently logged.

    use [my_db];

    select allocunitname, operation, context

    -- , sum([log record length])sum_log_record_length

    from fn_dblog(null, null)

    group by allocunitname, operation, context

    order by allocunitname, operation, context;

    allocunitname operation context

    ------------------------------ ------------------------------- -------------------------------

    MYDB.CLIENT.IX_CLIENT_NAME LOP_DELETE_ROWS LCX_MARK_AS_GHOST

    MYDB.CLIENT.IX_CLIENT_NAME LOP_INSERT_ROWS LCX_INDEX_LEAF

    MYDB.CLIENT.IX_CLIENT_NAME LOP_SET_BITS LCX_PFS

    MYDB.CLIENT.PK__CLIENT__30A40E8 LOP_DELETE_ROWS LCX_MARK_AS_GHOST

    MYDB.CLIENT.PK__CLIENT__30A40E8 LOP_INSERT_ROWS LCX_CLUSTERED

    . . .

    . . .

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • December 22, 2011 at 9:12 am

    #1424845

    Great Sir, let me try it and come back with more details.

    Thanks

  • December 22, 2011 at 9:23 am

    #1424851

    Very cool query, Mr. Russell. I did not know you could do that. The only problem in applying it to this particular problem is that the transactions he is interested in are no longer in the transaction log. They have all been saved to backup and then truncated from the log.

    I am not sure how you would investigate this unless you have some kind of 3rd party database monitoring software. Quest and Redgate both make some good stuff.

  • December 22, 2011 at 9:29 am

    #1424854

    David Moutray (12/22/2011)

    Very cool query, Mr. Russell. I did not know you could do that. The only problem in applying it to this particular problem is that the transactions he is interested in are no longer in the transaction log. They have all been saved to backup and then truncated from the log.

    I am not sure how you would investigate this unless you have some kind of 3rd party database monitoring software. Quest and Redgate both make some good stuff.

    Yes, the query I posted won't tell you what was contained in a transaction log before the backup. However, he can still keep an eye on the size of the log, and run the query today, if the problem reoccurs.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • December 22, 2011 at 9:38 am

    #1424858

    One more question Sir, what's the interpretation for the operation and context columns retrieved from the query?

    I ran it but how can i use those results, because i saw:

    e.g.

    dbo.TransactionLog.PK_TransactionLog LOP_MODIFY_ROW LCX_TEXT_MIX

    dbo.TransactionLog.PK_TransactionLog LOP_MODIFY_ROW LCX_TEXT_TREE

    dbo.TransactionLog.PK_TransactionLog LOP_SET_BITS LCX_GAM

    dbo.TransactionLog.PK_TransactionLog LOP_SET_BITS LCX_IAM

    sys.sysallocunits.clust LOP_COUNT_DELTA LCX_CLUSTERED

  • December 22, 2011 at 9:44 am

    #1424860

    islas.tonatiuh (12/22/2011)

    One more question Sir, what's the interpretation for the operation and context columns retrieved from the query?

    I ran it but how can i use those results, because i saw:

    e.g.

    dbo.TransactionLog.PK_TransactionLog LOP_MODIFY_ROW LCX_TEXT_MIX

    dbo.TransactionLog.PK_TransactionLog LOP_MODIFY_ROW LCX_TEXT_TREE

    dbo.TransactionLog.PK_TransactionLog LOP_SET_BITS LCX_GAM

    dbo.TransactionLog.PK_TransactionLog LOP_SET_BITS LCX_IAM

    sys.sysallocunits.clust LOP_COUNT_DELTA LCX_CLUSTERED

    The function fn_dblog is not officially documented by Microsoft or anywhere near fully documented in blogs. You'll have to google each of those operations to see what you can find on them.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • December 22, 2011 at 9:46 am

    #1424862

    Sorry i noticed your replay 🙂

    ok let me google it

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply