MSSQL: Find Biggest Transaction size

Posted on 2009-04-27
Last Modified: 2012-05-06
Hi experts!

I have a transactional backup plan in place on a 2005 MSSQL Server and I would like to know the biggest transaction size that ever happend to have a pretty good idea of the "worst case scenario".  Is this an information we can get? Thanks for the help.
Question by:lali_murray
    LVL 60

    Accepted Solution

    Not really info you can get without some sort of 3rd party tool such as lumigents log explorer...and even then, not sure you could find the "biggest" transaction.  I assume you mean the longest running?

    Stuff like this is better suited to capture with a trace, rather than going back and trying to read the logs.

    Author Comment

    The longuest running transaction would be nice to have, but I have a MRP job running nightly and it generate quite a bit of transactions. The logs are getting pretty big at this moment and I would like to be able to find out the size of the biggest transaction so I have an idea of how much the log file should grow without being too fragmented
    LVL 60

    Expert Comment

    log files don't get fragmented like a data files does...they are written sequentially.  

    if your log files are getting too large, consider doing more frequent log backups...that will keep the log file smaller.

    Author Closing Comment

    Thanks for the fast reply

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now