在MSSQL数据库中,我试图知道何时为该数据库执行最后一条语句。
那么,是否有任何日志可以用于查看所需的活动记录(可能是事务日志,但我不知道如何从它获取日期)?
发布于 2018-02-21 07:59:00
你会想要使用sys.dm_exec_query_stats车管所。如果您只想看到执行的最后一条语句,就应该开始执行(注意:您需要在数据库的上下文中执行该语句,例如使用USE <db_name>命令):
SELECT
deqs.creation_time
, deqs.last_execution_time
, dest.text
, SUBSTRING(dest.text,
(deqs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(dest.text)
ELSE deqs.statement_end_offset
END
- (deqs.statement_start_offset/2) + 1)))
AS statement_text
, deqp.query_plan
FROM
sys.dm_exec_query_stats AS deqs
CROSS APPLY
sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY
sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE deqs.last_execution_time = (SELECT MAX(last_execution_time)
FROM sys.dm_exec_query_stats)发布于 2018-02-21 07:53:31
您可以尝试以下两个查询
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query], dest.dbid
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或
SELECT conn.session_id, sson.host_name, sson.login_name,
sqltxt.text, sson.login_time, sson.status, sson.database_id
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sson
ON conn.session_id = sson.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS sqltxt
ORDER BY conn.session_id发布于 2018-02-21 09:12:46
迪穆塞的回答中提供的查询只需要与sys.dm_exec_plan_attributes进行额外的连接即可访问所需的DB记录:
SELECT
deqs.creation_time
, deqs.last_execution_time
, dest.text
, SUBSTRING(dest.text,
(deqs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(dest.text)
ELSE deqs.statement_end_offset
END
- (deqs.statement_start_offset/2) + 1)))
AS statement_text
, deqp.query_plan
FROM
sys.dm_exec_query_stats AS deqs
CROSS APPLY
sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY
sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY
sys.dm_exec_plan_attributes(deqs.plan_handle) AS pa
WHERE deqs.last_execution_time = (SELECT MAX(last_execution_time)
FROM sys.dm_exec_query_stats)
AND pa.attribute = 'dbid' AND pa.value = DB_ID()https://dba.stackexchange.com/questions/198439
复制相似问题