Audit and Tracing aim to track what is happening in the database. As such there are various ways how this can be accomplish.
Tracing in SQL Server can be accomplished via SQL Profiler or Manually. SQL Profile is utility in which you can configure a job OR to configure a script which you can run manually. Let's do the 2nd part since it joins the both methods:
As I already mentioned. SQL Profiler is additional utility than SSMS and it can help you with configuring tracing jobs: Once you start it, you have to choose the following
Here you can select filter options, meaning what you want to be monitored. Let's assume you want to monitor all DML on one table: TABLE_TEST_1
In that case, you have to select:
On that second tab you will find also a button called: “Column Filters” Here you can configure the database and the table:
In that view, configure:
After you specify that, start the job. Once you start it, stop it and export the job to a script. It should look something like that:
/****************************************************/ /* Created by: SQL Server 2008 Profiler */ /* Date: 16/10/2019 17:15:23 */ /****************************************************/ -- Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint set @maxfilesize = 5 <- Edit this number or the trace file will be only 5 MB big. -- Please replace the text InsertFileNameHere, with an appropriate -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension -- will be appended to the filename automatically. If you are writing from -- remote server to local drive, please use UNC path and make sure server has -- write access to your network share exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL <- Add the FileName location: (e.g. E:\traceTesttable.trc) if (@rc != 0) goto error -- Client side File and Table cannot be scripted -- Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 10, 7, @on exec sp_trace_setevent @TraceID, 10, 15, @on exec sp_trace_setevent @TraceID, 10, 8, @on exec sp_trace_setevent @TraceID, 10, 16, @on exec sp_trace_setevent @TraceID, 10, 48, @on exec sp_trace_setevent @TraceID, 10, 64, @on exec sp_trace_setevent @TraceID, 10, 1, @on exec sp_trace_setevent @TraceID, 10, 9, @on exec sp_trace_setevent @TraceID, 10, 17, @on exec sp_trace_setevent @TraceID, 10, 25, @on exec sp_trace_setevent @TraceID, 10, 41, @on exec sp_trace_setevent @TraceID, 10, 49, @on exec sp_trace_setevent @TraceID, 10, 10, @on exec sp_trace_setevent @TraceID, 10, 18, @on exec sp_trace_setevent @TraceID, 10, 26, @on exec sp_trace_setevent @TraceID, 10, 50, @on exec sp_trace_setevent @TraceID, 10, 66, @on exec sp_trace_setevent @TraceID, 10, 3, @on exec sp_trace_setevent @TraceID, 10, 11, @on exec sp_trace_setevent @TraceID, 10, 35, @on exec sp_trace_setevent @TraceID, 10, 51, @on exec sp_trace_setevent @TraceID, 10, 4, @on exec sp_trace_setevent @TraceID, 10, 12, @on exec sp_trace_setevent @TraceID, 10, 60, @on exec sp_trace_setevent @TraceID, 10, 13, @on exec sp_trace_setevent @TraceID, 10, 6, @on exec sp_trace_setevent @TraceID, 10, 14, @on exec sp_trace_setevent @TraceID, 45, 7, @on exec sp_trace_setevent @TraceID, 45, 55, @on exec sp_trace_setevent @TraceID, 45, 8, @on exec sp_trace_setevent @TraceID, 45, 16, @on exec sp_trace_setevent @TraceID, 45, 48, @on exec sp_trace_setevent @TraceID, 45, 64, @on exec sp_trace_setevent @TraceID, 45, 1, @on exec sp_trace_setevent @TraceID, 45, 9, @on exec sp_trace_setevent @TraceID, 45, 17, @on exec sp_trace_setevent @TraceID, 45, 25, @on exec sp_trace_setevent @TraceID, 45, 41, @on exec sp_trace_setevent @TraceID, 45, 49, @on exec sp_trace_setevent @TraceID, 45, 10, @on exec sp_trace_setevent @TraceID, 45, 18, @on exec sp_trace_setevent @TraceID, 45, 26, @on exec sp_trace_setevent @TraceID, 45, 50, @on exec sp_trace_setevent @TraceID, 45, 66, @on exec sp_trace_setevent @TraceID, 45, 3, @on exec sp_trace_setevent @TraceID, 45, 11, @on exec sp_trace_setevent @TraceID, 45, 35, @on exec sp_trace_setevent @TraceID, 45, 51, @on exec sp_trace_setevent @TraceID, 45, 4, @on exec sp_trace_setevent @TraceID, 45, 12, @on exec sp_trace_setevent @TraceID, 45, 60, @on exec sp_trace_setevent @TraceID, 45, 5, @on exec sp_trace_setevent @TraceID, 45, 13, @on exec sp_trace_setevent @TraceID, 45, 29, @on exec sp_trace_setevent @TraceID, 45, 61, @on exec sp_trace_setevent @TraceID, 45, 6, @on exec sp_trace_setevent @TraceID, 45, 14, @on exec sp_trace_setevent @TraceID, 45, 15, @on exec sp_trace_setevent @TraceID, 41, 7, @on exec sp_trace_setevent @TraceID, 41, 15, @on exec sp_trace_setevent @TraceID, 41, 55, @on exec sp_trace_setevent @TraceID, 41, 8, @on exec sp_trace_setevent @TraceID, 41, 16, @on exec sp_trace_setevent @TraceID, 41, 48, @on exec sp_trace_setevent @TraceID, 41, 64, @on exec sp_trace_setevent @TraceID, 41, 1, @on exec sp_trace_setevent @TraceID, 41, 9, @on exec sp_trace_setevent @TraceID, 41, 17, @on exec sp_trace_setevent @TraceID, 41, 25, @on exec sp_trace_setevent @TraceID, 41, 41, @on exec sp_trace_setevent @TraceID, 41, 49, @on exec sp_trace_setevent @TraceID, 41, 10, @on exec sp_trace_setevent @TraceID, 41, 18, @on exec sp_trace_setevent @TraceID, 41, 26, @on exec sp_trace_setevent @TraceID, 41, 50, @on exec sp_trace_setevent @TraceID, 41, 66, @on exec sp_trace_setevent @TraceID, 41, 3, @on exec sp_trace_setevent @TraceID, 41, 11, @on exec sp_trace_setevent @TraceID, 41, 35, @on exec sp_trace_setevent @TraceID, 41, 51, @on exec sp_trace_setevent @TraceID, 41, 4, @on exec sp_trace_setevent @TraceID, 41, 12, @on exec sp_trace_setevent @TraceID, 41, 60, @on exec sp_trace_setevent @TraceID, 41, 5, @on exec sp_trace_setevent @TraceID, 41, 13, @on exec sp_trace_setevent @TraceID, 41, 29, @on exec sp_trace_setevent @TraceID, 41, 61, @on exec sp_trace_setevent @TraceID, 41, 6, @on exec sp_trace_setevent @TraceID, 41, 14, @on -- Set the Filters declare @intfilter int declare @bigintfilter bigint exec sp_trace_setfilter @TraceID, 1, 0, 6, N'INSERT INTO %TABLE_TEST_1%' exec sp_trace_setfilter @TraceID, 1, 1, 6, N'update %TABLE_TEST_1%' exec sp_trace_setfilter @TraceID, 1, 1, 6, N'delete %TABLE_TEST_1%' exec sp_trace_setfilter @TraceID, 35, 0, 6, N'DATABASE_NAME' -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish: go
Once you execute that job, you should receive the ID of the job. (Usually “1”). You can contract the job status via the following commands:
--START EXEC sp_trace_setstatus @traceid=1, @status=1 --STOP EXEC sp_trace_setstatus @traceid=1, @status=0 --CLOSE TRACE EXEC sp_trace_setstatus @traceid=1, @status=2