如何查看在sql server studio上运行的所有以前的脚本来跟踪？ [英] How to check all the previous scripts ran on sql server studio to track ?

问题描述

Hii，





我想跟踪其他用户是否有任何不必要的查询运行。



如何跟踪记录？请建议我..第三方工具不是必需的..想要仅使用一些queriys来检查./。



i试过这样的东西..

Hii ,


I want to keep track on wther there are any unnecessary queriys running or not by some other user ..

How can i track the records ? please suggest me .. third party tools are not required .. want to check using some queriys only ./.

i tried somthing like this ..

SELECT t.[text]
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE N'%Insert into EndYearEmpCalibrationHeading%';

但这是会议明智的...我想检查即使会议结束......





互联网上的东西是一个如下，给我查询但不是所有其他细节

but this is session wise ... i want to check even if session ended ...


The thing have got on internet is the one as follow which give me query but not all the other details

SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

我喜欢的另一个是给我所有的细节，但不是查询文本...... c任何人都能帮助我将两者合并在一起..

The other i have fond is gives me all the details but not query text ...c an anybody atlest help me to merge the both the things in one ..

SELECT
    ISNULL( SCHEMA_NAME([schema_id]) + '.','') + ISNULL(OBJECT_NAME(parent_object_id),DBLA.AllocUnitName) AS TableName ,

      DBL.[Begin Time] AS ModificationTime,
      COUNT(*) Total_Rows_Affected,
      MAX(DBL.[Transaction Name]) AS ModificationType,
MAX(SUSER_SNAME(DBL.[Transaction SID])) ModifiedBy

FROM Sys.fn_dblog(NULL,NULL) DBL
INNER JOIN Sys.fn_dblog(NULL,NULL) DBLA
ON  LEFT(DBL.[Current LSN],17) = LEFT(DBLA.[Current LSN],17) LEFT JOIN sys.key_constraints C
ON  SCHEMA_NAME([schema_id]) + '.' + OBJECT_NAME(parent_object_id) + '.' + C.name = DBLA.AllocUnitName

WHERE DBL.[Transaction Name] + '_' + DBLA.Operation IN ('INSERT_LOP_INSERT_ROWS',
'UPDATE_LOP_MODIFY_ROW',
'DELETE_LOP_DELETE_ROWS')

GROUP BY
ISNULL( SCHEMA_NAME([schema_id]) + '.','') + ISNULL(OBJECT_NAME(parent_object_id),
DBLA.AllocUnitName),DBL.[Begin Time]

我想要两个结果在一个...

I want both the results in one ...

推荐答案

在数据库中创建一个执行日志表，并在每个脚本中插入此表。插入一条记录作为脚本中的第一条语句，并在脚本的最后一条语句中更新此记录。

记录启动时间，完成时间，成功/失败，脚本名称，帐户运行等等

Create an Execution Log table in the database, and in each script have an insert into this table. Insert one record as the very first statement in the script, and update this record in the very last statement of the script.
Record the time it was started, time finished, success/failure, name of script, account run under, etc.

这篇关于如何查看在sql server studio上运行的所有以前的脚本来跟踪？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！