capturing transaction rollbacks using SQL Server 2008 Audit
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?
ASKER
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:-))
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:-))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is the rationale from the source (http://msdn.microsoft.com/en-us/library/dd392015.aspx)
"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."
"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."
ASKER
Thanks for the pointer ...I completely overlooked the existence of the "Change Data Capture" and "Change Tracking" feature (introduced in SQL Server 2008)
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):
BEGIN TRAN
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.