捕捉一个SQL Server会话的所有语句

2022-06-13 09:07:39

问题：有一件事让我感到特别沮丧，那就是我经常写一些复杂的查询，但是我经常忘记保存它们或者不记得在我运行五个迭代之前的查询是什么样的。一个明智的做法是一直保存着你的脚本，但是当你处在尝试做一堆不同的事情时，你总有可能忘记了某些事情。在本技巧中，我们看看怎样创建一个会话的服务器端跟踪并且完整地捕捉所有运行的脚本，这样你可以找到你认为可能会丢失的复杂查询。

　　首先需要做的是创建两个存储过程，在这个技巧中，我在自己的主数据库上创建它们。它们允许你创建一个服务器端的跟踪，也允许你关掉一个服务器端的跟踪。要了解更多关于服务器端跟踪的信息，请参考前面的技巧“使用一个服务器端得跟踪进行SQL Server 性能统计”。

　　开始跟踪

　　第一个存储过程显示如下，它启动了服务器端的跟踪，但是也排除了你想捕捉的特定会话的SPID。它将创建一个文件名，如“TraceMySession_52_d20090317120912.trc”，以便该跟踪会话是唯一的。默认情况下，它保存在服务器的"C:"盘下，因此你可以把它更改到另一个不同的目录下。建立这个脚本也是为了创建一个5MB的文件然后回滚到另外的文件中。你可以再次检验上面提到的技巧得到更多关于这些设置的信息。

　　我们正在做的另一件事是只捕捉SQL:BatchCompleted事件。用这种方法这个跟踪文件不会变得非常大。捕捉的三个数据字段是TextData，StartTime和SPID。

　　要创建这个存储过程，复制下面的代码并且执行它。正如我所说的，我在主数据库中创建这个程序，但是你也可以在不同的数据库中创建它。

CREATE PROCEDURE [dbo].[spTraceMySessionStart] @spid INT

　　-- Create a Queue

　　DECLARE @rc INT

　　DECLARE @TraceID INT

　　DECLARE @maxfilesize bigint

　　SET @maxfilesize = 5

　　DECLARE @filename NVARCHAR(245)

　　SET @filename = 'C:\TraceMySession_'

　　+ CONVERT(NVARCHAR(10),@spid)

　　+ '_d'

　　+ REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','')

　　+ REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')

　　EXEC @rc = sp_trace_create @TraceID output, 2, @filename, @maxfilesize, NULL

　　IF (@rc != 0) GOTO error

　　-- Set the events

　　DECLARE @on bit

　　SET @on = 1

　　EXEC sp_trace_setevent @TraceID, 12, 1, @on

　　EXEC sp_trace_setevent @TraceID, 12, 12, @on

　　EXEC sp_trace_setevent @TraceID, 12, 14, @on

　　-- Set the Filters

　　DECLARE @intfilter INT

　　DECLARE @bigintfilter bigint

　　EXEC sp_trace_setfilter @TraceID, 12, 1, 0, @spid

　　-- Set the trace status to start

　　EXEC sp_trace_setstatus @TraceID, 1

　　-- display trace id for future references

　　SELECT TraceID=@TraceID

　　GOTO finish

　　error:

　　SELECT ErrorCode=@rc

　　finish:

　　停止跟踪

　　一旦你完成了跟踪，那么下面的这个存储过程将停止和关掉它。如果这不能运行，那么服务器端的跟踪将会继续运行和搜集其它任何正好具有相同SPID的会话数据。

　　这个存储过程排除一个参数，那就是TraceID。当你正好在运行第一个存储过程时，它将给你创建的TraceID。这是你传递到第二个存储过程以便停滞和关掉该跟踪的值。

　　我也把它创建在自己的主数据库中，但是同样地你可以创建在任何一个不同的数据库中。

CREATE PROCEDURE [dbo].[spTraceMySessionStop] @traceId INT

　　EXEC sp_trace_setstatus @traceId,0

　　EXEC sp_trace_setstatus @traceId,2

让我们试试看

　　因此我们可以假设，我们想运行下面的代码。

EXEC master.dbo.spTraceMySessionStart 52

　　USE AdventureWorks

　　SELECT name

　　FROM sys.sysobjects WHERE xtype = 'U'

　　SELECT TOP 10 Title

　　FROM HumanResources.Employee

　　EXEC master.dbo.spTraceMySessionStop 2

首先我们需要做的是启动这个跟踪，也就是我脚本中的第一行。我可以在截图底部看到我的SPID，也就是52，它已经在下面的截图中突出显示了。

　　图一

　　当我运行这个存储过程时，输出结果显示的是TraceID，也就是“2”。我们需要保存这个值以便后面使用。

　　图二

　　在我运行了其它的命令并且全部完成之后，我可以运行后面的命令来停止和关掉这个跟踪并使用我们上面得到的TraceID值“2”。

　　图三

　　查看我们所捕捉的

　　如果我使用探查器打开我们创建的文件，我可以看到在我的会话中运行的所有命令。你可以把这些命令复制到一个查询窗口以便重复使用。

　　图四

　　另一个选择是下载一个创建到SQL Server表的文件或者使用SQL Server只查询这些数据。

　　图五

SELECT TEXTData, StartTime

　　FROM ::fn_trace_gettable('c:\TraceMySession_52_d20090317120542.trc', DEFAULT)

　　WHERE TEXTData IS NOT NULL

码农公寓

相关文章