Post reply

IS there any script to find what transactions ran yesterday or week or mont

  • February 15, 2024 at 6:20 am

    #4378966

    Is there any script to find what transactions ran yesterday or week or month before in sqlserver

    we are not using any tools and if any issue comes we can see sp_who2'active' but if log size is full suddenly before 2 days and if we want to find the root cause why log got increased and disk space issue occurred  we cannot see old date transactions occurred

    Thanks
    Naga.Rohitkumar

  • February 15, 2024 at 9:56 am

    #4379062

    You would have to dump the log backups using fn_dump_dblog to see what went on, but it's going to be a huge mess.

    Sounds like you need to enable auditing, but that's going to be for future events, not past.

  • February 15, 2024 at 3:14 pm

    #4379215

    You could/should setup an Extended Event to capture anything (including code) that makes the log file grow.  Of course, you'd have to shrink the log file to a reasonable size first.  A quick search should reveal some good code for the EE in short order.

    Also, what are your growth settings on the log file?  They should NOT be based on percentage.

    As a bit of a sidebar, here are some typical "fun" things that can make your log file explode...

    1. BAD code with accidental many-to-many JOINs due to insufficient criteria, especially on single queries with many CTEs and queries with views/nested views and queries with sub-queries, etc.
    2. The use of REORGANIZE in Index Maintenance plans.
    3. The use of an illegal form of UPDATE for updates that have JOINs.  This illegal form is when there is a join between the table being updated and another table but the table being updated is in the UPDATE clause in the code rather than in the FROM clause with only an alias in the UPDATE clause.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 15, 2024 at 3:49 pm

    #4379244

    In order to have a history of queries, behaviors, what have you, on SQL Server, you have to set up monitoring. You either have to build it out yourself, or you need to pick up a 3rd party tool. Regardless, no, there really isn't any way, after the fact, with no monitoring in place, to know what caused something like a log growth, days after the fact.

    Set up monitoring on your servers.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 20, 2024 at 2:54 pm

    #4381749

    If you got full recovery model, make sure the transaction log backups set up properly.  This causes the disk space issues, too.

     

    DBASupport

Viewing 5 posts - 1 through 4 (of 4 total)

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