Post reply

Want to trace what transactions happend in database during certain time

  • September 11, 2008 at 5:01 am

    #192579

    Dear All,

    One of the report in my website was showing 20 records in the afternoon (which was wrong - 17 records were old and not supposed to be shown in report). Now its showing me only 3 reocrds which is correct.

    I want to find out what has happend in the afternoon..

    How can I trace it..? How will I use LOG file for this purpose..? (I have never worked with LOG file before...)

    Please help....

    Thanks,

    Santhosh Nair.

  • September 11, 2008 at 6:27 am

    #870257

    Unless you have some form of auditing already in place, you won't be able to see what happened. The error log just stored details of errors and informational messages.

    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

  • September 11, 2008 at 9:07 am

    #870396

    Can you explain a bit more in detail (Auditing).

    So that I will take all the precautions and

    have some clue atleast henceforth.

    Thanks..

    Santhosh.

  • September 11, 2008 at 9:25 am

    #870409

    You need to decide what you want to audit. There's no automatic way to do it.

    If you want to see all queries executed, that means some form of profiler trace. If you want to see changes to a table, it probably requires a trigger.

    First decide what the purpose of the auditing is, what you want to audit, and then consider how you want to do it.

    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

  • September 11, 2008 at 2:47 pm

    #870648

    You might be able to get that data out of the transaction log. You'll need a log parser for that. Lumigent and ApexSQL both have log parsing products.

    As for auditing and logs and all that, these two articles have data on that:

    http://qa.sqlservercentral.com/articles/Auditing/63247/

    http://qa.sqlservercentral.com/articles/Auditing/63248/

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • September 12, 2008 at 6:05 am

    #870884

    Red Gate also offers a log reading product. It works pretty well.

    Gail's right though, while something like Red Gate's Log Explorer can help, auditing is a more intentional act that you need to build into your design.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • September 12, 2008 at 8:54 am

    #871047

    I'm told RedGate's log parser doesn't work on SQL 2005 (a RedGate employee told me this), and that they aren't continuing the product. That's why I don't recommend that one.

    This is from the RedGate site:

    SQL Log Rescue is compatible with SQL Server 2000 and is now available as a free download. Forum support is available to help answer your questions about the tool. Support contracts can no longer be purchased for this tool, but customers who own a valid support contract for SQL Log Rescue will continue to receive full support until the end of their contracted period.

    So, I recommend either ApexSQL (http://www.apexsql.com/sql_tools_log.asp), or Lumigent (http://www.lumigent.com/products/log_explorer.html). Apex has a free trial (with some limitations). Lumigent won't even give you pricing data without talking to a salesperson.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • September 12, 2008 at 9:06 am

    #871063

    Oh wow! I wasn't aware of that. Sorry.

    I haven't used the thing in years, or needed one. I guess that's a good thing.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • September 12, 2008 at 10:50 am

    #871153

    The only reason I know is because it came up when I was writing my articles about database auditing.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 9 posts - 1 through 8 (of 8 total)

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