Log Sql Server Transactions (Queries + username)

Question

I need to see which user has made what query with a lookback of ~90(?) days.

I have activated change tracking on the database with 90 days.

I have found several solutions:

SELECT 
    deqs.*, 
    dest.*, 
    deqs.last_execution_time AS [Time], 
    dest.TEXT AS [Query] 
FROM 
    sys.dm_exec_query_stats AS deqs 
CROSS APPLY
    sys.dm_exec_sql_text(deqs.sql_handle) AS dest 
ORDER BY
    deqs.last_execution_time DESC

But this query doesn't give me any information about the user who made the query.

Then I got this query

SELECT 
    * 
FROM 
    FN_TRACE_GETTABLE('E:\MSSQLServer\MSSQL12.DBNAME\MSSQL\Log\log_29.trc', default) 
WHERE 
    TextData IS NOT NULL 
ORDER BY 
    TransactionID DESC

This give me the username but no statements about DELETE/UPDATE etc.

I read about the SQL Profiler but I can't copy/read the _log.mdf-files because it is always in use.

I need a tip what s the right way to do this.. :/

Answer 1

Change tracking provides an efficient, light-weight data change tracking mechanism. In other words, this only tracks UPDATE, INSERT, DELETE statements; it does not track SELECT statements. It does not track who made the change.

If you truly need to track all DML and DDL statements made against the server, you'll need to implement an Extended Events monitoring solution, but be aware for a server that is heavily used, you will likely affect performance in a negative way.

If you only are interested in DML changes, then change tracking will allow you to understand what changes were made to the data using the CHANGETABLE T-SQL statement. This sample code shows how that works. The code drops any database named ChangeTrackingTest first, then creates that same database with a single table that has change tracking enabled.

USE master;
GO
DROP DATABASE ChangeTrackingTest
GO
CREATE DATABASE ChangeTrackingTest;
ALTER DATABASE ChangeTrackingTest
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO
USE ChangeTrackingTest;
GO
CREATE TABLE dbo.t
(
    id int NOT NULL
        PRIMARY KEY
        CLUSTERED
);
ALTER TABLE dbo.t ENABLE CHANGE_TRACKING;

INSERT INTO dbo.t (id) VALUES (1);
UPDATE dbo.t SET id = 2 WHERE id = 1;

SELECT current_id = t.id
    , ctc.*
FROM dbo.t t
    CROSS APPLY CHANGETABLE(CHANGES dbo.t, NULL) ctc
GO

The results:

╔════════════╦════════════════════╦═════════════════════════════╦══════════════════════╦════════════════════╦════════════════════╦════╗
║ current_id ║ SYS_CHANGE_VERSION ║ SYS_CHANGE_CREATION_VERSION ║ SYS_CHANGE_OPERATION ║ SYS_CHANGE_COLUMNS ║ SYS_CHANGE_CONTEXT ║ id ║
╠════════════╬════════════════════╬═════════════════════════════╬══════════════════════╬════════════════════╬════════════════════╬════╣
║          2 ║                  2 ║                           1 ║ D                    ║ NULL               ║ NULL               ║  1 ║
║          2 ║                  2 ║                           2 ║ I                    ║ NULL               ║ NULL               ║  2 ║
╚════════════╩════════════════════╩═════════════════════════════╩══════════════════════╩════════════════════╩════════════════════╩════╝

Cleanup:

USE master;
GO
DROP DATABASE ChangeTrackingTest

Answer 2

I've used CT in past which created more issues in terms of performance rather than data I was expecting. Again it depends how heavy the system goes under the load.

Coming back to your question to track the queries x days ago, I would suggest using sp_whoisactive from Adam Mechanic. You can use this to capture the sessions coming in with all various details by running it via sql agent job say every 5 mins and later query the table to look for data. You can do it as mentioned here and here

Additionally I believe you can use light weight events within extended events to capture that info for you. Also starting sql 2016 you can make use of Query store which is great for analysing query performance.