Log Sql Server Transactions (Queries + username)
1
vote
2
answers
387
views
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.. :/
Asked by user170893
(11 rep)
Jan 24, 2019, 04:48 PM
Last activity: Mar 15, 2025, 04:09 AM
Last activity: Mar 15, 2025, 04:09 AM