16 августа 2012 г. 20:29Using SQL Server 2008 R2 - Newbie - Is there a query that I can run against a system database to tell who executed a query against what database in a specific time period? Want to see how many times a particular user is running queries. Any help is greatly appreciated.
16 августа 2012 г. 21:47
You cannot tell this after the fact. You can tell how many times a certain query has been excecuted, if the query plan is still in cache, but you cannot tell by which user and when, unless you can draw conclusions from the query text. Here is such a query:
; WITH basedata AS (
SELECT qs.statement_start_offset/2 AS stmt_start,
qs.statement_end_offset/2 AS stmt_end,
est.encrypted AS isencrypted, est.text AS sqltext,
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est
CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'
WHEN stmt_start >= 0
THEN substring(sqltext, stmt_start + 1,
WHEN 0 THEN datalength(sqltext)
ELSE stmt_end - stmt_start + 1
END AS Statement
Note that the queries in many cases could reside in stored procedures.
If you want to trace a certain user during a certain time, you need to set up a trace in advance.
Erland Sommarskog, SQL Server MVP, firstname.lastname@example.org
17 августа 2012 г. 12:32Thank you - I appreciate your help.