Overview

Audit and Tracing aim to track what is happening in the database. As such there are various ways how this can be accomplish.

Tracing

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:

SQL Profiler

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

General

Trace Name (Name of the JOB which you will see in sys.traces)

If you want to use template

If you want to save the output to a file or table

Events Selects

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:

SQL:StmtCompleted. That will track ALL user DMLs

SP:Completed. That will track any Stored procedure which might be executed on that table

RPC:Completed

On that second tab you will find also a button called: “Column Filters” Here you can configure the database and the table:

Column Filters

In that view, configure:

Database Name: Like: “Database Name”

TextData: “INSERT INTO TABLE_TEST_1%”, “UPDATE TABLE_TEST_1%” and “DELETE TABLE_TEST_1%”. That will trace any DML on that table.

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:

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: