SQL Server 2005: Get full information about transaction locks :
================================================================
http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx
================================================================
Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc...
With the introduction of DMV's in SQL Server 2005 getting this information is quite easy with this query:
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
Let's look at it one DMV at a time from top to bottom:
sys.dm_tran_locks:
Shows us all locks held on all resources for all transaction. We start from this view since we want to get information about locked resources.
sys.partitions:
Contains information about partitions in the database. We can join it to the sys.dm_tran_locks on the hobt_id only for resource_type values 'HOBT', 'Page', 'RID' and 'Key'. With this join we get the object_id of our locked table.
sys.objects:
Contains data for all schema scoped database objects. We join it to sys.partitions to get the Name of the locked object which is usually a table name.
sys.dm_exec_sessions:
Shows data about all active sessions on SQL server. We join it to sys.dm_tran_locks to get user login information for each held lock.
sys.dm_tran_session_transactions:
Shows data for all transactions for a session. By joining in to sys.dm_exec_sessions we get a link to all active transactions in the session.
sys.dm_tran_active_transactions:
Shows data for all active transactions in SQL Server. By joining it to sys.dm_tran_session_transactions we only get information for the session we want.
sys.dm_exec_connections:
Shows data for all connections in SQL Server. By joining it to sys.dm_exec_sessions we get connection info for our session.
sys.dm_exec_sql_text:
Returns SQL statement that is associated with the SQL handle input parameter. By cross applying it to the sys.dm_exec_connections we get the last executed statement for the connection, which in our case is the statement that is holding locks.
By applying the filter in the where clause you get the answers to questions like:
- What SQL Statement is causing the lock?
- Which user has executed the SQL statement that's holding the locks?
- What objects/tables are being locked?
- What kinds of locks are being held and on which pages, keys, RID's?
- etc...
maandag 24 november 2008
usp_FindObject
Create PROC usp_FindObject (
@objname varchar(200) = Null
, @objtype varchar(20) = Null
)
As
Declare @sqlstr nvarchar(200)
-- Insert wildcard, if exact search is not required.
-- Set @objname = '%' + @objname + '%'
-- Its better to supply custom wild card in the input parameter @objname
/* drop the temporary table if already exists */
If Object_Id('tempdb..#tblDBObjects') is Not Null
Drop table #tblDBObjects
/* create temporary table */
Create TABLE #tblDBObjects (
dbName sysname,
objName varchar(200),
objtype char(2)
)
Begin
If @objtype = 'CHECK'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''C'''''''
If @objtype = 'Default'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''D'''''''
If @objtype = 'FOREIGN KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''F'''''''
If @objtype = 'Log'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''L'''''''
If @objtype = 'Scalar function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''FN'''''''
If @objtype = 'Inlined table-function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''IF'''''''
If @objtype = 'Stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''P'''''''
If @objtype = 'PRIMARY KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''PK'''''''
If @objtype = 'Replication filter stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''RF'''''''
If @objtype = 'System table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''S'''''''
If @objtype = 'Table function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''TF'''''''
If @objtype = 'Trigger'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''TR'''''''
If @objtype = 'User table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''U'''''''
If @objtype = 'UNIQUE constraint'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''UQ'''''''
If @objtype = 'View'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''V'''''''
If @objtype = 'Extended stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''X'''''''
If (@objtype = '') Or (@objtype is Null)
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects'''
End
/* execute SQL string */
If (@sqlstr <> '') Or (@sqlstr is Not Null)
Exec sp_executesql @sqlstr
/* If @objname is not supplied it should still return result */
If (@objname = '') Or (@objname is Null)
Select * From #tblDBObjects
Else
Select * From #tblDBObjects Where objName like @objname
RETURN
@objname varchar(200) = Null
, @objtype varchar(20) = Null
)
As
Declare @sqlstr nvarchar(200)
-- Insert wildcard, if exact search is not required.
-- Set @objname = '%' + @objname + '%'
-- Its better to supply custom wild card in the input parameter @objname
/* drop the temporary table if already exists */
If Object_Id('tempdb..#tblDBObjects') is Not Null
Drop table #tblDBObjects
/* create temporary table */
Create TABLE #tblDBObjects (
dbName sysname,
objName varchar(200),
objtype char(2)
)
Begin
If @objtype = 'CHECK'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''C'''''''
If @objtype = 'Default'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''D'''''''
If @objtype = 'FOREIGN KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''F'''''''
If @objtype = 'Log'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''L'''''''
If @objtype = 'Scalar function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''FN'''''''
If @objtype = 'Inlined table-function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''IF'''''''
If @objtype = 'Stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''P'''''''
If @objtype = 'PRIMARY KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''PK'''''''
If @objtype = 'Replication filter stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''RF'''''''
If @objtype = 'System table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''S'''''''
If @objtype = 'Table function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''TF'''''''
If @objtype = 'Trigger'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''TR'''''''
If @objtype = 'User table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''U'''''''
If @objtype = 'UNIQUE constraint'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''UQ'''''''
If @objtype = 'View'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''V'''''''
If @objtype = 'Extended stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''X'''''''
If (@objtype = '') Or (@objtype is Null)
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects'''
End
/* execute SQL string */
If (@sqlstr <> '') Or (@sqlstr is Not Null)
Exec sp_executesql @sqlstr
/* If @objname is not supplied it should still return result */
If (@objname = '') Or (@objname is Null)
Select * From #tblDBObjects
Else
Select * From #tblDBObjects Where objName like @objname
RETURN
vrijdag 21 november 2008
Get sqlcmd output into a table?
CREATE TABLE ServerList_SSIS([Server] NVARCHAR(512),
[Connect] bit,
[DMZ] bit);
INSERT INTO ServerList_SSIS([Server])
EXEC master..xp_cmdshell 'sqlcmd /Lc';
Delete from ServerList_SSIS where [Server] IS NULL;
Select * from ServerList_SSIS
[Connect] bit,
[DMZ] bit);
INSERT INTO ServerList_SSIS([Server])
EXEC master..xp_cmdshell 'sqlcmd /Lc';
Delete from ServerList_SSIS where [Server] IS NULL;
Select * from ServerList_SSIS
donderdag 20 november 2008
spProcessTrace-fnExtractSPNameFromTextData-SpActivityGraph-spShowActivityGraphByChunks
--Listing 4. spProcessTrace
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spProcessTraceFile' and type = 'P')
DROP PROC spProcessTraceFile
GO
-------------------------------------------------------------------------------------------------------
-- Name spProcessTraceFile
-- Description Aggregates data from a SQL Server trace file and sends reports with top consumers and
-- long-runners by email
-- Input @ServerName - server name a trace file was created for
-- @ReportDate (optional) - date of the trace file (default - current)
-- @TraceFilePath - path to the trace file
-- @recipients - list of email recipient divided by [;]
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spProcessTraceFile
@ServerName sysname,
@ReportDate varchar(20) = null,
@TraceFilePath varchar(1000) = '\\Process02\D$\Program Files\Microsoft SQL Server\MSSQL\LOG\',
@recipients varchar(4000) = 'Manager1@company.com;Manager2@company.com'
AS
SET NOCOUNT ON
DECLARE @TabName sysname
DECLARE @Sqlstr varchar(8000)
declare @m varchar(8000)
declare @s varchar(8000)
declare @q varchar(8000)
declare @ReportDateShort varchar(20)
SET @ReportDate = CASE WHEN @ReportDate is null
THEN CONVERT(varchar(20),getdate(),107)
ELSE CONVERT(varchar(20),CONVERT(datetime,@ReportDate),107)
END
SET @ReportDateShort = REPLACE(LEFT(@ReportDate,6),' ','')
SET @TabName = 'Trace_' + @ServerName + @ReportDateShort
SET @Sqlstr = '
SELECT EventClass, TextData, DatabaseId, SPID, Duration, StartTime, Reads, Writes, CPU
INTO '+@TabName+' FROM ::fn_trace_gettable('''+@TraceFilePath+@TabName+'.trc'', default)'
EXEC (@Sqlstr)
IF @@ERROR = 0
PRINT 'Loading the trace file into a table succeeded'
-- CPU consumers
SET @Sqlstr = '
SELECT *
into '+@TabName+'_CPU
FROM (
select dbo.fnExtractSPNameFromTextData(TextData) ''SP'', COUNT(*) ''TimesExecuted'',
SUM(CPU) ''TotalCPU'', MIN(CPU) ''MinCPU'', MAX(CPU) ''MaxCPU''
from '+@TabName+'
WHERE DATALENGTH(TextData) > 0
AND EventClass in (10,12)
GROUP BY dbo.fnExtractSPNameFromTextData(TextData)) t
WHERE SP not in (''--'',''COMMIT'',''select'',''insert'',''delete'',''update'',''trace'',''set'',
''use'',''if'',''@retcode'')
ORDER BY 3 DESC'
EXEC (@Sqlstr)
IF @@ERROR = 0
PRINT 'Aggregation for CPU has been succeeded'
-- Sending top CPU consumers by e-mail
set @m = 'Top Overlord.dev CPU consumers for '+@ReportDate+':'+CHAR(13)+CHAR(10)
set @s = 'Top Overlord.dev CPU consumers for '+@ReportDate
set @q = 'select LEFT(SP,40) ''SP'',
TimesExecuted,
TotalCPU,
TotalCPU/TimesExecuted ''AverageExecTime'',
MinCPU,
MaxCPU
from dbo.'+@TabName+'_CPU
WHERE LEFT(SP,1) like ''[a-z]''
AND TimesExecuted > 1
ORDER BY TotalCPU DESC'
exec master.dbo.xp_sendmail @recipients =@recipients,
@message=@m,
@query =@q,
@subject = @s,
@width = 200,
@dbuse='Traces'
-- Long-runners
SET @Sqlstr = '
SELECT *
into '+@TabName+'_Duration
FROM (
select dbo.fnExtractSPNameFromTextData(TextData) ''SP'', COUNT(*) ''TimesExecuted'',
SUM(Duration) ''TotalDuration'', MIN(Duration) ''MinDuration'', MAX(Duration) ''MaxDuration''
from '+@TabName+'
WHERE DATALENGTH(TextData) > 0
AND EventClass in (10,12)
GROUP BY dbo.fnExtractSPNameFromTextData(TextData)) t
WHERE SP not in (''--'',''COMMIT'',''select'',''insert'',''delete'',''update'',''trace'',''set'',
''use'',''if'',''@retcode'')
ORDER BY 3 DESC'
EXEC (@Sqlstr)
-- Sending top Long-Runners result by e-mail
set @m = 'Top Overlord.dev Long-Runners for '+@ReportDate+':'+CHAR(13)+CHAR(10)
set @s = 'Top Overlord.dev Long-Runners for '+@ReportDate
set @q = 'select LEFT(SP,40) ''SP'',
TimesExecuted,
TotalDuration,
TotalDuration/TimesExecuted ''AverageExecTime'',
MinDuration,
MaxDuration
from dbo.'+@TabName+'_Duration
WHERE LEFT(SP,1) like ''[a-z]''
AND TimesExecuted > 1
ORDER BY TotalDuration DESC'
exec master.dbo.xp_sendmail @recipients =@recipients,
@message=@m,
@query =@q,
@subject = @s,
@width = 200,
@dbuse='Traces'
GO
--Listing 5. fnExtractSPNameFromTextData
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fnExtractSPNameFromTextData' and type = 'FN')
DROP FUNCTION fnExtractSPNameFromTextData
GO
---------------------------------------------------------------------------------
-- Name fnExtractSPNameFromTextData
-- Description Extracts SP name from profiler's textdata
-- Input @TextData
-- Output SP name
-- Created By Viktor Gorodnichenko
---------------------------------------------------------------------------------
CREATE FUNCTION fnExtractSPNameFromTextData
(@TextData nvarchar(4000))
RETURNS varchar(128)
AS
BEGIN
DECLARE @Name varchar(128)
IF CHARINDEX('sp_execute',@TextData) > 0
BEGIN
SET @Name = 'sp_execute'
GOTO exit_fn
END
IF CHARINDEX('exec',@TextData) > 0
SET @TextData = RIGHT(@TextData,LEN(@TextData)-CHARINDEX('exec',@TextData)+1)
SET @TextData = LTRIM(REPLACE(@TextData,'execute ',''))
SET @TextData = LTRIM(REPLACE(@TextData,'exec ',''))
IF CHARINDEX(' ',@TextData) > 0
BEGIN
IF LEFT(@TextData,1) = '@'
BEGIN
SET @TextData = RIGHT(@TextData,LEN(@TextData)-CHARINDEX('=',@TextData)-1)
END
SET @Name = CASE WHEN CHARINDEX(' ',@TextData) > 0
THEN LEFT(@TextData,CHARINDEX(' ',@TextData)-1)
ELSE @TextData
END
END
ELSE
SET @Name = @TextData
SET @Name = CASE WHEN CHARINDEX(CHAR(9),@Name) > 0
THEN LEFT(@Name,CHARINDEX(CHAR(9),@Name)-1)
ELSE @Name
END
IF CHARINDEX('.',@Name)>0
SET @Name = REVERSE(LEFT(REVERSE(@Name),CHARINDEX('.',REVERSE(@Name))-1))
exit_fn:
RETURN @Name
END
--Listing 6. SpActivityGraph
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spActivityGraph' and type = 'P')
DROP PROC spActivityGraph
GO
-------------------------------------------------------------------------------------------------------
-- Name spActivityGraph
-- Description Builds a graph of code activities. Uses data from a trace table
-- Input @TraceTable - name of the trace table
-- @TraceStart/@TraceEnd - trace period to cover in the graph
-- @SPNameLayoutLen = 20 - size of the column "name" in the report
-- @DurationLayoutLen = 6 - size of the column "duration" in the report
-- @LayoutWidth int = 115 - width of the report
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spActivityGraph
@TraceTable varchar(128),
@TraceStart varchar(40),
@TraceEnd varchar(40),
@SPNameLayoutLen int = 20,
@DurationLayoutLen int = 6,
@LayoutWidth int = 115
AS
DECLARE @GraphStart datetime
DECLARE @GraphEnd datetime
DECLARE @TimeToMark datetime
DECLARE @Coeff numeric(7,3)
DECLARE @strCoeff varchar(20)
DECLARE @StartTime datetime
DECLARE @Duration int
DECLARE @TextData varchar(8000)
DECLARE @sqlstr nvarchar(4000)
SET @sqlstr = N'SELECT @GraphStart = MIN(StartTime), @GraphEnd = MAX(StartTime) from '+@TraceTable+'
WHERE StartTime > '''+@TraceStart+''' and StartTime < '''+@TraceEnd+'''
and TextData not like ''%sp_getactiveprocesses%''
and EventClass in (10,12)
and Duration > 5000'
EXEC sp_executesql @sqlstr, N'@GraphStart datetime OUTPUT, @GraphEnd datetime OUTPUT',
@GraphStart = @GraphStart OUTPUT, @GraphEnd = @GraphEnd OUTPUT
IF @GraphStart is null
BEGIN
PRINT 'No data for the period'
RETURN
END
SET @Coeff = CONVERT(numeric(10,2),@LayoutWidth) / CASE WHEN DATEDIFF(ss, @GraphStart, @GraphEnd)>0
THEN CONVERT(numeric(10,2),DATEDIFF(ss, @GraphStart, @GraphEnd)) ELSE 1800 END -- 1800 sec in 30 min
-- The smaller @Coeff the less number of dashes will be used to represent a long-runner.
Fo ex, 0.2 means that a 5 sec process will get 1 dash.
SET @strCoeff = STR(@Coeff,7,3)
EXEC ('DECLARE SPIDs CURSOR FOR
select StartTime, Duration, CONVERT(varchar(8000),TextData) from '+@TraceTable+'
WHERE StartTime > '''+@TraceStart+''' and StartTime < '''+@TraceEnd+'''
and TextData not like ''%sp_getactiveprocesses%''
and EventClass in (10,12)
and (Duration/1000)*'+@strCoeff+' > 1
order by StartTime')
-- Printing time scale
PRINT 'StartTime Duration Text'+
SPACE(12)+LEFT(CONVERT(varchar(10),@GraphStart,108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),DATEADD(ss,DATEDIFF(ss,@GraphStart,@GraphEnd)/3,@GraphStart),108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),DATEADD(ss,DATEDIFF(ss,@GraphStart,@GraphEnd)*2/3,@GraphStart),108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),@GraphEnd,108),5)
IF @TimeToMark is not null
PRINT REPLICATE(' ',ROUND(DATEDIFF(ss,@GraphStart,@TimeToMark)*@Coeff,0))+'*'
OPEN SPIDs
FETCH NEXT FROM SPIDs INTO @StartTime, @Duration, @TextData
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT RIGHT(CONVERT(varchar(10),@StartTime,108),8) + ' ' + STR(@Duration,@DurationLayoutLen) + ' ' +
LEFT(dbo.fnExtractSPNameFromTextData(@TextData),@SPNameLayoutLen)+
REPLICATE(' ',@SPNameLayoutLen-LEN(LEFT(dbo.fnExtractSPNameFromTextData(@TextData),@SPNameLayoutLen)))+
REPLICATE(' ',ROUND(DATEDIFF(ss,@GraphStart,@StartTime)*@Coeff,0))+
REPLICATE('-',ROUND((@Duration/1000)*@Coeff,0))
FETCH NEXT FROM SPIDs INTO @StartTime, @Duration, @TextData
END
DEALLOCATE SPIDs
GO
--Listing 7. spShowActivityGraphByChunks
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spShowActivityGraphByChunks' and type = 'P')
DROP PROC spShowActivityGraphByChunks
GO
-------------------------------------------------------------------------------------------------------
-- Name spShowActivityGraphByChunks
-- Description Shows activity graphs by small portions
-- Input @ServerName - name of the production server the trace was collected for
-- @ReportDate - date of the trace
-- @StartTime/@EndTime - trace period to cover
-- @ChunkSize - size in minutes of a single graph
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spShowActivityGraphByChunks
@ServerName sysname,
@ReportDate datetime,
@StartTime varchar(7) = '8:30AM',
@EndTime varchar(7) = '5:30PM',
@ChunkSize int = 30
AS
DECLARE @TraceStart datetime
DECLARE @TraceEnd datetime
DECLARE @ReportDateShort varchar(20)
DECLARE @SqlStr varchar(2000)
SET @ReportDateShort = REPLACE(LEFT(CONVERT(varchar(20),@ReportDate,107),6),' ','')
SET @TraceStart = CONVERT(varchar(20),@ReportDate,107)+' '+@StartTime
WHILE @TraceStart <= CONVERT(varchar(20),@ReportDate,107)+' '+@EndTime
BEGIN
SET @TraceEnd = DATEADD(mi,@ChunkSize,@TraceStart)
SET @SqlStr = 'spActivityGraph Trace_'+@ServerName+@ReportDateShort+ ', '''+
CONVERT(varchar(40),@TraceStart)+''', '''+CONVERT(varchar(30),@TraceEnd)+''''
EXEC (@SqlStr)
SET @TraceStart = DATEADD(mi,60,@TraceStart)
END
--------------------
http://www.sqlservercentral.com/columnists/vgorod/monitoringperformance_scripts.txt
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spProcessTraceFile' and type = 'P')
DROP PROC spProcessTraceFile
GO
-------------------------------------------------------------------------------------------------------
-- Name spProcessTraceFile
-- Description Aggregates data from a SQL Server trace file and sends reports with top consumers and
-- long-runners by email
-- Input @ServerName - server name a trace file was created for
-- @ReportDate (optional) - date of the trace file (default - current)
-- @TraceFilePath - path to the trace file
-- @recipients - list of email recipient divided by [;]
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spProcessTraceFile
@ServerName sysname,
@ReportDate varchar(20) = null,
@TraceFilePath varchar(1000) = '\\Process02\D$\Program Files\Microsoft SQL Server\MSSQL\LOG\',
@recipients varchar(4000) = 'Manager1@company.com;Manager2@company.com'
AS
SET NOCOUNT ON
DECLARE @TabName sysname
DECLARE @Sqlstr varchar(8000)
declare @m varchar(8000)
declare @s varchar(8000)
declare @q varchar(8000)
declare @ReportDateShort varchar(20)
SET @ReportDate = CASE WHEN @ReportDate is null
THEN CONVERT(varchar(20),getdate(),107)
ELSE CONVERT(varchar(20),CONVERT(datetime,@ReportDate),107)
END
SET @ReportDateShort = REPLACE(LEFT(@ReportDate,6),' ','')
SET @TabName = 'Trace_' + @ServerName + @ReportDateShort
SET @Sqlstr = '
SELECT EventClass, TextData, DatabaseId, SPID, Duration, StartTime, Reads, Writes, CPU
INTO '+@TabName+' FROM ::fn_trace_gettable('''+@TraceFilePath+@TabName+'.trc'', default)'
EXEC (@Sqlstr)
IF @@ERROR = 0
PRINT 'Loading the trace file into a table succeeded'
-- CPU consumers
SET @Sqlstr = '
SELECT *
into '+@TabName+'_CPU
FROM (
select dbo.fnExtractSPNameFromTextData(TextData) ''SP'', COUNT(*) ''TimesExecuted'',
SUM(CPU) ''TotalCPU'', MIN(CPU) ''MinCPU'', MAX(CPU) ''MaxCPU''
from '+@TabName+'
WHERE DATALENGTH(TextData) > 0
AND EventClass in (10,12)
GROUP BY dbo.fnExtractSPNameFromTextData(TextData)) t
WHERE SP not in (''--'',''COMMIT'',''select'',''insert'',''delete'',''update'',''trace'',''set'',
''use'',''if'',''@retcode'')
ORDER BY 3 DESC'
EXEC (@Sqlstr)
IF @@ERROR = 0
PRINT 'Aggregation for CPU has been succeeded'
-- Sending top CPU consumers by e-mail
set @m = 'Top Overlord.dev CPU consumers for '+@ReportDate+':'+CHAR(13)+CHAR(10)
set @s = 'Top Overlord.dev CPU consumers for '+@ReportDate
set @q = 'select LEFT(SP,40) ''SP'',
TimesExecuted,
TotalCPU,
TotalCPU/TimesExecuted ''AverageExecTime'',
MinCPU,
MaxCPU
from dbo.'+@TabName+'_CPU
WHERE LEFT(SP,1) like ''[a-z]''
AND TimesExecuted > 1
ORDER BY TotalCPU DESC'
exec master.dbo.xp_sendmail @recipients =@recipients,
@message=@m,
@query =@q,
@subject = @s,
@width = 200,
@dbuse='Traces'
-- Long-runners
SET @Sqlstr = '
SELECT *
into '+@TabName+'_Duration
FROM (
select dbo.fnExtractSPNameFromTextData(TextData) ''SP'', COUNT(*) ''TimesExecuted'',
SUM(Duration) ''TotalDuration'', MIN(Duration) ''MinDuration'', MAX(Duration) ''MaxDuration''
from '+@TabName+'
WHERE DATALENGTH(TextData) > 0
AND EventClass in (10,12)
GROUP BY dbo.fnExtractSPNameFromTextData(TextData)) t
WHERE SP not in (''--'',''COMMIT'',''select'',''insert'',''delete'',''update'',''trace'',''set'',
''use'',''if'',''@retcode'')
ORDER BY 3 DESC'
EXEC (@Sqlstr)
-- Sending top Long-Runners result by e-mail
set @m = 'Top Overlord.dev Long-Runners for '+@ReportDate+':'+CHAR(13)+CHAR(10)
set @s = 'Top Overlord.dev Long-Runners for '+@ReportDate
set @q = 'select LEFT(SP,40) ''SP'',
TimesExecuted,
TotalDuration,
TotalDuration/TimesExecuted ''AverageExecTime'',
MinDuration,
MaxDuration
from dbo.'+@TabName+'_Duration
WHERE LEFT(SP,1) like ''[a-z]''
AND TimesExecuted > 1
ORDER BY TotalDuration DESC'
exec master.dbo.xp_sendmail @recipients =@recipients,
@message=@m,
@query =@q,
@subject = @s,
@width = 200,
@dbuse='Traces'
GO
--Listing 5. fnExtractSPNameFromTextData
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fnExtractSPNameFromTextData' and type = 'FN')
DROP FUNCTION fnExtractSPNameFromTextData
GO
---------------------------------------------------------------------------------
-- Name fnExtractSPNameFromTextData
-- Description Extracts SP name from profiler's textdata
-- Input @TextData
-- Output SP name
-- Created By Viktor Gorodnichenko
---------------------------------------------------------------------------------
CREATE FUNCTION fnExtractSPNameFromTextData
(@TextData nvarchar(4000))
RETURNS varchar(128)
AS
BEGIN
DECLARE @Name varchar(128)
IF CHARINDEX('sp_execute',@TextData) > 0
BEGIN
SET @Name = 'sp_execute'
GOTO exit_fn
END
IF CHARINDEX('exec',@TextData) > 0
SET @TextData = RIGHT(@TextData,LEN(@TextData)-CHARINDEX('exec',@TextData)+1)
SET @TextData = LTRIM(REPLACE(@TextData,'execute ',''))
SET @TextData = LTRIM(REPLACE(@TextData,'exec ',''))
IF CHARINDEX(' ',@TextData) > 0
BEGIN
IF LEFT(@TextData,1) = '@'
BEGIN
SET @TextData = RIGHT(@TextData,LEN(@TextData)-CHARINDEX('=',@TextData)-1)
END
SET @Name = CASE WHEN CHARINDEX(' ',@TextData) > 0
THEN LEFT(@TextData,CHARINDEX(' ',@TextData)-1)
ELSE @TextData
END
END
ELSE
SET @Name = @TextData
SET @Name = CASE WHEN CHARINDEX(CHAR(9),@Name) > 0
THEN LEFT(@Name,CHARINDEX(CHAR(9),@Name)-1)
ELSE @Name
END
IF CHARINDEX('.',@Name)>0
SET @Name = REVERSE(LEFT(REVERSE(@Name),CHARINDEX('.',REVERSE(@Name))-1))
exit_fn:
RETURN @Name
END
--Listing 6. SpActivityGraph
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spActivityGraph' and type = 'P')
DROP PROC spActivityGraph
GO
-------------------------------------------------------------------------------------------------------
-- Name spActivityGraph
-- Description Builds a graph of code activities. Uses data from a trace table
-- Input @TraceTable - name of the trace table
-- @TraceStart/@TraceEnd - trace period to cover in the graph
-- @SPNameLayoutLen = 20 - size of the column "name" in the report
-- @DurationLayoutLen = 6 - size of the column "duration" in the report
-- @LayoutWidth int = 115 - width of the report
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spActivityGraph
@TraceTable varchar(128),
@TraceStart varchar(40),
@TraceEnd varchar(40),
@SPNameLayoutLen int = 20,
@DurationLayoutLen int = 6,
@LayoutWidth int = 115
AS
DECLARE @GraphStart datetime
DECLARE @GraphEnd datetime
DECLARE @TimeToMark datetime
DECLARE @Coeff numeric(7,3)
DECLARE @strCoeff varchar(20)
DECLARE @StartTime datetime
DECLARE @Duration int
DECLARE @TextData varchar(8000)
DECLARE @sqlstr nvarchar(4000)
SET @sqlstr = N'SELECT @GraphStart = MIN(StartTime), @GraphEnd = MAX(StartTime) from '+@TraceTable+'
WHERE StartTime > '''+@TraceStart+''' and StartTime < '''+@TraceEnd+'''
and TextData not like ''%sp_getactiveprocesses%''
and EventClass in (10,12)
and Duration > 5000'
EXEC sp_executesql @sqlstr, N'@GraphStart datetime OUTPUT, @GraphEnd datetime OUTPUT',
@GraphStart = @GraphStart OUTPUT, @GraphEnd = @GraphEnd OUTPUT
IF @GraphStart is null
BEGIN
PRINT 'No data for the period'
RETURN
END
SET @Coeff = CONVERT(numeric(10,2),@LayoutWidth) / CASE WHEN DATEDIFF(ss, @GraphStart, @GraphEnd)>0
THEN CONVERT(numeric(10,2),DATEDIFF(ss, @GraphStart, @GraphEnd)) ELSE 1800 END -- 1800 sec in 30 min
-- The smaller @Coeff the less number of dashes will be used to represent a long-runner.
Fo ex, 0.2 means that a 5 sec process will get 1 dash.
SET @strCoeff = STR(@Coeff,7,3)
EXEC ('DECLARE SPIDs CURSOR FOR
select StartTime, Duration, CONVERT(varchar(8000),TextData) from '+@TraceTable+'
WHERE StartTime > '''+@TraceStart+''' and StartTime < '''+@TraceEnd+'''
and TextData not like ''%sp_getactiveprocesses%''
and EventClass in (10,12)
and (Duration/1000)*'+@strCoeff+' > 1
order by StartTime')
-- Printing time scale
PRINT 'StartTime Duration Text'+
SPACE(12)+LEFT(CONVERT(varchar(10),@GraphStart,108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),DATEADD(ss,DATEDIFF(ss,@GraphStart,@GraphEnd)/3,@GraphStart),108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),DATEADD(ss,DATEDIFF(ss,@GraphStart,@GraphEnd)*2/3,@GraphStart),108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),@GraphEnd,108),5)
IF @TimeToMark is not null
PRINT REPLICATE(' ',ROUND(DATEDIFF(ss,@GraphStart,@TimeToMark)*@Coeff,0))+'*'
OPEN SPIDs
FETCH NEXT FROM SPIDs INTO @StartTime, @Duration, @TextData
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT RIGHT(CONVERT(varchar(10),@StartTime,108),8) + ' ' + STR(@Duration,@DurationLayoutLen) + ' ' +
LEFT(dbo.fnExtractSPNameFromTextData(@TextData),@SPNameLayoutLen)+
REPLICATE(' ',@SPNameLayoutLen-LEN(LEFT(dbo.fnExtractSPNameFromTextData(@TextData),@SPNameLayoutLen)))+
REPLICATE(' ',ROUND(DATEDIFF(ss,@GraphStart,@StartTime)*@Coeff,0))+
REPLICATE('-',ROUND((@Duration/1000)*@Coeff,0))
FETCH NEXT FROM SPIDs INTO @StartTime, @Duration, @TextData
END
DEALLOCATE SPIDs
GO
--Listing 7. spShowActivityGraphByChunks
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spShowActivityGraphByChunks' and type = 'P')
DROP PROC spShowActivityGraphByChunks
GO
-------------------------------------------------------------------------------------------------------
-- Name spShowActivityGraphByChunks
-- Description Shows activity graphs by small portions
-- Input @ServerName - name of the production server the trace was collected for
-- @ReportDate - date of the trace
-- @StartTime/@EndTime - trace period to cover
-- @ChunkSize - size in minutes of a single graph
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spShowActivityGraphByChunks
@ServerName sysname,
@ReportDate datetime,
@StartTime varchar(7) = '8:30AM',
@EndTime varchar(7) = '5:30PM',
@ChunkSize int = 30
AS
DECLARE @TraceStart datetime
DECLARE @TraceEnd datetime
DECLARE @ReportDateShort varchar(20)
DECLARE @SqlStr varchar(2000)
SET @ReportDateShort = REPLACE(LEFT(CONVERT(varchar(20),@ReportDate,107),6),' ','')
SET @TraceStart = CONVERT(varchar(20),@ReportDate,107)+' '+@StartTime
WHILE @TraceStart <= CONVERT(varchar(20),@ReportDate,107)+' '+@EndTime
BEGIN
SET @TraceEnd = DATEADD(mi,@ChunkSize,@TraceStart)
SET @SqlStr = 'spActivityGraph Trace_'+@ServerName+@ReportDateShort+ ', '''+
CONVERT(varchar(40),@TraceStart)+''', '''+CONVERT(varchar(30),@TraceEnd)+''''
EXEC (@SqlStr)
SET @TraceStart = DATEADD(mi,60,@TraceStart)
END
--------------------
http://www.sqlservercentral.com/columnists/vgorod/monitoringperformance_scripts.txt
spTraceBuild
--Listing 3. spTraceBuild
USE msdb
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spTraceBuild' and type = 'P')
DROP PROC spTraceBuild
GO
-------------------------------------------------------------------------------------------------------
-- Name spTraceBuild
-- Description Starts a SQL Server trace. The code based on sp build_trace from the Microsoft
-- Knowledge Base Article Q283790.
-- Input From 'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
-- Modified By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spTraceBuild
@traceini nvarchar (245) = N'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
as
SET NOCOUNT ON
declare @traceid int, @options int, @tracefile nvarchar (245), @maxfilesize bigint
, @stoptime datetime, @minMBfree bigint, @rc int, @on bit, @cmd1 nvarchar(512)
, @events varchar(512), @columns varchar(512), @event int, @column int, @estart int, @enext int
, @cstart int, @cnext int, @le int, @lc int, @filter nvarchar(245), @filter_num int
create table #t1 ([c1] nvarchar(512))
set @cmd1 = 'bulk insert #t1 FROM '''
--select @cmd1 + @traceini
exec (@cmd1 + @traceini + '''')
select @tracefile = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1 where left(c1,3) = '@tr'
select @maxfilesize = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as bigint)
from #t1 where left(c1,3) = '@ma'
select @stoptime = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as datetime)
from #t1 where left(c1,3) = '@st'
SET @stoptime = CONVERT(datetime, CONVERT(varchar(20),getdate(),107) + ' ' + @stoptime)
select @options = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as int)
from #t1 where left(c1,3) = '@op'
select @events=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@ev'
select @columns=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@co'
set @on = 1
set @traceid = 0
select @tracefile = @tracefile + '_' + @@SERVERNAME + REPLACE(LEFT(CONVERT(varchar(20),getdate(),107),6),' ','')
--select @tracefile
SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefile
if @traceid != 0 goto finish
set @cmd1 = 'if exist "' + @tracefile + '.trc" ' + 'del "' + @tracefile + '*.trc"'
exec @rc = master.dbo.xp_cmdshell @cmd1, no_output
exec @rc = sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptime
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_create failed. Unknown error.'
WHEN @rc = 10 THEN 'sp_trace_create failed. Invalid options. Options specified are incompatible.'
WHEN @rc = 12 THEN 'sp_trace_create failed. File not created.'
WHEN @rc = 13 THEN 'sp_trace_create failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_create failed. Invalid stop time. The stop time specified has already happened.'
WHEN @rc = 15 THEN 'sp_trace_create failed. Invalid parameters. Supplied incompatible parameters.'
END
RETURN
END
select @estart = 1
select @enext = charindex(',',@events,@estart)
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
set @le = len(@events)
set @lc = len(@columns)
while @enext > 0
begin
select @event = cast(substring(@events,@estart,@enext-@estart) as int)
while @cnext > 0
begin
select @column = cast(substring(@columns,@cstart,@cnext-@cstart) as int)
exec @rc = sp_trace_setevent @traceid, @event, @column, @on
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_setevent failed. Unknown error.'
WHEN @rc = 2 THEN 'sp_trace_setevent failed. The trace is currently running.'
WHEN @rc = 3 THEN 'sp_trace_setevent failed. The specified Event is not valid. The Event may not exist or it
is not an appropriate one for the store procedure.'
WHEN @rc = 4 THEN 'sp_trace_setevent failed. The specified Column is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setevent failed. The specified Trace Handle is not valid.'
WHEN @rc = 11 THEN 'sp_trace_setevent failed. The specified Column is used internally and cannot be removed. '
WHEN @rc = 13 THEN 'sp_trace_setevent failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_setevent failed. The function is not valid for this trace.'
END
RETURN
END
select @cstart = @cnext + 1
select @cnext = charindex(',',@columns,@cstart)
if @cnext = 0 set @cnext = @lc + 1
if @cstart >@lc set @cnext = 0
end
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
select @estart = @enext + 1
select @enext = charindex(',',@events,@estart)
if @enext = 0 set @enext = @le + 1
if @estart > @le set @enext = 0
end
set @cmd1 = 'exec sp_trace_setfilter '
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter1'
set @filter_num = 1
while @filter != N'none'
begin
exec (@cmd1 + @traceid + ','+@filter)
set @filter_num = @filter_num + 1
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter'
+ cast(@filter_num as nvarchar(3))
--select @filter
end
finish:
drop table #t1
exec @rc = sp_trace_setstatus @traceid, 1
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_setstatus. Unknown error.'
WHEN @rc = 8 THEN 'sp_trace_setstatus. The specified Status is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setstatus. The specified Trace Handle is not valid.'
WHEN @rc = 13 THEN 'sp_trace_setstatus. Out of memory. Returned when there is not enough memory to perform the specified action.'
END
RETURN
END
SELECT 'Trace '+LTRIM(STR(@traceid)) + ' has been lanched. The trace details:'
SELECT * FROM ::fn_trace_getinfo (@traceid)
GO
exec spTraceBuild
USE msdb
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spTraceBuild' and type = 'P')
DROP PROC spTraceBuild
GO
-------------------------------------------------------------------------------------------------------
-- Name spTraceBuild
-- Description Starts a SQL Server trace. The code based on sp build_trace from the Microsoft
-- Knowledge Base Article Q283790.
-- Input From 'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
-- Modified By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spTraceBuild
@traceini nvarchar (245) = N'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
as
SET NOCOUNT ON
declare @traceid int, @options int, @tracefile nvarchar (245), @maxfilesize bigint
, @stoptime datetime, @minMBfree bigint, @rc int, @on bit, @cmd1 nvarchar(512)
, @events varchar(512), @columns varchar(512), @event int, @column int, @estart int, @enext int
, @cstart int, @cnext int, @le int, @lc int, @filter nvarchar(245), @filter_num int
create table #t1 ([c1] nvarchar(512))
set @cmd1 = 'bulk insert #t1 FROM '''
--select @cmd1 + @traceini
exec (@cmd1 + @traceini + '''')
select @tracefile = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1 where left(c1,3) = '@tr'
select @maxfilesize = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as bigint)
from #t1 where left(c1,3) = '@ma'
select @stoptime = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as datetime)
from #t1 where left(c1,3) = '@st'
SET @stoptime = CONVERT(datetime, CONVERT(varchar(20),getdate(),107) + ' ' + @stoptime)
select @options = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as int)
from #t1 where left(c1,3) = '@op'
select @events=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@ev'
select @columns=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@co'
set @on = 1
set @traceid = 0
select @tracefile = @tracefile + '_' + @@SERVERNAME + REPLACE(LEFT(CONVERT(varchar(20),getdate(),107),6),' ','')
--select @tracefile
SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefile
if @traceid != 0 goto finish
set @cmd1 = 'if exist "' + @tracefile + '.trc" ' + 'del "' + @tracefile + '*.trc"'
exec @rc = master.dbo.xp_cmdshell @cmd1, no_output
exec @rc = sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptime
IF @rc <> 0
BEGIN
CASE
WHEN @rc = 1 THEN 'sp_trace_create failed. Unknown error.'
WHEN @rc = 10 THEN 'sp_trace_create failed. Invalid options. Options specified are incompatible.'
WHEN @rc = 12 THEN 'sp_trace_create failed. File not created.'
WHEN @rc = 13 THEN 'sp_trace_create failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_create failed. Invalid stop time. The stop time specified has already happened.'
WHEN @rc = 15 THEN 'sp_trace_create failed. Invalid parameters. Supplied incompatible parameters.'
END
RETURN
END
select @estart = 1
select @enext = charindex(',',@events,@estart)
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
set @le = len(@events)
set @lc = len(@columns)
while @enext > 0
begin
select @event = cast(substring(@events,@estart,@enext-@estart) as int)
while @cnext > 0
begin
select @column = cast(substring(@columns,@cstart,@cnext-@cstart) as int)
exec @rc = sp_trace_setevent @traceid, @event, @column, @on
IF @rc <> 0
BEGIN
CASE
WHEN @rc = 1 THEN 'sp_trace_setevent failed. Unknown error.'
WHEN @rc = 2 THEN 'sp_trace_setevent failed. The trace is currently running.'
WHEN @rc = 3 THEN 'sp_trace_setevent failed. The specified Event is not valid. The Event may not exist or it
is not an appropriate one for the store procedure.'
WHEN @rc = 4 THEN 'sp_trace_setevent failed. The specified Column is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setevent failed. The specified Trace Handle is not valid.'
WHEN @rc = 11 THEN 'sp_trace_setevent failed. The specified Column is used internally and cannot be removed. '
WHEN @rc = 13 THEN 'sp_trace_setevent failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_setevent failed. The function is not valid for this trace.'
END
RETURN
END
select @cstart = @cnext + 1
select @cnext = charindex(',',@columns,@cstart)
if @cnext = 0 set @cnext = @lc + 1
if @cstart >@lc set @cnext = 0
end
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
select @estart = @enext + 1
select @enext = charindex(',',@events,@estart)
if @enext = 0 set @enext = @le + 1
if @estart > @le set @enext = 0
end
set @cmd1 = 'exec sp_trace_setfilter '
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter1'
set @filter_num = 1
while @filter != N'none'
begin
exec (@cmd1 + @traceid + ','+@filter)
set @filter_num = @filter_num + 1
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter'
+ cast(@filter_num as nvarchar(3))
--select @filter
end
finish:
drop table #t1
exec @rc = sp_trace_setstatus @traceid, 1
IF @rc <> 0
BEGIN
CASE
WHEN @rc = 1 THEN 'sp_trace_setstatus. Unknown error.'
WHEN @rc = 8 THEN 'sp_trace_setstatus. The specified Status is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setstatus. The specified Trace Handle is not valid.'
WHEN @rc = 13 THEN 'sp_trace_setstatus. Out of memory. Returned when there is not enough memory to perform the specified action.'
END
RETURN
END
SELECT 'Trace '+LTRIM(STR(@traceid)) + ' has been lanched. The trace details:'
SELECT * FROM ::fn_trace_getinfo (@traceid)
GO
exec spTraceBuild
Abonneren op:
Posts (Atom)