Scenario:
I was at a new client, with their previous and only DBA / DEVELOPER/ MASTER OF ALL of 8 years all suddenly gone with no documentation.
Their business critical application was having data integrity issues. Some data were updated incorrectly while some others were not updated at all, showing old or incorrect data to their users/customers/vendors etc...
After going through their stored procedures, triggers, view, ETLs, SQL trace etc... I was able to track down and fix most of the data issues.
But there was one particular table that I could not figure out. I thought maybe that table is never updated? How can I be sure what is going on with it?
I decided I need to setup something to automatically monitor this table for few days. Here, SQL server offers few options:
- Profiler trace
- Server side trace
- Extended events
- Audits
- Triggers
- CDC/CTC
- Service brokers
- ..........
I knew that I only need to know how this particular table is being updated. I don't need to know the actual data per column that is being updated, which is where triggers, CDC, service brokers, trace etc... can come handy.
I decided that using the Audi feature would be the best option here for me. It would tell me exactly what I needed to know with as little overhead as possible, it was quick and easy to setup as well.
In the following sample script I am using it to audit data changes in a particular table. You could however use it to audit multiple tables and/or also audit actions against Views, Stored procedures, Inline functions, Scalar functions, Table-valued functions, Aggregate functions, Sequences etc.
-- FIRST , WE NEED TO DEFINE THE AUDIT FILE WHERE THE LOG ENTRIES WILL BE STORED
-- TO CREATE SERVER AUDIT, YOU NEED TO BE IN THE MASTER DATABASE
USE[master]
GO
CREATE SERVER AUDIT[Audit-Data-Changes]
TO FILE
( FILEPATH =N'E:\MSSQL\Audit\'
,MAXSIZE = 100 MB
,MAX_FILES = 5
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY =1000
,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT[Audit-Data-Changes] WITH (STATE = ON)
GO
-- NOW SWITCH TO THE DATABASE WHERE THE TABLE IS
-- HERE WE ARE GOING TO DEFINE WHICH OBJECT AND APPLICABLE ACTIONS WE WANT TO LOG
USE[DB_NAME]
GO
CREATE DATABASE AUDITSPECIFICATION [DatabaseAuditSpecification-Data-Changes]
FOR SERVER AUDIT[Audit-Data-Changes]
ADD (DELETE ON OBJECT::[dbo].[Table1] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[Table1] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[Table1] BY [public])
WITH (STATE = ON)
GO
-- After running the Audit for two days, I ran the following query and there was the information I needed.
-- READING THE LOG ENTRIES
DECLARE@audit_name NVARCHAR(1000) = 'Audit-Data-Changes';
DECLARE@audit_log_file NVARCHAR(4000)
SELECT@audit_log_file =CONCAT(log_file_path, name,'*') FROM sys.server_file_audits WHERE name = @audit_name AND is_state_enabled =1 ORDER BYaudit_id DESC
SELECT ac.name ,ac.class_desc ,alf.* FROM sys.fn_get_audit_file(@audit_log_file, DEFAULT, DEFAULT) alf
LEFT JOIN sys.dm_audit_actions ac ONac.action_id =alf.action_id ANDac.action_in_log =1
ORDERBY event_time DESC;
GO
