1

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.. :/

  • Check out the SQL Server Database Audit functionality that is now available for SQL Standard as of SQL 2016 SP1. You will need Enterprise edition in versions SQL 2012 - SQL 2016 (pre SP1). – John Eisbrener Jan 24 at 17:45
0

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
0

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.

  • sp_whoisactive is not a method of capturing all queries, but just the ones at the point in time it runs. You can certainly run it every second and log it to a table, but you'll miss all of the queries that ran between executions of it, and that could be thousands of queries. – Tony Hinkle Jan 24 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.