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 AUDIT
SPECIFICATION [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 BY
audit_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 ON
ac.action_id =
alf.action_id AND
ac.action_in_log =
1
ORDER
BY event_time DESC;
GO