capturing transaction rollbacks using SQL Server 2008 Audit

Posted on 2010-01-06
Last Modified: 2012-08-14
SQL Server 2008 Audit allows tracking of specific events and logging them to an audit (file or Application log). It allows tracking of any INSERT, UPDATE or DELETE statement issued against a database for which I define a Database Audit Specification. The events will be logged regardless of whether the DML statements were issued inside of a transaction. How can I tell that a specific DML SQL statement that appears in the Audit log has not been rolled back?
Question by:GeeCR
    LVL 13

    Expert Comment

    I don't have 2008 Enterprise Edition or Dev edition handy at present. Only standard on this machine but are you sure that Audit is only writing the fact that you tried to do a change?

    I would have assumed only committed changes would be written (like in Change Data Capture, a feature introduced at the same time). It would be difficult to audit with rolled back changes.

    You can try a simple experiment that I was going to try until I realized I was on standard with this laptop:

    1.) Create a new simple table someplace
    CREATE TABLE testAudit (ident int, descr varchar(50))

    2.) Insert some rows like:
    1 'Unchanged'
    2 'Unchanged'
    3 'Unchanged'

    3.) Turn on auditing and verify it looks good
    4.) Change one row:
    UPDATE testAudit SET descr = 'Changed_NoExplicitTran' WHERE ident = 1
    5.) Do you see it in audit? You should.
    6.) Update another row in a transaction (that you don't commit):
    UPDATE testAudit SET descr = 'Changed - No Commit') WHERE ident = 2
    7.) Wait for your delay of a second or whatever you setup in the audit and check the audit. Is that change there?
    8.) If it is then I am wrong and we have to look at options. If it isn't there yet, this is promising perhaps.
    9.) Commit that transaction finally and check the audit again. Is it there now? Good
    10.) Do the same test with Ident 3 only this time BEGIN TRAN... Update... ROLLBACK --> is it there?

    I don't use auditing in production but I would not expect to see rolled back or uncommitted changes there yet since they never really happened yet as far as the ACID properties go. They are in process but they haven't been committed and persisted as factual events.

    If the rolled back transactions or transactions pending commits still happen then you have to look at other options.

    Not sure what you are trying to do but Change Data Capture is one maybe to investigate. Another is looking at some of the log reading tools out there like APEX SQL Log.

    Author Comment

    Hello MikeWalsh,
    Thank you for you response.

    As to step 8) of your instructions, the answer is "Yes, the Update gets logged in the Audit right away (or after the Queue Delay value specified in the Audit definition ...2 seconds for me), and it does get logged a long before the transaction gets committed or rolled back. Once the commit or rollback occurs no additional event seems to be logged into the Audit.  

    Sql trace could trap event # 187, 188 but that seems to be out of scope of SQL Server 2008 Audit feature (?)

    On the other hand, It looks like the Change Data Capture feature is the way to go for what I need. (track committed changes:-))
    LVL 13

    Accepted Solution

    That surprises me but I guess there is some rationale that I am just not thinking of. Yeah you could use trace to track rollbacks but that could get complicated.

    You might look at Change Data Capture (again depending on how you are using this) or look at a tool like Apex SQL Log (or really any SQL Log reader, I haven't used one in awhile. I believe Quest makes one as well, at least they did have one for SQL 2000. Lumigent also makes tools for auditing changes. Sentrigo is another vendor.


    Author Comment

    Here is the rationale from the source (

    "Another piece is how auditable events are recorded when they occur within a transaction. The simple way to describe this is: They are always audited. Clearly there would be negative security implications if data could be read, but no audit was recorded because a pending transaction was rolled back. So, the recording of audit events occurs irrespective of transaction scope and whether a rollback occurs or not."

    Author Closing Comment

    Thanks for the pointer ...I completely overlooked the existence of the "Change Data Capture" and "Change Tracking" feature (introduced in SQL Server 2008)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Container Orchestration platforms empower organizations to scale their apps at an exceptional rate. This is the reason numerous innovation-driven companies are moving apps to an appropriated datacenter wide platform that empowers them to scale at a …
    Ransomware continues to be a growing problem for both personal and business users alike and Antivirus companies are still struggling to find a reliable way to protect you from this dangerous threat.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    759 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

    9 Experts available now in Live!

    Get 1:1 Help Now