RAISERROR ('NOTE:  It''s completely normal for some errors to be generated by this script', 0, 1) WITH NOWAIT
GO

/*
Procs:
	sp_set_blk_thresholdNN
	sp_tmpregread
	sp_tmpregenumvalues
	sp_sqldiagNN
	sp_traceNN
	sp_sqldiag_cleanupNN
	sp_code_runnerNN
	sp_blocker_pssNN

*/

USE tempdb
GO
set nocount on
go


/*

sp_set_blk_threshold

*/

IF OBJECT_ID('dbo.sp_set_blk_threshold09','P') IS NOT NULL drop proc dbo.sp_set_blk_threshold09
GO
PRINT ''
RAISERROR ('===== Creating sp_set_blk_threshold09', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_set_blk_threshold09 @threshold int
as
if (select value from sys.sysconfigures where config = 518) = 1 
begin
    exec sp_configure 'Blocked process',@threshold	
    reconfigure with override
end
else 
begin
    exec sp_configure 'show advanced option',1 
    reconfigure with override
    exec sp_configure 'Blocked process',@threshold	
    exec sp_configure 'show advanced option',0 
    reconfigure with override
end

go

-- Set up a default blocked process threshold in case blocked process event is
-- included in regular trace
if (CHARINDEX('9.00.',@@VERSION)<>0)
BEGIN
	PRINT ''
	RAISERROR ('===== Executing sp_set_blk_threshold09', 0, 1) WITH NOWAIT
	exec dbo.sp_set_blk_threshold09 5
END
GO
IF OBJECT_ID('dbo.sp_set_blk_threshold10','P') IS NOT NULL drop proc dbo.sp_set_blk_threshold10
GO
PRINT ''
RAISERROR ('===== Creating sp_set_blk_threshold10', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_set_blk_threshold10 @threshold int
as
BEGIN
  exec dbo.sp_set_blk_threshold09 @threshold
END
GO
-- SQL11 Version Bump
IF OBJECT_ID('dbo.sp_set_blk_threshold11','P') IS NOT NULL drop proc dbo.sp_set_blk_threshold11
GO
PRINT ''
RAISERROR ('===== Creating sp_set_blk_threshold11', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_set_blk_threshold11 @threshold int
as
BEGIN
  exec dbo.sp_set_blk_threshold10 @threshold
END
GO

-- SQL14 Version Bump
IF OBJECT_ID('dbo.sp_set_blk_threshold12','P') IS NOT NULL drop proc dbo.sp_set_blk_threshold12
GO
PRINT ''
RAISERROR ('===== Creating sp_set_blk_threshold12', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_set_blk_threshold12 @threshold int
as
BEGIN
  exec dbo.sp_set_blk_threshold10 @threshold
END
GO

-- SQL15 Version Bump
IF OBJECT_ID('dbo.sp_set_blk_threshold13','P') IS NOT NULL drop proc dbo.sp_set_blk_threshold13
GO
PRINT ''
RAISERROR ('===== Creating sp_set_blk_threshold13', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_set_blk_threshold13 @threshold int
as
BEGIN
  exec dbo.sp_set_blk_threshold10 @threshold
END
GO

/*

sp_sqldiag

*/

IF OBJECT_ID ('dbo.sp_tmpregread','P') IS NOT NULL DROP PROC dbo.sp_tmpregread 
IF OBJECT_ID ('dbo.sp_tmpregenumvalues','P') IS NOT NULL DROP PROC dbo.sp_tmpregenumvalues 
GO
PRINT ''
RAISERROR ('===== Creating sp_tmpregread', 0, 1) WITH NOWAIT
GO
-- Create temporary stored procedures in tempdb 
CREATE PROCEDURE dbo.sp_tmpregread 
  @hive varchar (60), @key nvarchar (2000), @value nvarchar (2000), @data nvarchar (4000) = NULL OUTPUT 
AS
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
DECLARE @sql70or80xp sysname

DECLARE @sqlcmd nvarchar (4000)
-- To avoid osql line wrapping, don't store more than 2000 chars.
CREATE TABLE #regdata (RegValue nvarchar(190), RegData nvarchar (1800))
IF CHARINDEX ('7.00.', @@VERSION) = 0
  SET @sql70or80xp = 'master.dbo.xp_instance_regread'
ELSE
  SET @sql70or80xp = 'master.dbo.xp_regread'
SET @sqlcmd = 'INSERT INTO #regdata EXEC ' + @sql70or80xp + ' @P1, @P2, @P3' 
EXEC sp_executesql @sqlcmd, 
  N'@P1 varchar (40), @P2 nvarchar (2000), @P3 nvarchar (2000)', 
  @hive, @key, @value 
SELECT * FROM #regdata
PRINT ''
GO
PRINT ''
RAISERROR ('===== Creating sp_tmpregenumvalues', 0, 1) WITH NOWAIT
GO
CREATE PROCEDURE dbo.sp_tmpregenumvalues 
  @hive varchar (40), @key nvarchar (2000), @direct_output int = 0
AS
DECLARE @sql70or80xp sysname
DECLARE @sqlcmd nvarchar (4000)
CREATE TABLE #regdata (RegValue nvarchar(190), RegData nvarchar (1800))
IF CHARINDEX ('7.00.', @@VERSION) = 0
  SET @sql70or80xp = 'master.dbo.xp_instance_regenumvalues'
ELSE
  SET @sql70or80xp = 'master.dbo.xp_regenumvalues'
IF @direct_output = 1 SET @sqlcmd = 'EXEC '
ELSE SET @sqlcmd = 'INSERT INTO #regdata EXEC '
SET @sqlcmd = @sqlcmd + @sql70or80xp + ' @P1, @P2' 
EXEC sp_executesql @sqlcmd, 
  N'@P1 varchar (40), @P2 nvarchar (2000)', 
  @hive, @key
IF @direct_output = 0 SELECT * FROM #regdata
GO


IF OBJECT_ID('dbo.sp_sqldiag07','P') IS NOT NULL drop proc dbo.sp_sqldiag07
GO
PRINT ''
RAISERROR ('===== Creating sp_sqldiag07', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_sqldiag07 @bGetSyscacheobjects int = 0 
AS

PRINT 'Errorlogs'
PRINT '---------'

declare @i tinyint, @res int
set @i=0
while (@i<255) begin
	if (0=@i) begin
		print 'ERRORLOG'
		exec @res=master.dbo.xp_readerrorlog 
	end	else begin
		print 'ERRORLOG.'+cast(@i as varchar(3))
		exec @res=master.dbo.xp_readerrorlog @i
	end
	if (@@error<>0) OR (@res<>0) break
	set @i=@i+1
end

PRINT 'Registry Information'
PRINT '--------------------'

PRINT 'SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo:'
PRINT '------------------------------------------------'
EXEC master.dbo.sp_tmpregenumvalues 'HKEY_LOCAL_MACHINE', 
  'Software\Microsoft\MSSQLServer\Client\ConnectTo'
PRINT ''

PRINT 'SOFTWARE\Microsoft\MSSQLServer\Client\DB-Lib:'
PRINT '---------------------------------------------'
EXEC master.dbo.sp_tmpregenumvalues 'HKEY_LOCAL_MACHINE', 
  'SOFTWARE\Microsoft\MSSQLServer\Client\DB-Lib' 
PRINT ''

PRINT 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion:'
PRINT '----------------------------------------------------------'
EXEC master.dbo.sp_tmpregread 'HKEY_LOCAL_MACHINE', 
  'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion', 'CurrentVersion'
PRINT ''

PRINT 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters:'
PRINT '------------------------------------------------------'
EXEC master.dbo.sp_tmpregenumvalues  'HKEY_LOCAL_MACHINE', 
  'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
PRINT ''

PRINT 'SOFTWARE\Microsoft\MSSQLServer\Setup\SQLPath:'
PRINT '---------------------------------------------'
EXEC master.dbo.sp_tmpregread 'HKEY_LOCAL_MACHINE', 
  'SOFTWARE\Microsoft\MSSQLServer\Setup', 'SQLPath'
PRINT ''

PRINT 'System\CurrentControlSet\Control\ProductOptions:'
PRINT '------------------------------------------------'
EXEC master.dbo.sp_tmpregenumvalues  'HKEY_LOCAL_MACHINE', 
  'System\CurrentControlSet\Control\ProductOptions', 1
PRINT ''

PRINT 'Software\Microsoft\Windows NT\CurrentVersion\SystemRoot:'
PRINT '--------------------------------------------------------'
EXEC master.dbo.sp_tmpregread 'HKEY_LOCAL_MACHINE', 
  'Software\Microsoft\Windows NT\CurrentVersion', 'SystemRoot'
PRINT ''

PRINT 'Software\Microsoft\Windows NT\CurrentVersion\CurrentVersion:'
PRINT '------------------------------------------------------------'
EXEC master.dbo.sp_tmpregread 'HKEY_LOCAL_MACHINE', 
  'Software\Microsoft\Windows NT\CurrentVersion', 'CurrentVersion'
PRINT ''

PRINT 'System\CurrentControlSet\Control\Nls\CodePage:'
PRINT '----------------------------------------------'
EXEC master.dbo.sp_tmpregread 'HKEY_LOCAL_MACHINE', 
  'System\CurrentControlSet\Control\Nls\CodePage', 'ACP'
PRINT ''

PRINT 'System\CurrentControlSet\Control\Nls\CodePage:'
PRINT '----------------------------------------------'
EXEC master.dbo.sp_tmpregread 'HKEY_LOCAL_MACHINE', 
  'System\CurrentControlSet\Control\Nls\CodePage', 'OEMCP'
PRINT ''

PRINT 'Software\Microsoft\DataAccess:'
PRINT '------------------------------'
EXEC master.dbo.sp_tmpregenumvalues  'HKEY_LOCAL_MACHINE', 
  'Software\Microsoft\DataAccess'
PRINT ''

PRINT 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias:'
PRINT '--------------------------------------------------------------------'
EXEC master.dbo.sp_tmpregread 'HKEY_LOCAL_MACHINE', 
  'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'ActiveTimeBias'
PRINT ''

PRINT 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation\Bias:'
PRINT '----------------------------------------------------------'
EXEC master.dbo.sp_tmpregread 'HKEY_LOCAL_MACHINE', 
  'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'Bias'
PRINT ''

IF (CHARINDEX('7.0',@@VERSION)<>-1) BEGIN
	PRINT 'SYSTEM\CurrentControlSet\Services\LicenseInfo\MSSQL7.00:'
	PRINT '--------------------------------------------------------'
	EXEC master.dbo.sp_tmpregread 'HKEY_LOCAL_MACHINE',
	  'SYSTEM\CurrentControlSet\Services\LicenseInfo\MSSQL7.00', 'ConcurrentLimit'
END ELSE BEGIN
	PRINT 'SOFTWARE\Microsoft\Microsoft SQL Server\80\MSSQLLicenseInfo\MSSQL8.00:'
	PRINT '----------------------------------------------------------------------'
	EXEC master.dbo.sp_tmpregread 'HKEY_LOCAL_MACHINE',
	  'SOFTWARE\Microsoft\Microsoft SQL Server\80\MSSQLLicenseInfo\MSSQL8.00',
	   'ConcurrentLimit'
END
PRINT ''
PRINT ''


PRINT '-> sp_configure'
declare @show_advance int 
if (select value from master.dbo.syscurconfigs where config = 518) = 1 
   select @show_advance = 1 
else 
   select @show_advance = 0 
if @show_advance = 0 
begin 
    exec sp_configure 'show advanced option',1 
    reconfigure with override
    exec sp_configure	
    exec sp_configure 'show advanced option',0 
    reconfigure with override
end 
else 
    exec sp_configure

PRINT '-> sp_who'
exec sp_who
PRINT ''

PRINT '-> sp_lock'
exec sp_lock
PRINT ''

PRINT '-> sp_helpdb'
exec sp_helpdb
PRINT ''

PRINT '-> xp_msver'
exec master.dbo.xp_msver
PRINT ''

PRINT '-> sp_helpextendedproc'
exec sp_helpextendedproc
PRINT ''

PRINT '-> Sysprocesses'
select spid, kpid, blocked, waittype, waittime, lastwaittype, 
  LEFT (waitresource, 50) AS waitresource, dbid, 
  uid, cpu, physical_io, memusage, login_time, last_batch, ecid, open_tran, 
  status, sid, LEFT (hostname, 30) AS hostname, 
  LEFT (program_name, 50) AS program_name, hostprocess, cmd, 
  LEFT (nt_domain, 30) AS nt_domain, LEFT (nt_username, 30) AS nt_username, 
  net_address, net_library, loginame, context_info
from master.dbo.sysprocesses
PRINT ''

-- fn_virtualservernodes is not present in 7.0
IF (CHARINDEX ('Microsoft SQL Server  7.00', @@VERSION) = 0)
BEGIN
    EXEC ('PRINT ''-> ::fn_virtualservernodes()''')
    EXEC ('SELECT * FROM ::fn_virtualservernodes()')
    EXEC ('PRINT ''''')
END

PRINT '-> sysdevices'
SELECT * from master.dbo.sysdevices
PRINT ''

PRINT '-> sysdatabases'
SELECT * from master.dbo.sysdatabases
PRINT ''

--Input buffers
PRINT 'Input buffer SPIDs'

declare @spid smallint
declare @i_buff_string char(30) 
set nocount on   
declare bufCursor CURSOR FOR SELECT spid from master.dbo.sysprocesses where spid > 10
FOR READ ONLY
open bufCursor
fetch next from bufCursor into @spid 
while (@@fetch_status <> -1) 
begin 
    SET @i_buff_string = ('DBCC INPUTBUFFER (' + convert(char(6),@spid) +')') 
    PRINT '-> '+@i_buff_string 
    exec (@i_buff_string) 
		PRINT ''
    fetch next from bufCursor into @spid 
end 
close bufCursor
deallocate bufCursor

PRINT '-> Head blockers'

select spid as [Blocking spid],loginame,hostname,program_name as progname,cmd,status,physical_io,waittype
from master.dbo.sysprocesses 
where spid in (select blocked from master.dbo.sysprocesses)
and blocked=0
PRINT ''

PRINT '-> SELECT @@version:'
PRINT @@VERSION
PRINT ''

PRINT '-> Current login (SUSER_SNAME):'
PRINT SUSER_SNAME ()
PRINT ''

PRINT '-> SQL Server name (@@SERVERNAME):'
PRINT @@SERVERNAME
PRINT ''

PRINT '-> Host (client) machine name (HOST_NAME):'
PRINT HOST_NAME()
PRINT ''

PRINT '-> @@LANGUAGE:'
PRINT @@LANGUAGE
PRINT ''

/*
PRINT '-> DBCC PSS(n):'

DBCC TRACEON (3604)
DECLARE @sp int
DECLARE @cmd varchar(255)
DECLARE spid_curs INSENSITIVE CURSOR  FOR 
  SELECT CONVERT (int, spid) AS spid 
  FROM master.dbo.sysprocesses WHERE spid > 6
OPEN spid_curs
FETCH NEXT FROM spid_curs INTO @sp
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @cmd = 'DBCC PSS (0, ' + CONVERT (varchar, @sp) + ')'
    PRINT '-> ' + @cmd
    EXEC (@cmd)
	  PRINT ''
  END
  FETCH NEXT FROM spid_curs INTO @sp
END
CLOSE spid_curs
DEALLOCATE spid_curs
DBCC TRACEOFF(3604)

*/

PRINT '-> DBCC TRACESTATUS (-1):'
DBCC TRACESTATUS (-1)
PRINT ''

PRINT '-> DBCC OPENTRAN (''<database>''):'

DECLARE @dbname sysname
DECLARE @tmpstr varchar(255)  
-- Note: won't work for 7.0/2K db's with Unicode names, 
-- but nvarchar won't work on 6.5.
DECLARE db_cursor cursor FOR 
SELECT name FROM master.dbo.sysdatabases 
WHERE status&32 + status&64 + status&128 + status&256 + status&512 = 0
  AND name NOT IN ('master', 'model', 'msdb', 'pubs', 'Northwind')  
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN 
    SET @tmpstr = 'DBCC OPENTRAN (''' + @dbname + ''')'
    PRINT @tmpstr
    EXEC (@tmpstr)
    PRINT ''
  END
  FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor 
DEALLOCATE db_cursor 
PRINT ''

PRINT '-> DBCC SQLPERF (THREADS)'
DBCC SQLPERF (THREADS)
PRINT ''

PRINT '-> DBCC SQLPERF (NETSTATS)'
DBCC SQLPERF (NETSTATS)
PRINT ''

PRINT '-> DBCC SQLPERF (IOSTATS)'
DBCC SQLPERF (IOSTATS)
PRINT ''

PRINT '-> DBCC SQLPERF (SPINLOCKSTATS)'
DBCC SQLPERF (SPINLOCKSTATS)
PRINT ''

-- This is potentially too large (was 80MB in one case) to capture by default. 
IF @bGetSyscacheobjects = 1
BEGIN
  PRINT '-> syscacheobjects'
  SELECT * FROM master.dbo.syscacheobjects
  PRINT ''
END

PRINT '-> DBCC MEMORYSTATUS'
DBCC MEMORYSTATUS
PRINT ''

PRINT '-> DBCC SQLPERF (UMSSTATS)'
DBCC SQLPERF (UMSSTATS)
PRINT ''

PRINT '-> DBCC SQLPERF (WAITSTATS)'
DBCC SQLPERF (WAITSTATS)
PRINT ''

PRINT '-> DBCC SQLPERF (LRUSTATS)'
DBCC SQLPERF (LRUSTATS)
PRINT ''

PRINT '-> sysperfinfo snapshot #1'
PRINT CONVERT (varchar, GETDATE(), 109)
SELECT * FROM master.dbo.sysperfinfo
WAITFOR DELAY '0:0:05'
PRINT '-> sysperfinfo snapshot #2'
PRINT CONVERT (varchar, GETDATE(), 109)
SELECT * FROM master.dbo.sysperfinfo
PRINT ''

PRINT '-> NET START'
EXEC master.dbo.xp_cmdshell 'NET START'
PRINT ''

DECLARE @IsFullTextInstalled int
PRINT '-> Full-text information'
PRINT '-> FULLTEXTSERVICEPROPERTY (IsFulltextInstalled)'
SET @IsFullTextInstalled = FULLTEXTSERVICEPROPERTY ('IsFulltextInstalled')
PRINT CASE @IsFullTextInstalled 
    WHEN 1 THEN '1 - Yes' 
    WHEN 0 THEN '0 - No' 
    ELSE 'Unknown'
  END
IF (@IsFullTextInstalled = 1)
BEGIN
  PRINT '-> FULLTEXTSERVICEPROPERTY (ResourceUsage)'
  PRINT CASE FULLTEXTSERVICEPROPERTY ('ResourceUsage')
      WHEN 0 THEN '0 - MSSearch not running'
      WHEN 1 THEN '1 - Background'
      WHEN 2 THEN '2 - Low'
      WHEN 3 THEN '3 - Normal'
      WHEN 4 THEN '4 - High'
      WHEN 5 THEN '5 - Highest'
      ELSE CONVERT (varchar, FULLTEXTSERVICEPROPERTY ('ResourceUsage'))
    END

  PRINT '-> FULLTEXTSERVICEPROPERTY (ConnectTimeout)'
  PRINT CONVERT (varchar, FULLTEXTSERVICEPROPERTY ('ConnectTimeout')) + ' sec'
  PRINT ''

  DECLARE @dbn varchar(31)
  DECLARE @cm varchar(8000)
  DECLARE db_cursor CURSOR FOR
  SELECT name FROM master.dbo.sysdatabases WHERE DATABASEPROPERTYEX (name, 'IsFulltextEnabled') = 1
  FOR READ ONLY
  IF 0 = @@ERROR
  BEGIN
    OPEN db_cursor
    IF 0 = @@ERROR
    BEGIN
      FETCH db_cursor INTO @dbn
      WHILE @@FETCH_STATUS <> -1 AND 0 = @@ERROR
      BEGIN
        SELECT @cm = '
USE ' + + @dbn + '
PRINT ''-> sp_help_fulltext_catalogs''
EXEC sp_help_fulltext_catalogs
PRINT ''-> sp_help_fulltext_tables''
EXEC sp_help_fulltext_tables
PRINT ''-> sp_help_fulltext_columns''
EXEC sp_help_fulltext_columns
PRINT ''-> Catalog properties''
SELECT name, FULLTEXTCATALOGPROPERTY (name, ''ItemCount'') AS ItemCount, 
  CONVERT (varchar, FULLTEXTCATALOGPROPERTY (name, ''IndexSize'')) + ''MB'' AS IndexSize, 
  FULLTEXTCATALOGPROPERTY (name, ''UniqueKeyCount'') AS [Unique word count] 
FROM sysfulltextcatalogs 
USE master'
        PRINT '-> Full text information for db [' + @dbn + ']'
        EXEC(@cm)
        FETCH db_cursor INTO @dbn
      END
      CLOSE db_cursor
    END
    DEALLOCATE db_cursor
  END
END
PRINT ''

PRINT '-> Relative time spent on I/O, CPU, and idle since server start'
SELECT @@CPU_BUSY AS [@@CPU_BUSY], @@IDLE AS [@@IDLE], @@IO_BUSY AS [@@IO_BUSY], 
  CONVERT (varchar(8), CONVERT (numeric (6, 4), (100.0 * @@CPU_BUSY / (@@CPU_BUSY + @@IDLE + @@IO_BUSY)))) + '%' AS Pct_CPU_BUSY, 
  CONVERT (varchar(8), CONVERT (numeric (6, 4), (100.0 * @@IDLE / (@@CPU_BUSY + @@IDLE + @@IO_BUSY)))) + '%' AS Pct_IDLE, 
  CONVERT (varchar(8), CONVERT (numeric (6, 4), (100.0 * @@IO_BUSY / (@@CPU_BUSY + @@IDLE + @@IO_BUSY)))) + '%' AS Pct_IO_BUSY
PRINT ''

PRINT '-> Misc network and I/O stats'
SELECT @@PACK_RECEIVED AS [@@PACK_RECEIVED], @@PACK_SENT AS [@@PACK_SENT], 
  @@PACKET_ERRORS AS [@@PACKET_ERRORS (network errors e.g. 17824)]
SELECT @@TOTAL_READ AS [@@TOTAL_READ], @@TOTAL_WRITE AS [@@TOTAL_WRITE], 
  @@TOTAL_ERRORS AS [@@TOTAL_ERRORS (disk read/write I/O errors)] 
---- Disabled -- On Win2k winmsd calls a MMC snapin and this doesn't
---- recognize the old winmsd cmd line params. 
-- PRINT ''
-- PRINT '======== Generating WinMSD Report'
-- EXEC master.dbo.xp_cmdshell 'c: & cd \ & winmsd -a'
-- GO
PRINT ''

PRINT '-> GETDATE()'
PRINT CONVERT (varchar, GETDATE(), 109)
PRINT ''
PRINT 'Done.'
GO
IF (CHARINDEX('7.00.',@@VERSION)<>0) OR (CHARINDEX('8.00.',@@VERSION)<>0) BEGIN
	IF (OBJECT_ID('dbo.sp_sqldiag07','P') IS NULL) RAISERROR('Error creating sp_sqldiag07',16,127)
	-- If running on 8.0, rename the proc to the 8.0 version
	-- since the contents are the same and the collector expects ...08
	IF (CHARINDEX('8.00.',@@VERSION)<>0)
        BEGIN
		PRINT ''
		RAISERROR ('===== Renaming sp_sqldiag07 to sp_sqldiag08', 0, 1) WITH NOWAIT
		IF OBJECT_ID('dbo.sp_sqldiag08','P') IS NOT NULL drop proc dbo.sp_sqldiag08
		EXEC sp_rename 'sp_sqldiag07', 'sp_sqldiag08'
		IF (OBJECT_ID('dbo.sp_sqldiag08','P') IS NULL) RAISERROR('Error renaming sp_sqldiag07 to sp_sqldiag08',16,127)
	END
END
GO

IF OBJECT_ID('dbo.sp_sqldiag09','P') IS NOT NULL drop proc dbo.sp_sqldiag09
GO 
PRINT ''
RAISERROR ('===== Creating sp_sqldiag09', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_sqldiag09 @bGetQueryStats int = 0 
AS

PRINT 'Errorlogs'
PRINT '---------'

declare @i tinyint, @res int
set @i=0
while (@i<255) begin
	if (0=@i) begin
		print 'ERRORLOG'
		exec @res=master.dbo.xp_readerrorlog 
	end	else begin
		print 'ERRORLOG.'+cast(@i as varchar(3))
		exec @res=master.dbo.xp_readerrorlog @i
	end
	if (@@error<>0) OR (@res<>0) break
	set @i=@i+1
end

PRINT ''

PRINT '-> sp_configure'
if (select value from sys.sysconfigures where config = 518) = 1 
begin
    exec sp_configure	
end
else 
begin
    exec sp_configure 'show advanced option',1 
    reconfigure with override
    exec sp_configure	
    exec sp_configure 'show advanced option',0 
    reconfigure with override
end

PRINT '-> sp_who'
exec sp_who
PRINT ''

PRINT '-> sp_lock'
exec sp_lock
PRINT ''

PRINT '-> sp_helpdb'
exec sp_helpdb
PRINT ''

PRINT '-> xp_msver'
exec master.dbo.xp_msver
PRINT ''

PRINT '-> sp_helpextendedproc'
exec sp_helpextendedproc
PRINT ''

PRINT '-> sysprocesses'
select * from sys.sysprocesses
PRINT ''

PRINT '-> sys.dm_exec_sessions'
select * from sys.dm_exec_sessions
PRINT ''

PRINT '-> ::fn_virtualservernodes()'
SELECT * FROM ::fn_virtualservernodes()
PRINT ''

PRINT '-> sysdevices'
select * from sys.sysdevices
PRINT ''

PRINT '-> sysdatabases'
SELECT * from sys.sysdatabases
PRINT ''

PRINT '-> sys.master_files'
select * from sys.master_files
PRINT ''

--Input buffers
PRINT '-> Non-NULL input buffers by SPID'
SELECT 
p.spid,
(select SUBSTRING (REPLACE (REPLACE (text, char(13), ' '), char(10), ' '), 1, 8000) FROM sys.dm_exec_sql_text(p.sql_handle)) AS query_text 
FROM sys.sysprocesses p
WHERE p.spid>10 AND (select text FROM sys.dm_exec_sql_text(p.sql_handle)) IS NOT NULL
PRINT ''

--Query stats
PRINT '-> Stats for currently running queries'
select TOP 10000
    r.*, 
    t.dbid, 
    t.objectid, 
    t.encrypted,
    substring(t.text, statement_start_offset / 2, (case when statement_end_offset = -1 then datalength(t.text) else statement_end_offset end - statement_start_offset) / 2) as query_text
from sys.dm_exec_requests as r outer apply sys.dm_exec_sql_text(r.sql_handle) as t
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> Head blockers'
select *
from sys.sysprocesses 
where spid in (select blocked from sys.sysprocesses)
and blocked=0
PRINT ''

PRINT '-> SELECT @@version:'
PRINT @@VERSION
PRINT ''

PRINT '-> Current login (SUSER_SNAME):'
PRINT SUSER_SNAME ()
PRINT ''

PRINT '-> SQL Server name (@@SERVERNAME):'
PRINT @@SERVERNAME
PRINT ''

PRINT '-> Host (client) machine name (HOST_NAME):'
PRINT HOST_NAME()
PRINT ''

PRINT '-> @@LANGUAGE:'
PRINT @@LANGUAGE
PRINT ''

PRINT '-> DBCC TRACESTATUS (-1):'
DBCC TRACESTATUS (-1)
PRINT ''

PRINT '-> sys.dm_tran_database_transactions'
select TOP 10000 db_name(database_id), * from sys.dm_tran_database_transactions 
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_tran_active_transactions'
select TOP 10000 * from sys.dm_tran_active_transactions 
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''


PRINT '-> sys.dm_os_sys_info'
select TOP 10000 * from sys.dm_os_sys_info
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_schedulers'
select * from sys.dm_os_schedulers
PRINT ''

PRINT '-> sys.dm_os_threads'
select TOP 10000 * from sys.dm_os_threads
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_workers'
select TOP 10000 * from sys.dm_os_workers
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_tasks'
select TOP 10000 * from sys.dm_os_tasks
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_io_pending_io_requests'
select TOP 10000 * from sys.dm_io_pending_io_requests
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_io_virtual_file_stats'
SELECT TOP 10000 * FROM sys.dm_io_virtual_file_stats(NULL, NULL)
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_latch_stats'
select TOP 10000 * from sys.dm_os_latch_stats
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_sublatches'
select TOP 10000 * from sys.dm_os_sublatches
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_memory_pools'
select TOP 10000 * from sys.dm_os_memory_pools
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_memory_clerks'
select TOP 10000 * from sys.dm_os_memory_clerks
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_wait_stats'
select TOP 10000 * from sys.dm_os_wait_stats
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_waiting_tasks'
select TOP 10000 * from sys.dm_os_waiting_tasks
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_loaded_modules'
select TOP 10000 * from sys.dm_os_loaded_modules
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_cluster_nodes'
select * from sys.dm_os_cluster_nodes
PRINT ''

PRINT '-> sys.dm_io_cluster_shared_drives'
select TOP 10000 * from sys.dm_io_cluster_shared_drives
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_clr_appdomains'
select TOP 10000 * from sys.dm_clr_appdomains
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_clr_loaded_assemblies'
select TOP 10000 * from sys.dm_clr_loaded_assemblies
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_clr_properties'
select TOP 10000 * from sys.dm_clr_properties
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_clr_tasks'
select TOP 10000 * from sys.dm_clr_tasks
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.assemblies'
select TOP 10000 * from sys.assemblies
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.assembly_modules'
select TOP 10000 * from sys.assembly_modules
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.assembly_types'
select TOP 10000 * from sys.assembly_types
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.database_files'
select TOP 10000 * from sys.database_files 
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_db_file_space_usage'
select TOP 10000 * from sys.dm_db_file_space_usage
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_db_session_space_usage'
select TOP 10000 * from sys.dm_db_session_space_usage
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_db_task_space_usage'
select TOP 10000 * from sys.dm_db_task_space_usage
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_exec_query_optimizer_info'
select TOP 10000 * from sys.dm_exec_query_optimizer_info
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_broker_activated_tasks'
select TOP 10000 * from sys.dm_broker_activated_tasks
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_broker_connections'
select TOP 10000 * from sys.dm_broker_connections
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_broker_queue_monitors'
select TOP 10000 * from sys.dm_broker_queue_monitors
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''


-- This is potentially too large to capture by default. 
IF @bGetQueryStats = 1
BEGIN
  PRINT '-> sys.dm_exec_query_stats'
  SELECT TOP 10000 * FROM sys.dm_exec_query_stats 
  IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
  PRINT ''
END

PRINT '-> sysperfinfo snapshot #1'
PRINT CONVERT (varchar, GETDATE(), 109)
SELECT * FROM sys.sysperfinfo
WAITFOR DELAY '0:0:05'
PRINT '-> sysperfinfo snapshot #2'
PRINT CONVERT (varchar, GETDATE(), 109)
SELECT * FROM sys.sysperfinfo
PRINT ''

DECLARE @IsFullTextInstalled int
PRINT '-> Full-text information'
PRINT '-> FULLTEXTSERVICEPROPERTY (IsFulltextInstalled)'
SET @IsFullTextInstalled = FULLTEXTSERVICEPROPERTY ('IsFulltextInstalled')
PRINT CASE @IsFullTextInstalled 
    WHEN 1 THEN '1 - Yes' 
    WHEN 0 THEN '0 - No' 
    ELSE 'Unknown'
  END
IF (@IsFullTextInstalled = 1)
BEGIN
  PRINT '-> FULLTEXTSERVICEPROPERTY (ResourceUsage)'
  PRINT CASE FULLTEXTSERVICEPROPERTY ('ResourceUsage')
      WHEN 0 THEN '0 - MSSearch not running'
      WHEN 1 THEN '1 - Background'
      WHEN 2 THEN '2 - Low'
      WHEN 3 THEN '3 - Normal'
      WHEN 4 THEN '4 - High'
      WHEN 5 THEN '5 - Highest'
      ELSE CONVERT (varchar, FULLTEXTSERVICEPROPERTY ('ResourceUsage'))
    END

  PRINT '-> FULLTEXTSERVICEPROPERTY (ConnectTimeout)'
  PRINT CONVERT (varchar, FULLTEXTSERVICEPROPERTY ('ConnectTimeout')) + ' sec'
  PRINT ''

  PRINT '-> sys.dm_fts_active_catalogs'
  select TOP 10000 * from sys.dm_fts_active_catalogs
  IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
  PRINT ''

  PRINT '-> sys.dm_fts_index_population'
  select TOP 10000 * from sys.dm_fts_index_population
  IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
  PRINT ''

  PRINT '-> sys.dm_fts_memory_pools'
  select TOP 10000 * from sys.dm_fts_memory_pools
  IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
  PRINT ''

  PRINT '-> sys.dm_fts_population_ranges'
  select TOP 10000 * from sys.dm_fts_population_ranges
  IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
  PRINT ''

  PRINT '-> msdb..suspect_pages'
  select TOP 10000 * from msdb..suspect_pages
  IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
  PRINT ''

  DECLARE @dbn varchar(31)
  DECLARE @cm varchar(8000)
  DECLARE db_cursor CURSOR FOR
  SELECT name FROM master.dbo.sysdatabases WHERE DATABASEPROPERTYEX (name, 'IsFulltextEnabled') = 1
  FOR READ ONLY
  IF 0 = @@ERROR
  BEGIN
    OPEN db_cursor
    IF 0 = @@ERROR
    BEGIN
      FETCH db_cursor INTO @dbn
      WHILE @@FETCH_STATUS <> -1 AND 0 = @@ERROR
      BEGIN
        SELECT @cm = '
USE ' + + @dbn + '
PRINT ''-> sp_help_fulltext_catalogs''
EXEC sp_help_fulltext_catalogs
PRINT ''-> sp_help_fulltext_tables''
EXEC sp_help_fulltext_tables
PRINT ''-> sp_help_fulltext_columns''
EXEC sp_help_fulltext_columns
PRINT ''-> Catalog properties''
SELECT name, FULLTEXTCATALOGPROPERTY (name, ''ItemCount'') AS ItemCount, 
  CONVERT (varchar, FULLTEXTCATALOGPROPERTY (name, ''IndexSize'')) + ''MB'' AS IndexSize, 
  FULLTEXTCATALOGPROPERTY (name, ''UniqueKeyCount'') AS [Unique word count] 
FROM sysfulltextcatalogs 
USE tempdb'
        PRINT '-> Full text information for db [' + @dbn + ']'
        EXEC(@cm)
        FETCH db_cursor INTO @dbn
      END
      CLOSE db_cursor
    END
    DEALLOCATE db_cursor
  END
END
PRINT ''

PRINT '-> Relative time spent on I/O, CPU, and idle since server start'
SELECT @@CPU_BUSY AS [@@CPU_BUSY], @@IDLE AS [@@IDLE], @@IO_BUSY AS [@@IO_BUSY], 
  CONVERT (varchar(8), CONVERT (numeric (6, 4), (100.0 * @@CPU_BUSY / (@@CPU_BUSY + @@IDLE + @@IO_BUSY)))) + '%' AS Pct_CPU_BUSY, 
  CONVERT (varchar(8), CONVERT (numeric (6, 4), (100.0 * @@IDLE / (@@CPU_BUSY + @@IDLE + @@IO_BUSY)))) + '%' AS Pct_IDLE, 
  CONVERT (varchar(8), CONVERT (numeric (6, 4), (100.0 * @@IO_BUSY / (@@CPU_BUSY + @@IDLE + @@IO_BUSY)))) + '%' AS Pct_IO_BUSY
PRINT ''

PRINT '-> Misc network and I/O stats'
SELECT @@PACK_RECEIVED AS [@@PACK_RECEIVED], @@PACK_SENT AS [@@PACK_SENT], 
  @@PACKET_ERRORS AS [@@PACKET_ERRORS (network errors e.g. 17824)]
SELECT @@TOTAL_READ AS [@@TOTAL_READ], @@TOTAL_WRITE AS [@@TOTAL_WRITE], 
  @@TOTAL_ERRORS AS [@@TOTAL_ERRORS (disk read/write I/O errors)] 
PRINT ''

PRINT '-> GETDATE()'
PRINT CONVERT (varchar, GETDATE(), 109)
PRINT ''
PRINT 'Done.'
GO

IF (CHARINDEX('9.00.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_sqldiag09','P') IS NULL) 
	RAISERROR('Error creating sp_sqldiag09',16,127)
GO

IF OBJECT_ID('dbo.sp_sqldiag10','P') IS NOT NULL drop proc dbo.sp_sqldiag10
GO 
PRINT ''
RAISERROR ('===== Creating sp_sqldiag10', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_sqldiag10 @bGetQueryStats int = 0 
AS

PRINT 'Errorlogs'
PRINT '---------'

declare @i tinyint, @res int
set @i=0
while (@i<255) begin
	if (0=@i) begin
		print 'ERRORLOG'
		exec @res=master.dbo.xp_readerrorlog 
	end	else begin
		print 'ERRORLOG.'+cast(@i as varchar(3))
		exec @res=master.dbo.xp_readerrorlog @i
	end
	if (@@error<>0) OR (@res<>0) break
	set @i=@i+1
end

PRINT ''

PRINT '-> sp_configure'
if (select value from sys.sysconfigures where config = 518) = 1 
begin
    exec sp_configure	
end
else 
begin
    exec sp_configure 'show advanced option',1 
    reconfigure with override
    exec sp_configure	
    exec sp_configure 'show advanced option',0 
    reconfigure with override
end

PRINT '-> sp_who'
exec sp_who
PRINT ''

PRINT '-> sp_lock'
exec sp_lock
PRINT ''

PRINT '-> sp_helpdb'
exec sp_helpdb
PRINT ''

PRINT '-> xp_msver'
exec master.dbo.xp_msver
PRINT ''

PRINT '-> sp_helpextendedproc'
exec sp_helpextendedproc
PRINT ''

PRINT '-> sysprocesses'
select * from sys.sysprocesses
PRINT ''

PRINT '-> sys.dm_exec_sessions'
select * from sys.dm_exec_sessions
PRINT ''

PRINT '-> ::fn_virtualservernodes()'
SELECT * FROM ::fn_virtualservernodes()
PRINT ''

PRINT '-> sysdevices'
select * from sys.sysdevices
PRINT ''

PRINT '-> sysdatabases'
SELECT * from sys.sysdatabases
PRINT ''

PRINT '-> sys.master_files'
select * from sys.master_files
PRINT ''

--Input buffers
PRINT '-> Non-NULL input buffers by SPID'
SELECT 
p.spid,
(select SUBSTRING (REPLACE (REPLACE (text, char(13), ' '), char(10), ' '), 1, 8000) FROM sys.dm_exec_sql_text(p.sql_handle)) AS query_text 
FROM sys.sysprocesses p
WHERE p.spid>10 AND (select text FROM sys.dm_exec_sql_text(p.sql_handle)) IS NOT NULL
PRINT ''

--Query stats
PRINT '-> Stats for currently running queries'
select TOP 10000
    r.*, 
    t.dbid, 
    t.objectid, 
    t.encrypted,
    substring(t.text, statement_start_offset / 2, (case when statement_end_offset = -1 then datalength(t.text) else statement_end_offset end - statement_start_offset) / 2) as query_text
from sys.dm_exec_requests as r outer apply sys.dm_exec_sql_text(r.sql_handle) as t
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> Head blockers'
select *
from sys.sysprocesses 
where spid in (select blocked from sys.sysprocesses)
and blocked=0
PRINT ''

PRINT '-> SELECT @@version:'
PRINT @@VERSION
PRINT ''

PRINT '-> Current login (SUSER_SNAME):'
PRINT SUSER_SNAME ()
PRINT ''

PRINT '-> SQL Server name (@@SERVERNAME):'
PRINT @@SERVERNAME
PRINT ''

PRINT '-> Host (client) machine name (HOST_NAME):'
PRINT HOST_NAME()
PRINT ''

PRINT '-> @@LANGUAGE:'
PRINT @@LANGUAGE
PRINT ''

PRINT '-> DBCC TRACESTATUS (-1):'
DBCC TRACESTATUS (-1)
PRINT ''

PRINT '-> sys.dm_tran_database_transactions'
select TOP 10000 db_name(database_id), * from sys.dm_tran_database_transactions 
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_tran_active_transactions'
select TOP 10000 * from sys.dm_tran_active_transactions 
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''


PRINT '-> sys.dm_os_sys_info'
select TOP 10000 * from sys.dm_os_sys_info
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_schedulers'
select * from sys.dm_os_schedulers
PRINT ''

PRINT '-> sys.dm_os_threads'
select TOP 10000 * from sys.dm_os_threads
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_workers'
select TOP 10000 * from sys.dm_os_workers
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_tasks'
select TOP 10000 * from sys.dm_os_tasks
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_io_pending_io_requests'
select TOP 10000 * from sys.dm_io_pending_io_requests
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_io_virtual_file_stats'
SELECT TOP 10000 * FROM sys.dm_io_virtual_file_stats(NULL, NULL)
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_latch_stats'
select TOP 10000 * from sys.dm_os_latch_stats
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_os_spinlock_stats'
select TOP 10000 * from sys.dm_os_spinlock_stats
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_sublatches'
select TOP 10000 * from sys.dm_os_sublatches
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_memory_pools'
select TOP 10000 * from sys.dm_os_memory_pools
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_memory_clerks'
select TOP 10000 * from sys.dm_os_memory_clerks
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_os_memory_brokers'
select TOP 10000 * from sys.dm_os_memory_brokers
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_os_memory_nodes'
select TOP 10000 * from sys.dm_os_memory_nodes
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_wait_stats'
select TOP 10000 * from sys.dm_os_wait_stats
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_waiting_tasks'
select TOP 10000 * from sys.dm_os_waiting_tasks
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_loaded_modules'
select TOP 10000 * from sys.dm_os_loaded_modules
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_os_cluster_nodes'
select * from sys.dm_os_cluster_nodes
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_os_nodes'
select TOP 10000 * from sys.dm_os_nodes
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_os_process_memory'
select TOP 10000 * from sys.dm_os_process_memory
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_os_sys_memory'
select TOP 10000 * from sys.dm_os_sys_memory
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_io_cluster_shared_drives'
select TOP 10000 * from sys.dm_io_cluster_shared_drives
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_clr_appdomains'
select TOP 10000 * from sys.dm_clr_appdomains
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_clr_loaded_assemblies'
select TOP 10000 * from sys.dm_clr_loaded_assemblies
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_clr_properties'
select TOP 10000 * from sys.dm_clr_properties
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_clr_tasks'
select TOP 10000 * from sys.dm_clr_tasks
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.assemblies'
select TOP 10000 * from sys.assemblies
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.assembly_modules'
select TOP 10000 * from sys.assembly_modules
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.assembly_types'
select TOP 10000 * from sys.assembly_types
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.database_files'
select TOP 10000 * from sys.database_files 
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_db_file_space_usage'
select TOP 10000 * from sys.dm_db_file_space_usage
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_db_session_space_usage'
select TOP 10000 * from sys.dm_db_session_space_usage
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_db_task_space_usage'
select TOP 10000 * from sys.dm_db_task_space_usage
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_exec_query_optimizer_info'
select TOP 10000 * from sys.dm_exec_query_optimizer_info
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_exec_query_memory_grants'
select TOP 10000 * from sys.dm_exec_query_memory_grants
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_exec_query_resource_semaphores'
select TOP 10000 * from sys.dm_exec_query_resource_semaphores
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_exec_query_transformation_stats'
select TOP 10000 * from sys.dm_exec_query_transformation_stats
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_broker_activated_tasks'
select TOP 10000 * from sys.dm_broker_activated_tasks
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_broker_connections'
select TOP 10000 * from sys.dm_broker_connections
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

PRINT '-> sys.dm_broker_queue_monitors'
select TOP 10000 * from sys.dm_broker_queue_monitors
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_resource_governor_configuration'
select TOP 10000 * from sys.dm_resource_governor_configuration
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_resource_governor_resource_pools'
select TOP 10000 * from sys.dm_resource_governor_resource_pools
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_resource_governor_workload_groups'
select TOP 10000 * from sys.dm_resource_governor_workload_groups
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_database_encryption_keys'
select TOP 10000 * from sys.dm_database_encryption_keys
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_filestream_file_io_handles'
select TOP 10000 * from sys.dm_filestream_file_io_handles
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- New DMV in SQL Server 2008
PRINT '-> sys.dm_filestream_file_io_requests'
select TOP 10000 * from sys.dm_filestream_file_io_requests
IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
PRINT ''

-- This is potentially too large to capture by default. 
IF @bGetQueryStats = 1
BEGIN
  PRINT '-> sys.dm_exec_query_stats'
  SELECT TOP 10000 * FROM sys.dm_exec_query_stats 
  IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
  PRINT ''
END

PRINT '-> sysperfinfo snapshot #1'
PRINT CONVERT (varchar, GETDATE(), 109)
SELECT * FROM sys.sysperfinfo
WAITFOR DELAY '0:0:05'
PRINT '-> sysperfinfo snapshot #2'
PRINT CONVERT (varchar, GETDATE(), 109)
SELECT * FROM sys.sysperfinfo
PRINT ''

DECLARE @IsFullTextInstalled int
PRINT '-> Full-text information'
PRINT '-> FULLTEXTSERVICEPROPERTY (IsFulltextInstalled)'
SET @IsFullTextInstalled = FULLTEXTSERVICEPROPERTY ('IsFulltextInstalled')
PRINT CASE @IsFullTextInstalled 
    WHEN 1 THEN '1 - Yes' 
    WHEN 0 THEN '0 - No' 
    ELSE 'Unknown'
  END
IF (@IsFullTextInstalled = 1)
BEGIN
  PRINT '-> FULLTEXTSERVICEPROPERTY (ResourceUsage)'
  PRINT CASE FULLTEXTSERVICEPROPERTY ('ResourceUsage')
      WHEN 0 THEN '0 - MSSearch not running'
      WHEN 1 THEN '1 - Background'
      WHEN 2 THEN '2 - Low'
      WHEN 3 THEN '3 - Normal'
      WHEN 4 THEN '4 - High'
      WHEN 5 THEN '5 - Highest'
      ELSE CONVERT (varchar, FULLTEXTSERVICEPROPERTY ('ResourceUsage'))
    END

  PRINT '-> FULLTEXTSERVICEPROPERTY (ConnectTimeout)'
  PRINT CONVERT (varchar, FULLTEXTSERVICEPROPERTY ('ConnectTimeout')) + ' sec'
  PRINT ''

  PRINT '-> sys.dm_fts_active_catalogs'
  select TOP 10000 * from sys.dm_fts_active_catalogs
  IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
  PRINT ''

  PRINT '-> sys.dm_fts_index_population'
  select TOP 10000 * from sys.dm_fts_index_population
  IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
  PRINT ''

  PRINT '-> sys.dm_fts_memory_pools'
  select TOP 10000 * from sys.dm_fts_memory_pools
  IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
  PRINT ''

  PRINT '-> sys.dm_fts_population_ranges'
  select TOP 10000 * from sys.dm_fts_population_ranges
  IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
  PRINT ''

  PRINT '-> msdb..suspect_pages'
  select TOP 10000 * from msdb..suspect_pages
  IF @@rowcount >= 10000 PRINT '<<<<< LIMIT OF 10000 ROWS EXCEEDED, SOME RESULTS NOT SHOWN >>>>>'
  PRINT ''

  DECLARE @dbn varchar(31)
  DECLARE @cm varchar(8000)
  DECLARE db_cursor CURSOR FOR
  SELECT name FROM master.dbo.sysdatabases WHERE DATABASEPROPERTYEX (name, 'IsFulltextEnabled') = 1
  FOR READ ONLY
  IF 0 = @@ERROR
  BEGIN
    OPEN db_cursor
    IF 0 = @@ERROR
    BEGIN
      FETCH db_cursor INTO @dbn
      WHILE @@FETCH_STATUS <> -1 AND 0 = @@ERROR
      BEGIN
        SELECT @cm = '
USE ' + + @dbn + '
PRINT ''-> sp_help_fulltext_catalogs''
EXEC sp_help_fulltext_catalogs
PRINT ''-> sp_help_fulltext_tables''
EXEC sp_help_fulltext_tables
PRINT ''-> sp_help_fulltext_columns''
EXEC sp_help_fulltext_columns
PRINT ''-> Catalog properties''
SELECT name, FULLTEXTCATALOGPROPERTY (name, ''ItemCount'') AS ItemCount, 
  CONVERT (varchar, FULLTEXTCATALOGPROPERTY (name, ''IndexSize'')) + ''MB'' AS IndexSize, 
  FULLTEXTCATALOGPROPERTY (name, ''UniqueKeyCount'') AS [Unique word count] 
FROM sysfulltextcatalogs 
USE tempdb'
        PRINT '-> Full text information for db [' + @dbn + ']'
        EXEC(@cm)
        FETCH db_cursor INTO @dbn
      END
      CLOSE db_cursor
    END
    DEALLOCATE db_cursor
  END
END
PRINT ''

PRINT '-> Relative time spent on I/O, CPU, and idle since server start'
SELECT @@CPU_BUSY AS [@@CPU_BUSY], @@IDLE AS [@@IDLE], @@IO_BUSY AS [@@IO_BUSY], 
  CONVERT (varchar(8), CONVERT (numeric (6, 4), (100.0 * @@CPU_BUSY / (@@CPU_BUSY + @@IDLE + @@IO_BUSY)))) + '%' AS Pct_CPU_BUSY, 
  CONVERT (varchar(8), CONVERT (numeric (6, 4), (100.0 * @@IDLE / (@@CPU_BUSY + @@IDLE + @@IO_BUSY)))) + '%' AS Pct_IDLE, 
  CONVERT (varchar(8), CONVERT (numeric (6, 4), (100.0 * @@IO_BUSY / (@@CPU_BUSY + @@IDLE + @@IO_BUSY)))) + '%' AS Pct_IO_BUSY
PRINT ''

PRINT '-> Misc network and I/O stats'
SELECT @@PACK_RECEIVED AS [@@PACK_RECEIVED], @@PACK_SENT AS [@@PACK_SENT], 
  @@PACKET_ERRORS AS [@@PACKET_ERRORS (network errors e.g. 17824)]
SELECT @@TOTAL_READ AS [@@TOTAL_READ], @@TOTAL_WRITE AS [@@TOTAL_WRITE], 
  @@TOTAL_ERRORS AS [@@TOTAL_ERRORS (disk read/write I/O errors)] 
PRINT ''

PRINT '-> GETDATE()'
PRINT CONVERT (varchar, GETDATE(), 109)
PRINT ''
PRINT 'Done.'
GO

/*
The board chagned the Product Version format in Katmai. The current foramt starting from Katmai will be single zero for minor version instead of two zeros in previous sql versions. " 10.m.bbbb.rr "
*/
IF (CHARINDEX('10.0.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_sqldiag10','P') IS NULL) 
	RAISERROR('Error creating sp_sqldiag10',16,127)
GO

/* SQL11 Version Bump */

IF OBJECT_ID('dbo.sp_sqldiag11','P') IS NOT NULL drop proc dbo.sp_sqldiag11
GO 
PRINT ''
RAISERROR ('===== Creating sp_sqldiag11', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_sqldiag11 @bGetQueryStats int = 0 
AS
BEGIN
  EXEC dbo.sp_sqldiag10 @bGetQueryStats
END
GO
IF (CHARINDEX('11.0.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_sqldiag11','P') IS NULL) 
	RAISERROR('Error creating sp_sqldiag11',16,127)
GO

/* SQL14 Version Bump */

IF OBJECT_ID('dbo.sp_sqldiag12','P') IS NOT NULL drop proc dbo.sp_sqldiag12
GO 
PRINT ''
RAISERROR ('===== Creating sp_sqldiag12', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_sqldiag12 @bGetQueryStats int = 0 
AS
BEGIN
  EXEC dbo.sp_sqldiag10 @bGetQueryStats
END
GO
IF (CHARINDEX('12.0.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_sqldiag12','P') IS NULL) 
	RAISERROR('Error creating sp_sqldiag12',16,127)
GO


/* SQL15 Version Bump */

IF OBJECT_ID('dbo.sp_sqldiag13','P') IS NOT NULL drop proc dbo.sp_sqldiag13
GO 
PRINT ''
RAISERROR ('===== Creating sp_sqldiag13', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_sqldiag13 @bGetQueryStats int = 0 
AS
BEGIN
  EXEC dbo.sp_sqldiag10 @bGetQueryStats
END
GO
IF (CHARINDEX('13.0.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_sqldiag13','P') IS NULL) 
	RAISERROR('Error creating sp_sqldiag13',16,127)


/*

sp_trace

*/

IF OBJECT_ID('dbo.sp_trace07','P') IS NOT NULL
  DROP PROC dbo.sp_trace07
GO
PRINT ''
RAISERROR ('===== Creating sp_trace07', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_trace07 @OnOff varchar(3)='/?', @FileName sysname=NULL, @TraceName sysname='sp_trace',
@Events varchar(300)=NULL, @Cols int=NULL,
@TraceType int=0,
@IncludeTextFilter sysname=NULL, @ExcludeTextFilter sysname=NULL,
@IncludeObjIdFilter int=NULL,
@IncludeAppFilter sysname=NULL, @ExcludeAppFilter sysname='SQLAgent%;SQL Server Profiler%',
@IncludeConnectionIdFilter int=NULL,
@MinCPUFilter int=0, @MaxCPUFilter int=0,
@IncludeDBIdFilter int=NULL,
@MinDurationFilter int=0, @MaxDurationFilter int=0,
@IncludeNTNameFilter sysname=NULL, @ExcludeNTNameFilter sysname=NULL,
@MinReadFilter int=0, @MaxReadFilter int=0,
@IncludeServerFilter sysname=NULL, @ExcludeServerFilter sysname=NULL,
@IncludeSPIdFilter int=NULL,
@IncludeUserFilter sysname=NULL, @ExcludeUserFilter sysname=NULL,
@MinWriteFilter int=0, @MaxWriteFilter int=0,
@MinSeverityFilter int=0, @MaxSeverityFilter int=0,
@IncludeHostFilter sysname=NULL, @ExcludeHostFilter sysname='PSSDIAG%',
@IncludeHpIdFilter int=NULL,
@IncludeIndIdFilter int=NULL,
@IncludeNTDomainFilter sysname=NULL, @ExcludeNTDomainFilter sysname=NULL,
@IncludeSysobjectsFilter int=NULL,
@AppName sysname='PSSDIAG'
AS
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
SET NOCOUNT ON

IF (@OnOff='/?') GOTO Help
SET @OnOff=UPPER(@OnOff)
IF (@OnOff<>'ON') AND (@OnOff<>'OFF') BEGIN
  RAISERROR('The @OnOff parameter must be either ''ON'' or ''OFF''.',16,10)
  RETURN -1
END

IF (ABS(@IncludeSysobjectsFilter)>1) BEGIN
  RAISERROR('The @IncludeSysobjectsFilter parameter must be either 0 or 1.',16,10)
  RETURN -1
END

-- Declare variables
DECLARE @OldQueueHandle int -- Queue handle of currently running trace queue
DECLARE @QueueHandle int -- Queue handle for new trace queue
DECLARE @OldFileName sysname -- Output file for currently trace

-- Stop the trace if running
PRINT 'Stopping the trace if it is running...'
IF OBJECT_ID('tempdb.dbo.PSSDIAGTraceQueue') IS NOT NULL BEGIN
  IF EXISTS(SELECT * FROM tempdb.dbo.PSSDIAGTraceQueue WHERE QueueName = @TraceName AND AppName=@AppName) BEGIN

    SELECT @OldQueueHandle = QueueHandle, @OldFileName= QueueFile
    FROM tempdb.dbo.PSSDIAGTraceQueue
    WHERE QueueName = @TraceName
    AND AppName=@AppName

    IF @@ROWCOUNT<>0 BEGIN
      EXEC master..xp_trace_destroyqueue @OldQueueHandle
      PRINT 'Deleted trace: ' + @TraceName+', Queue: '+CAST(@OldQueueHandle AS varchar)+'.'
      PRINT 'The trace output file name is: '+@OldFileName+'.'
      DELETE tempdb.dbo.PSSDIAGTraceQueue WHERE QueueName = @TraceName AND AppName=@AppName
    END
  END ELSE PRINT 'No active traces named '+@TraceName
END ELSE PRINT 'No active traces.'

IF @OnOff='OFF' RETURN 0 -- We've stopped the trace (if it's running), so exit

-- Append the datetime to the file name to create a new, unique file name.
IF @FileName IS NULL SET @FileName = 'c:\TEMP\sp_trace_' + CONVERT(CHAR(8),getdate(),112) + REPLACE(CONVERT(varchar(15),getdate(),114),':','')+'.trc'

-- Set the column mask for the data columns to capture.
-- Text data | Binary data | Database ID | Transaction ID | Connection ID|
-- Username | Domain name | Host name | Host process ID | Application name|
-- SQL username | SPID | Duration | Start time | End time | Reads | Writes|
-- CPU usage | Severity | Event Subclass | Object ID | Index ID |
-- Integer data | Server name | Event Class
IF @Cols IS NULL
  SET @Cols =  1|/*2|*/4|8|16|32|64|128|256|512|1024|2048|4096|8192|16384|32768|
  65536|131072|524288|1048576|2097152|8388608|16777216|33554432|67108864

-- Create the trace queue
EXEC master..xp_trace_addnewqueue 11000, 10000, 95, 90, @Cols, @QueueHandle output

-- Record the trace queue handle for subsequent jobs.  (This allows us to know how to stop our trace.)
IF OBJECT_ID('tempdb.dbo.PSSDIAGTraceQueue') IS NULL BEGIN
  CREATE TABLE tempdb.dbo.PSSDIAGTraceQueue (QueueHandle int, QueueName sysname, QueueFile sysname, AppName sysname )
  INSERT tempdb.dbo.PSSDIAGTraceQueue VALUES(@QueueHandle, @TraceName, @FileName, @AppName)
END ELSE BEGIN
  IF EXISTS(SELECT * FROM tempdb.dbo.PSSDIAGTraceQueue WHERE QueueName = @TraceName AND AppName=@AppName) BEGIN
    UPDATE tempdb.dbo.PSSDIAGTraceQueue SET QueueHandle = @QueueHandle WHERE QueueName = @TraceName AND AppName=@AppName
  END ELSE BEGIN
    INSERT tempdb.dbo.PSSDIAGTraceQueue VALUES(@QueueHandle, @TraceName, @FileName, @AppName)
  END
END

-- Set filters (default values avoid tracing the trace activity itself)
-- You can specify other filters like application name etc. by supplying strings to the @IncludeTextFilter/@ExcludeTextFilter parameters, separated by semicolons
SET @ExcludeTextFilter='EXEC% master..xp_trace%;sp_trace%;EXEC% sp_trace%'+ISNULL(';'+@ExcludeTextFilter,'')  -- Keep our own activity from showing up
EXEC master..xp_trace_settextfilter @QueueHandle, @IncludeTextFilter, @ExcludeTextFilter
EXEC master..xp_trace_setappfilter @QueueHandle, @IncludeAppFilter, @ExcludeAppFilter
EXEC master..xp_trace_setcpufilter @QueueHandle, @MinCPUFilter, @MaxCPUFilter
EXEC master..xp_trace_setdurationfilter @QueueHandle, @MinDurationFilter, @MaxDurationFilter
EXEC master..xp_trace_setntnmfilter @QueueHandle, @IncludeNTNameFilter, @ExcludeNTNameFilter
EXEC master..xp_trace_setreadfilter @QueueHandle, @MinReadFilter, @MaxReadFilter
EXEC master..xp_trace_setserverfilter @QueueHandle, @IncludeServerFilter, @ExcludeServerFilter
EXEC master..xp_trace_setuserfilter @QueueHandle, @IncludeUserFilter, @ExcludeUserFilter
EXEC master..xp_trace_setwritefilter @QueueHandle, @MinWriteFilter, @MaxWriteFilter
EXEC master..xp_trace_setseverityfilter @QueueHandle, @MinSeverityFilter, @MaxSeverityFilter
EXEC master..xp_trace_sethostfilter @QueueHandle, @IncludeHostFilter, @ExcludeHostFilter
EXEC master..xp_trace_setntdmfilter @QueueHandle, @IncludeNTDomainFilter, @ExcludeNTDomainFilter
IF @IncludeObjIdFilter IS NOT NULL
  EXEC master..xp_trace_setobjidfilter @QueueHandle, @IncludeObjIdFilter
IF @IncludeConnectionIdFilter IS NOT NULL
  EXEC master..xp_trace_setconnectionidfilter @QueueHandle, @IncludeConnectionIdFilter
IF @IncludeDBIdFilter IS NOT NULL
  EXEC master..xp_trace_setdbidfilter @QueueHandle, @IncludeDBIdFilter
IF @IncludeSPIdFilter IS NOT NULL
  EXEC master..xp_trace_setspidfilter @QueueHandle, @IncludeSPIdFilter
IF @IncludeHpIdFilter IS NOT NULL
  EXEC master..xp_trace_sethpidfilter @QueueHandle, @IncludeHpIdFilter
IF @IncludeIndIdFilter IS NOT NULL
  EXEC master..xp_trace_setindidfilter @QueueHandle, @IncludeIndIdFilter
IF @IncludeSysobjectsFilter IS NOT NULL
  EXEC master..xp_trace_setsysobjectsfilter @QueueHandle, @IncludeSysobjectsFilter

-- Specify the event classes to trace
-- To list all the event classes, execute the procedure xp_trace_geteventnames using Query Analyzer
-- or look up for that procedure in SQL Server BOL and add to the following list as needed
IF @Events IS NULL BEGIN
  IF @TraceType=0 BEGIN  -- Minimal trace
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 14 ,1 -- Connect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 15 ,1 -- Disconnect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 17 ,1 -- Existing Connection

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 16 ,1 -- Attention

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 33 ,1 -- Exception
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 55 ,1 -- Hash Warning
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 67 ,1 -- Execution Warnings
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 69 ,1 -- Sort Warnings
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 79 ,1 -- Missing Column Statistics
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 80 ,1 -- Missing Join Predicate

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 11 ,1 -- RPC:Starting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 13 ,1 -- SQL:BatchStarting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 42 ,1 -- SP:Starting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 43 ,1 -- SP:Completed
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 45 ,1 -- SP:StmtCompleted
  END ELSE IF @TraceType=1 BEGIN -- Generic replay trace
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 21 ,1 -- EventLog
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 22 ,1 -- ErrorLog
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 33 ,1 -- Exception
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 55 ,1 -- Hash Warning
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 61 ,1 -- OLEDB Errors
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 67 ,1 -- Execution Warnings
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 69 ,1 -- Sort Warnings
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 79 ,1 -- Missing Column Statistics
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 80 ,1 -- Missing Join Predicate

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 25 ,1 -- Lock:Deadlock
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 27 ,1 -- Lock:Timeout
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 59 ,1 -- Lock:Deadlock Chain

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 16 ,1 -- Attention
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 58 ,1 -- Auto-UpdateStats
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 81 ,1 -- Server Memory Change
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 18 ,1 -- ServiceControl
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 32 ,1 -- ConnectionBeingKilled

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 14 ,1 -- Connect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 15 ,1 -- Disconnect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 17 ,1 -- Existing Connection

    DBCC TRACEON (8510, -1) -- 8510 is necessary for DTCTransaction to correctly report isolation levels
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 19 ,1 -- DTCTransaction

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 11 ,1 -- RPC:Starting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 10 ,1 -- RPC:Completed
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 13 ,1 -- SQL:BatchStarting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 12 ,1 -- SQL:BatchCompleted
  END ELSE IF @TraceType=2 BEGIN -- Minimal performance trace
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 75 ,1 -- Cursor Recompile

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 21 ,1 -- EventLog
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 22 ,1 -- ErrorLog
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 33 ,1 -- Exception
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 55 ,1 -- Hash Warning
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 61 ,1 -- OLEDB Errors
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 67 ,1 -- Execution Warnings
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 69 ,1 -- Sort Warnings
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 79 ,1 -- Missing Column Statistics
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 80 ,1 -- Missing Join Predicate

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 25 ,1 -- Lock:Deadlock
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 27 ,1 -- Lock:Timeout
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 59 ,1 -- Lock:Deadlock Chain

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 16 ,1 -- Attention
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 58 ,1 -- Auto-UpdateStats
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 81 ,1 -- Server Memory Change
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 18 ,1 -- ServiceControl
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 32 ,1 -- ConnectionBeingKilled

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 14 ,1 -- Connect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 15 ,1 -- Disconnect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 17 ,1 -- Existing Connection

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 37 ,1 -- SP:Recompile

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 10 ,1 -- RPC:Completed
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 12 ,1 -- SQL:BatchCompleted
  END ELSE IF @TraceType=3 BEGIN -- General performance trace
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 75 ,1 -- Cursor Recompile
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 76 ,1 -- Cursor Implicit Conversion

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 21 ,1 -- EventLog
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 22 ,1 -- ErrorLog
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 33 ,1 -- Exception
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 55 ,1 -- Hash Warning
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 61 ,1 -- OLEDB Errors
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 67 ,1 -- Execution Warnings
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 69 ,1 -- Sort Warnings
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 79 ,1 -- Missing Column Statistics
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 80 ,1 -- Missing Join Predicate

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 25 ,1 -- Lock:Deadlock
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 27 ,1 -- Lock:Timeout
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 59 ,1 -- Lock:Deadlock Chain

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 16 ,1 -- Attention
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 58 ,1 -- Auto-UpdateStats
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 81 ,1 -- Server Memory Change
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 18 ,1 -- ServiceControl
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 32 ,1 -- ConnectionBeingKilled

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 14 ,1 -- Connect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 15 ,1 -- Disconnect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 17 ,1 -- Existing Connection

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 34 ,1 -- SP:CacheMiss
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 35 ,1 -- SP:CacheInsert
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 36 ,1 -- SP:CacheRemove
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 37 ,1 -- SP:Recompile
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 38 ,1 -- SP:CacheHit
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 39 ,1 -- SP:ExecContextHit

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 50 ,1 -- SQLTransaction

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 11 ,1 -- RPC:Starting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 10 ,1 -- RPC:Completed
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 13 ,1 -- SQL:BatchStarting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 12 ,1 -- SQL:BatchCompleted
  END ELSE IF @TraceType=4 BEGIN -- Detailed performance trace
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 53 ,1 -- Cursor Open
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 75 ,1 -- Cursor Recompile
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 76 ,1 -- Cursor Implicit Conversion

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 21 ,1 -- EventLog
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 22 ,1 -- ErrorLog
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 33 ,1 -- Exception
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 55 ,1 -- Hash Warning
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 61 ,1 -- OLEDB Errors
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 67 ,1 -- Execution Warnings
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 69 ,1 -- Sort Warnings
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 79 ,1 -- Missing Column Statistics
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 80 ,1 -- Missing Join Predicate

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 25 ,1 -- Lock:Deadlock
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 27 ,1 -- Lock:Timeout
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 59 ,1 -- Lock:Deadlock Chain
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 60 ,1 -- Lock:Escalation

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 16 ,1 -- Attention
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 58 ,1 -- Auto-UpdateStats
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 68 ,1 -- Execution Plan
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 20 ,1 -- LoginFailed
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 81 ,1 -- Server Memory Change
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 18 ,1 -- ServiceControl
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 32 ,1 -- ConnectionBeingKilled

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 14 ,1 -- Connect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 15 ,1 -- Disconnect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 17 ,1 -- Existing Connection

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 34 ,1 -- SP:CacheMiss
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 35 ,1 -- SP:CacheInsert
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 36 ,1 -- SP:CacheRemove
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 37 ,1 -- SP:Recompile
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 38 ,1 -- SP:CacheHit
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 39 ,1 -- SP:ExecContextHit
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 42 ,1 -- SP:Starting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 43 ,1 -- SP:Completed
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 44 ,1 -- SP:StmtStarting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 45 ,1 -- SP:StmtCompleted
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 50 ,1 -- SQLTransaction

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 11 ,1 -- RPC:Starting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 10 ,1 -- RPC:Completed
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 13 ,1 -- SQL:BatchStarting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 12 ,1 -- SQL:BatchCompleted
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 40 ,1 -- SQL:StmtStarting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 41 ,1 -- SQL:StmtCompleted
  END ELSE IF @TraceType=5 BEGIN -- DTC replay trace
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 11 ,1 -- RPC:Starting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 10 ,1 -- RPC:Completed
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 13 ,1 -- SQL:BatchStarting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 12 ,1 -- SQL:BatchCompleted

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 14 ,1 -- Connect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 15 ,1 -- Disconnect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 17 ,1 -- Existing Connection

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 16 ,1 -- Attention

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 50 ,1 -- SQLTransaction
    DBCC TRACEON (8510, -1) -- 8510 is necessary for DTCTransaction to correctly report isolation levels
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 19 ,1 -- DTCTransaction

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 21 ,1 -- EventLog
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 22 ,1 -- ErrorLog
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 33 ,1 -- Exception
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 79 ,1 -- Missing Column Statistics
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 80 ,1 -- Missing Join Predicate
  END ELSE IF @TraceType=6 BEGIN -- Itemized (semi-detailed) performance trace (no execution plan)
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 53 ,1 -- Cursor Open
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 75 ,1 -- Cursor Recompile
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 76 ,1 -- Cursor Implicit Conversion

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 21 ,1 -- EventLog
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 22 ,1 -- ErrorLog
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 33 ,1 -- Exception
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 55 ,1 -- Hash Warning
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 61 ,1 -- OLEDB Errors
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 67 ,1 -- Execution Warnings
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 69 ,1 -- Sort Warnings
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 79 ,1 -- Missing Column Statistics
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 80 ,1 -- Missing Join Predicate

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 25 ,1 -- Lock:Deadlock
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 27 ,1 -- Lock:Timeout
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 59 ,1 -- Lock:Deadlock Chain
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 60 ,1 -- Lock:Escalation

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 16 ,1 -- Attention
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 58 ,1 -- Auto-UpdateStats
--    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 68 ,1 -- Execution Plan
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 20 ,1 -- LoginFailed
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 81 ,1 -- Server Memory Change
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 18 ,1 -- ServiceControl
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 32 ,1 -- ConnectionBeingKilled

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 14 ,1 -- Connect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 15 ,1 -- Disconnect
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 17 ,1 -- Existing Connection

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 34 ,1 -- SP:CacheMiss
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 35 ,1 -- SP:CacheInsert
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 36 ,1 -- SP:CacheRemove
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 37 ,1 -- SP:Recompile
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 38 ,1 -- SP:CacheHit
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 39 ,1 -- SP:ExecContextHit
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 42 ,1 -- SP:Starting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 43 ,1 -- SP:Completed
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 44 ,1 -- SP:StmtStarting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 45 ,1 -- SP:StmtCompleted
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 50 ,1 -- SQLTransaction

    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 11 ,1 -- RPC:Starting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 10 ,1 -- RPC:Completed
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 13 ,1 -- SQL:BatchStarting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 12 ,1 -- SQL:BatchCompleted
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 40 ,1 -- SQL:StmtStarting
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, 41 ,1 -- SQL:StmtCompleted
  END ELSE BEGIN
    RAISERROR('Invalid trace type.',16,10)
    DBCC TRACEOFF(3604)
    RETURN -1
  END
END ELSE BEGIN -- Loop through the @Events string, parsing out each event number and adding it to the queue definition
  IF RIGHT(@Events,1)<>',' SET @Events=@Events+',' -- Append a comma to satisfy the loop
  DECLARE @i int, @Event int
  SET @i=CHARINDEX(',',@Events)
  WHILE @i<>0 BEGIN
    SET @Event=CAST(LEFT(@Events,@i-1) AS int)
    EXEC master..xp_trace_seteventclassrequired @QueueHandle, @Event ,1
    SET @Events=SUBSTRING(@Events,@i+1,300)
    SET @i=CHARINDEX(',',@Events)
  END
END


-- Delete the file if it exists
DECLARE @cmd varchar(8000)
SET @cmd='DEL '+@FileName
EXEC master..xp_cmdshell @cmd

-- Configure the queue to write to a file
PRINT 'The new trace output file name is : '+@FileName+'.'
EXEC master..xp_trace_setqueuedestination @QueueHandle, 2, 1, NULL, @FileName

-- Start the consumer that actually writes to the file
EXEC master..xp_trace_startconsumer @QueueHandle

RETURN 0

Help:
DECLARE @crlf char(2), @tabc char(1)
SET @crlf=char(13)+char(10)
SET @tabc=char(9)
PRINT 'sp_trace -- Starts/stops a Profiler-like trace using Transact-SQL eXtended Procedure calls'
PRINT @crlf+'Parameters:'
PRINT @tabc+'Parameter                  Type         Default                        Purpose'
PRINT @tabc+'-------------------------- ------------ ------------------------------ ------------------------------------------------------------------------------------------'
PRINT @tabc+'@OnOff                     varchar(3)   /?                             Starts/stops the trace (or lists help info)'
PRINT @tabc+'@FileName                  sysname      c:\temp\YYYYMMDDhhmissmmm.trc  Trace file name'
PRINT @tabc+'@TraceName                 sysname      sp_trace                       Specifies the name of the trace'
PRINT @tabc+'@TraceType                 int          0                              Type of trace: 0=Minimal, 1=Generic replay, 2=Minimal performance, 3=General performance, 4=Detailed performance, 5=DTC replay, 6=Itemized (semi-detailed) performance'
PRINT @tabc+'@Events                    varchar(300) SP events, errors & warnings   Comma-delimited list specifying the events numbers (per xp_trace_geteventnames) to trace'
PRINT @tabc+'@Cols                      int          All columns except BinaryData  Bitmask specifying what columns (per xp_trace_addnewqueue) to include in the trace'
PRINT @tabc+'@IncludeTextFilter         sysname      NULL                           Semicolon delimited list specifying what TextData strings to include in the trace'
PRINT @tabc+'@ExcludeTextFilter         sysname      NULL                           Semicolon delimited list specifying what TextData strings to filter out of the trace'
PRINT @tabc+'@IncludeObjIdFilter        sysname      NULL                           Id of an object to target with the trace'
PRINT @tabc+'@IncludeAppFilter          sysname      NULL                           Semicolon delimited list of AppNames to include in the trace'
PRINT @tabc+'@ExcludeAppFilter          sysname      SQLAgent%;SQL Server Profiler% Semicolon delimited list of AppNames to filter out of the trace'
PRINT @tabc+'@IncludeConnectionIdFilter int          NULL                           Id of a connection to target with the trace'
PRINT @tabc+'@MinCPUFilter              int          0                              Minimum CPU time (in ms) to include for the events specified'
PRINT @tabc+'@MaxCPUFilter              int          0                              Maximum CPU time (in ms) to include for the events specified'
PRINT @tabc+'@IncludeDBIdFilter         int          NULL                           Id of the database to target with the trace'
PRINT @tabc+'@MinDurationFilter         int          0                              Minimum event duration (in ms) to capture for the events specified'
PRINT @tabc+'@MaxDurationFilter         int          0                              Maximum event duration (in ms) to capture for the events specified'
PRINT @tabc+'@IncludeNTNameFilter       sysname      NULL,                          Semicolon delimited list of NT user names to include in the trace'
PRINT @tabc+'@ExcludeNTNameFilter       sysname      NULL                           Semicolon delimited list of NT user names to filter out of the trace'
PRINT @tabc+'@MinReadFilter             int          0                              Minimum number of logical read operations to capture for the events specified'
PRINT @tabc+'@MaxReadFilter             int          0                              Maximum number of logical read operations to capture for the events specified'
PRINT @tabc+'@IncludeServerFilter       sysname      NULL                           Semicolon delimited list of server names to include in the trace'
PRINT @tabc+'@ExcludeServerFilter       sysname      NULL                           Semicolon delimited list of server names to filter out of the trace'
PRINT @tabc+'@IncludeSPIdFilter         int          NULL                           Id of the user process to target with the trace'
PRINT @tabc+'@IncludeUserFilter         sysname      NULL                           Semicolon delimited list of user names to include in the trace'
PRINT @tabc+'@ExcludeUserFilter         sysname      NULL                           Semicolon delimited list of user names to exclude from the trace'
PRINT @tabc+'@MinWriteFilter            int          0                              Minimum number of logical write operations to capture for the events specified'
PRINT @tabc+'@MaxWriteFilter            int          0                              Maximum number of logical write operations to capture for the events specified'
PRINT @tabc+'@MinSeverityFilter         int          0                              Minimum event severity level to capture for the events specified'
PRINT @tabc+'@MaxSeverityFilter         int          0                              Maximum event severity level to capture for the events specified'
PRINT @tabc+'@IncludeHostFilter         sysname      NULL                           Semicolon delimited list of host names to include in the trace'
PRINT @tabc+'@ExcludeHostFilter         sysname      NULL                           Semicolon delimited list of host names to exclude from the trace'
PRINT @tabc+'@IncludeHpIdFilter         int          NULL                           Id of the host process to target with the trace'
PRINT @tabc+'@IncludeIndIdFilter        int          NULL                           Id of the index to target with the trace'
PRINT @tabc+'@IncludeNTDomainFilter     sysname      NULL                           Semicolon delimited list of NT domain names to include in the trace'
PRINT @tabc+'@ExcludeNTDomainFilter     sysname      NULL                           Semicolon delimited list of NT domain names to exclude from the trace'
PRINT @tabc+'@IncludeSysobjectsFilter   int          NULL                           Integer specifying whether to include system objects in the trace (1=Yes, 0=No)'
PRINT @crlf+'Supported events:'
EXEC master..xp_trace_geteventnames
PRINT @crlf+'Examples: '
PRINT @crlf+@tabc+'EXEC sp_trace'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'''
PRINT @crlf+@tabc+'EXEC sp_trace ''OFF'''
PRINT @crlf+@tabc+'EXEC sp_trace @OnOff=''ON'', @filename=''d:\mssql7\log\mytrace.trc'''
PRINT @crlf+@tabc+'EXEC sp_trace @OnOff=''ON'', @Events=''37,43'''
PRINT @crlf+@tabc+'DECLARE @ColsToGet int'
PRINT @tabc+'SET @ColsToGet=4 | 4096 | 2097152'
PRINT @tabc+'EXEC sp_trace @OnOff=''ON'', @Cols=@ColsToGet'
PRINT @crlf+@tabc+'EXEC sp_trace @OnOff=''ON'', @IncludeTextFilter=''EXEC FooProc%'''
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'',@tracename=''General Performance'''
PRINT @crlf+@tabc+'EXEC sp_trace ''OFF'',@tracename=''General Performance'''
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'',@filename = ''d:\mssql7\log\mytrace'','
PRINT @tabc+@tabc+'@TraceName = ''General Performance'','
PRINT @tabc+@tabc+'@TraceType = 0,'
PRINT @tabc+@tabc+'@Events = ''10,11,14,15,16,17,27,37,40,41,55'','
PRINT @tabc+@tabc+'@Cols = DEFAULT,'
PRINT @tabc+@tabc+'@IncludeTextFilter = NULL,'
PRINT @tabc+@tabc+'@IncludeObjIdFilter = NULL'
RETURN 0
GO

IF (CHARINDEX('7.00.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_trace07','P') IS NULL) 
	RAISERROR('Error creating sp_trace07',16,127)
GO



IF OBJECT_ID('dbo.sp_trace08','P') IS NOT NULL
  DROP PROC dbo.sp_trace08
GO
PRINT ''
RAISERROR ('===== Creating sp_trace08', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_trace08 @OnOff varchar(4)='/?',
@FileName sysname=NULL,
@TraceName sysname='tsqltrace',
@Options int=2,
@MaxFileSize bigint=4000,
@StopTime datetime=NULL,
@TraceType int=0,
@Events varchar(300)=
--  11 - RPC:Starting
--  13 - SQL:BatchStarting
--  14 - Connect
--  15 - Disconnect
--  16 - Attention
--  17 - Existing Connection
--  33 - Exception
--  42 - SP:Starting
--  43 - SP:Completed
--  45 - SP:StmtCompleted
--  55 - Hash Warning
--  67 - Execution Warnings
--  69 - Sort Warnings
--  79 - Missing Column Statistics
--  80 - Missing Join Predicate
'11,13,14,15,16,17,33,42,43,45,55,67,69,79,80',
@Cols varchar(300)=
-- All columns
'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,',
@IncludeTextFilter sysname=NULL, @ExcludeTextFilter sysname=NULL,
@IncludeObjIdFilter int=NULL, @ExcludeObjIdFilter int=NULL,
@IncludeObjNameFilter sysname=NULL, @ExcludeObjNameFilter sysname=NULL,
@IncludeHostFilter sysname=NULL, @ExcludeHostFilter sysname=NULL, 
@IncludeSpidFilter int=NULL, @ExcludeSpidFilter int=NULL,  
@TraceId int = NULL,
@AppName sysname='PSSDIAG'
AS
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
SET NOCOUNT ON

IF @OnOff='/?' GOTO Help

SET @OnOff=UPPER(@OnOff)
IF (@OnOff='LIST') BEGIN
	IF (OBJECT_ID('tempdb..PSSDIAGTraceQueue') IS NOT NULL) BEGIN
		IF (@TraceId IS NULL) BEGIN
			DECLARE tc CURSOR FOR SELECT * FROM tempdb..PSSDIAGTraceQueue WHERE AppName=@AppName FOR READ ONLY
			DECLARE @tid int, @tname varchar(20), @tfile sysname
			OPEN tc
			FETCH tc INTO @tid, @tname, @tfile
			IF @@ROWCOUNT<>0 BEGIN
						WHILE @@FETCH_STATUS=0 BEGIN
	   						SELECT TraceId, TraceName, TraceFile 
	   						FROM tempdb..PSSDIAGTraceQueue WHERE TraceId=@tid
	   						
							SELECT * FROM ::fn_trace_getinfo(@tid)		
							
		  					FETCH tc INTO @tid, @tname, @tfile
						END
			END ELSE PRINT 'No traces in the trace queue.'
			CLOSE tc
			DEALLOCATE tc
		END ELSE BEGIN
			SELECT TraceId, TraceName, TraceFile 
			FROM tempdb..PSSDIAGTraceQueue WHERE TraceId=@TraceId
			SELECT * FROM ::fn_trace_getinfo(@TraceId)		
	  END
	END ELSE PRINT 'No traces to list.'
  RETURN 0
END

-- Declare variables
DECLARE @OldQueueHandle int -- Queue handle of currently running trace queue
DECLARE @QueueHandle int -- Queue handle for new running trace queue
DECLARE @On bit  -- Necessary because of a bug in some of the sp_trace_xx procs
DECLARE @OurObjId int -- Used to keep us out of the trace log
DECLARE @OldTraceFile sysname -- File name of running trace
DECLARE @res int -- Result var for sp calls
SET @On=1

-- Stop the trace if running
IF OBJECT_ID('tempdb..PSSDIAGTraceQueue') IS NOT NULL BEGIN
  IF EXISTS(SELECT * FROM tempdb..PSSDIAGTraceQueue WHERE TraceName = @TraceName AND AppName=@AppName) BEGIN

    SELECT @OldQueueHandle = TraceId, @OldTraceFile=TraceFile
    FROM tempdb..PSSDIAGTraceQueue
    WHERE TraceName = @TraceName
    AND AppName=@AppName

    IF @@ROWCOUNT<>0 BEGIN
      EXEC sp_trace_setstatus @TraceId=@OldQueueHandle, @status=0
      EXEC sp_trace_setstatus @TraceId=@OldQueueHandle, @status=2
      PRINT 'Deleted trace queue ' + CAST(@OldQueueHandle AS varchar(20))+'.'
      PRINT 'The trace output file name is: '+@OldTraceFile+'.trc.'
      DELETE tempdb..PSSDIAGTraceQueue WHERE TraceName = @TraceName AND AppName=@AppName
    END
  END ELSE PRINT 'No active traces named '+@TraceName+' for '+@AppName+'.'
END ELSE PRINT 'No active traces.'

IF @OnOff='OFF' RETURN 0 -- We've stopped the trace (if it's running), so exit

-- Do some basic param validation
IF (@Cols IS NULL) BEGIN
  RAISERROR('You must specify the columns to trace.',16,10)
  RETURN -1
END

IF ((@TraceType=0) AND (@Events IS NULL)) BEGIN
  RAISERROR('You must specify either @TraceType or @Events.',16,10)
  RETURN -1
END

-- Append the datetime to the file name to create a new, unique file name.
IF @FileName IS NULL SELECT @FileName = 'c:\TEMP\tsqltrace_' + CONVERT(CHAR(8),getdate(),112) + REPLACE(CONVERT(varchar(15),getdate(),114),':','')

-- Delete the file if it exists
DECLARE @cmd varchar(8000)
SET @cmd='DEL '+@FileName+'.trc'
SET @cmd=REPLACE(@cmd,'sp_trace','*')  -- Delete all trace files so that SQL Server's rollover functionality works
EXEC master..xp_cmdshell @cmd

-- Check for proper permissions
-- This check is only valid if xp_cmdshell and SQL Server are running under the same account

-- First try accessing the output folder 
DECLARE @outputfolder varchar(255)

SET @outputfolder=@FileName

-- Remove the filename portion of the full file path
SET @outputfolder=REVERSE(@outputfolder)
SET @outputfolder=SUBSTRING(@outputfolder,CHARINDEX('\',@outputfolder)+1,255)
SET @outputfolder=REVERSE(@outputfolder)

PRINT 'Output folder='+@outputfolder

/*

CREATE TABLE #dirlist (linein varchar(8000))

SET @cmd='DIR '+@outputfolder

INSERT #dirlist
EXEC @res=master..xp_cmdshell @cmd

IF (1=@res) BEGIN
	RAISERROR('Warning:  Error accessing output folder %s.  Reason:  The path does not exist or SQL Server lacks sufficient rights to access it',10,10,@outputfolder)
	RETURN @res
END ELSE BEGIN

	-- Check the amount of free space
	DECLARE @spaceline varchar(255)
	SELECT @spaceline=linein
	FROM #dirlist
	WHERE linein LIKE '% bytes free%'
	
	IF @spaceline IS NOT NULL BEGIN
	
		SET @spaceline=LEFT(@spaceline,CHARINDEX(' bytes free',@spaceline))
		
		SET @spaceline=SUBSTRING(@spaceline,CHARINDEX('Dir(s)   ',@spaceline)+LEN('Dir(s)   '),255)
		
		PRINT 'Free space='+@spaceline+' bytes'
		
		DECLARE @freespace int
		SET @freespace=CAST(REPLACE(@spaceline,',','') AS bigint) / (1024*1024)
		
		-- If free space < 64MB, issue a warning
		IF (@freespace < 64) BEGIN
			RAISERROR('Warning:  The target drive has only %d MB of free space.  Your trace may not complete successfully.',10,10, @freespace)
		END
		
	END

	-- Now try creating a file in the output folder
	SET @cmd='ECHO test>'+@outputfolder+'\_ms_pss_tst.txt'
	EXEC @res=master..xp_cmdshell @cmd
	IF (1=@res) BEGIN
		RAISERROR('Warning:  Error creating file.  Reason:  The drive is out of space or SQL Server lacks sufficient rights to write files in %s',10,10,@outputfolder)
	END ELSE BEGIN
		-- File created; delete it
		SET @cmd='DEL '+@outputfolder+'\_ms_pss_tst.txt'
		EXEC master..xp_cmdshell @cmd, no_output
	END
END

DROP TABLE #dirlist

-- End permissions check
*/


-- Create the trace queue
--if MaxFileSize is not more than 0, sql server runtime will report invalid parameter error. In this case, set MaxFileSize default value based on BOL
IF @MaxFileSize<=0 SET @MaxFileSize=5

EXEC @res=sp_trace_create @TraceId=@QueueHandle OUT, @options=@Options, @tracefile=@FileName, @maxfilesize=@MaxFileSize, @stoptime=@StopTime
IF @res<>0 BEGIN
  IF @res=1 RAISERROR('Trace not started.  Reason: Unknown error.',16,10)
  ELSE IF @res=10 RAISERROR('Trace not started.  Reason: Invalid options. Returned when options specified are incompatible.',16,10)
  ELSE IF @res=12 RAISERROR('Trace not started.  Reason: Error creating file. Returned if the file already exists, drive is out of space, the path %s does not exist, or SQL Server does not have sufficent rights to write files in it.',16,10, @outputfolder)
  ELSE IF @res=13 RAISERROR('Trace not started.  Reason: Out of memory. Returned when there is not enough memory to perform the specified action.',16,10)
  ELSE IF @res=14 RAISERROR('Trace not started.  Reason: Invalid stop time. Returned when the stop time specified has already happened.',16,10)
  ELSE IF @res=15 RAISERROR('Trace not started.  Reason: Invalid parameters. Returned when the user supplied incompatible parameters.',16,10)
  RETURN @res
END 
PRINT 'Trace started.'
PRINT 'The trace file name is : '+@FileName+'.'

-- 2004/03/25 - BD: removed Errorlog event (22) from templates to avoid shiloh bug 471314. 
-- Also note that the "Missing Column Stats" event (79) was previously removed to avoid bug 356136. 
IF @TraceType=1 SET @Events='75,76,92,93,94,95,16,21,33,67,69,55,79,80,61,25,27,59,58,14,15,81,17,10,11,34,35,36,37,38,39,50,11,12'
ELSE IF @TraceType=2 SET @Events='53,75,76,60,92,93,94,95,16,21,28,29,30,31,33,67,69,55,79,80,61,25,27,59,58,14,15,81,17,10,11,34,35,36,37,38,39,50,11,12,97,98,18,100,41,19,'

-- Specify the event classes and columns to trace
IF @Events IS NOT NULL BEGIN -- Loop through the @Events and @Cols strings, parsing out each event & column number and adding them to the trace definition
  IF RIGHT(@Events,1)<>',' SET @Events=@Events+',' -- Append a comma to satisfy the loop
  IF RIGHT(@Cols,1)<>',' SET @Cols=@Cols+',' -- Append a comma to satisfy the loop
  DECLARE @i int, @j int, @Event int, @Col int, @ColStr varchar(300)
  SET @i=CHARINDEX(',',@Events)
  WHILE @i<>0 BEGIN
    SET @Event=CAST(LEFT(@Events,@i-1) AS int)
    SET @ColStr=@Cols
    SET @j=CHARINDEX(',',@ColStr)
    WHILE @j<>0 BEGIN
      SET @Col=CAST(LEFT(@ColStr,@j-1) AS int)
      EXEC sp_trace_setevent @TraceId=@QueueHandle, @eventid=@Event, @columnid=@Col, @on=@On
      SET @ColStr=SUBSTRING(@ColStr,@j+1,300)
      SET @j=CHARINDEX(',',@ColStr)
    END
    SET @Events=SUBSTRING(@Events,@i+1,300)
    SET @i=CHARINDEX(',',@Events)
  END
END

-- Set filters (default values avoid tracing the trace activity itself)
-- You can specify other filters like application name etc. by supplying strings to the @IncludeTextFilter/@ExcludeTextFilter parameters, separated by semicolons
--SET @ExcludeTextFilter='sp_trace%'+ISNULL(';'+@ExcludeTextFilter,'')  -- By default, keep our own activity from showing up
SET @OurObjId=OBJECT_ID('master..sp_trace')
-- EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=1, @logical_operator=0, @comparison_operator=7, @value=N'EXEC% sp_trace%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter


IF @IncludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=1, @logical_operator=0, @comparison_operator=6, @value=@IncludeTextFilter
IF @IncludeObjIdFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=22, @logical_operator=0, @comparison_operator=0, @value=@IncludeObjIdFilter
-- EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=22, @logical_operator=0, @comparison_operator=1, @value=@OurObjId
IF @ExcludeObjIdFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=22, @logical_operator=0, @comparison_operator=1, @value=@ExcludeObjIdFilter

IF @IncludeObjNameFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=34, @logical_operator=0, @comparison_operator=6, @value=@IncludeObjNameFilter
IF @ExcludeObjNameFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=34, @logical_operator=0, @comparison_operator=7, @value=@ExcludeObjNameFilter

IF @IncludeHostFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=8, @logical_operator=0, @comparison_operator=6, @value=@IncludeHostFilter
IF @ExcludeHostFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=8, @logical_operator=0, @comparison_operator=7, @value=@ExcludeHostFilter

IF @IncludeSpidFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=12, @logical_operator=0, @comparison_operator=0, @value=@IncludeSpidFilter
IF @ExcludeSpidFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=12, @logical_operator=0, @comparison_operator=1, @value=@ExcludeSpidFilter

-- Turn the trace on
EXEC sp_trace_setstatus @TraceId=@QueueHandle, @status=1

-- Record the trace queue handle for subsequent jobs.  (This allows us to know how to stop our trace.)
IF OBJECT_ID('tempdb..PSSDIAGTraceQueue') IS NULL BEGIN
  CREATE TABLE tempdb..PSSDIAGTraceQueue (TraceId int, TraceName varchar(20), TraceFile sysname, AppName sysname)
  INSERT tempdb..PSSDIAGTraceQueue VALUES(@QueueHandle, @TraceName, @FileName, @AppName)
END ELSE BEGIN
  IF EXISTS(SELECT * FROM tempdb..PSSDIAGTraceQueue WHERE TraceName = @TraceName AND AppName=@AppName) BEGIN
    UPDATE tempdb..PSSDIAGTraceQueue SET TraceId = @QueueHandle, TraceFile=@FileName WHERE TraceName = @TraceName AND AppName=@AppName
  END ELSE BEGIN
    INSERT tempdb..PSSDIAGTraceQueue VALUES(@QueueHandle, @TraceName, @FileName, @AppName)
  END
END
RETURN 0

Help:
PRINT 'sp_trace -- Starts/stops a Profiler-like trace using Transact-SQL eXtended Procedure calls.'
DECLARE @crlf char(2), @tabc char(1)
SET @crlf=char(13)+char(10)
SET @tabc=char(9)
PRINT @crlf+'Parameters:'
PRINT @crlf+@tabc+'@OnOff              varchar(3)   default: ON -- Starts/stops the trace'
PRINT @crlf+@tabc+'@FileName           sysname      default: c:\temp\YYYYMMDDhhmissmmm.trc -- Specifies the trace file name (SQL Server always appends .trc extension)'
PRINT @crlf+@tabc+'@TraceName          sysname      default: tsqltrace -- Specifies the name of the trace'
PRINT @crlf+@tabc+'@TraceType          int          default: 0 -- Specifies the type of trace to run 1=General performance, 2=Detailed performance'
PRINT @crlf+@tabc+'@Options            int          default: 2 (TRACE_FILE_ROLLOVER)'
PRINT @crlf+@tabc+'@MaxFileSize        bigint       default: 2000000000 (MB)'
PRINT @crlf+@tabc+'@StopTime           datetime     default: NULL'
PRINT @crlf+@tabc+'@Events             varchar(300) default: SP-related events and errors/warnings -- Comma-delimited list specifying the events numbers to trace'
PRINT @crlf+@tabc+'@Cols               varchar(300) default: All columns -- Comma-delimited list specifying the column numbers to trace'
PRINT @crlf+@tabc+'@IncludeTextFilter  sysname      default: NULL -- String mask specifying what TextData strings to include in the trace'
PRINT @crlf+@tabc+'@ExcludeTextFilter  sysname      default: NULL -- String mask specifying what TextData strings to filter out of the trace'
PRINT @crlf+@tabc+'@IncludeObjIdFilter sysname      default: NULL -- Specifies the id of an object to target with the trace'
PRINT @crlf+@tabc+'@ExcludeObjIdFilter sysname      default: NULL -- Specifies the id of an object to exclude from the trace'
PRINT @crlf+@tabc+'@TraceId            int          default: NULL -- Specified the id of the trace to list when you specify the LIST option to @OnOff'
PRINT @crlf+@tabc+'@AppName            sysname      default: PSSDIAG -- Specifies the name of the calling application'
PRINT @crlf+'Examples: '
PRINT @crlf+@tabc+'EXEC sp_trace -- Displays this help text'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'' -- Starts a trace'
PRINT @crlf+@tabc+'EXEC sp_trace ''OFF'' -- Stops a trace'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'', @Filename=''d:\mssql7\log\mytrace'' -- Starts a trace with the specified file name'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'',@Events=''37,43'' -- Starts a trace the traps the specified event classes'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'',@Cols=''1,2,3'' -- Starts a trace that includes the specified columns'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'',@IncludeTextFilter=''EXEC% FooProc%'' -- Starts a trace that includes events matching the specified TextData mask'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'',@tracename=''General Performance'' -- Starts a trace using the specified name'
PRINT @crlf+@tabc+'EXEC sp_trace ''OFF'',@tracename=''General Performance'' -- Stops a trace with the specified name'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'',@filename = ''d:\mssql7\log\mytrace'', -- Starts a trace with the specified parameters' 
PRINT @tabc+@tabc+'@TraceName = ''General Performance'','
PRINT @tabc+@tabc+'@Options = 2, '
PRINT @tabc+@tabc+'@TraceType = 0,'
PRINT @tabc+@tabc+'@MaxFileSize = 500,'
PRINT @tabc+@tabc+'@StopTime = NULL, '
PRINT @tabc+@tabc+'@Events = ''10,11,14,15,16,17,27,37,40,41,55,58,67,69,79,80,98'','
PRINT @tabc+@tabc+'@Cols = DEFAULT,'
PRINT @tabc+@tabc+'@IncludeTextFilter = NULL,'
PRINT @tabc+@tabc+'@IncludeObjIdFilter = NULL,'
PRINT @tabc+@tabc+'@ExcludeObjIdFilter = NULL'
PRINT @crlf+@tabc+'To list all the traces currently running:'
PRINT @crlf+@tabc+@tabc+'sp_trace ''LIST'''
PRINT @crlf+@tabc+'To list information about a particular trace:'
PRINT @crlf+@tabc+@tabc+'sp_trace ''LIST'', @TraceId=n -- where n is the trace ID you want to list'
PRINT @crlf+@tabc+'To stop a specific trace, supply the @TraceName parameter when you call sp_trace ''OFF''.'
RETURN 0
GO
IF (CHARINDEX('8.00.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_trace08','P') IS NULL) 
	RAISERROR('Error creating sp_trace08',16,127)
GO


/*
IF OBJECT_ID('tempdb.dbo.SQLDIAGTraceQueue') IS NOT NULL
  DROP TABLE tempdb.dbo.SQLDIAGTraceQueue
--GO
*/

IF OBJECT_ID('dbo.sp_trace09','P') IS NOT NULL
  DROP PROC dbo.sp_trace09
GO
PRINT ''
RAISERROR ('===== Creating sp_trace09', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_trace09 @OnOff varchar(4)='/?',
@FileName sysname=NULL,
@TraceName sysname='tsqltrace',
@Options int=2,
@MaxFileSize bigint=4000,
@StopTime datetime=NULL,
@FileCount int=NULL,
@TraceType int=0,
@Events varchar(300)=
--  11 - RPC:Starting
--  13 - SQL:BatchStarting
--  14 - Connect
--  15 - Disconnect
--  16 - Attention
--  17 - Existing Connection
--  33 - Exception
--  42 - SP:Starting
--  43 - SP:Completed
--  45 - SP:StmtCompleted
--  55 - Hash Warning
--  67 - Execution Warnings
--  69 - Sort Warnings
--  79 - Missing Column Statistics
--  80 - Missing Join Predicate
'11,13,14,15,16,17,33,42,43,45,55,67,69,79,80',
@Cols varchar(300)=
-- All columns
'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,',
@IncludeTextFilter sysname=NULL, @ExcludeTextFilter sysname=NULL,
@IncludeObjIdFilter int=NULL, @ExcludeObjIdFilter int=NULL,
@IncludeObjNameFilter sysname=NULL, @ExcludeObjNameFilter sysname=NULL,
@IncludeHostFilter sysname=NULL, @ExcludeHostFilter sysname=NULL, 
@IncludeSpidFilter int=NULL, @ExcludeSpidFilter int=NULL,  
@TraceId int = NULL,
@AppName sysname='SQLDIAG'
AS
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
SET NOCOUNT ON

IF @OnOff='/?' GOTO Help

SET @OnOff=UPPER(@OnOff)
IF (@OnOff='LIST') BEGIN
	IF (OBJECT_ID('tempdb..SQLDIAGTraceQueue') IS NOT NULL) BEGIN
		IF (@TraceId IS NULL) BEGIN
			DECLARE tc CURSOR FOR SELECT * FROM tempdb..SQLDIAGTraceQueue WHERE AppName=@AppName FOR READ ONLY
			DECLARE @tid int, @tname varchar(20), @tfile sysname
			OPEN tc
			FETCH tc INTO @tid, @tname, @tfile
			IF @@ROWCOUNT<>0 BEGIN
						WHILE @@FETCH_STATUS=0 BEGIN
	   						SELECT TraceId, TraceName, TraceFile 
	   						FROM tempdb..SQLDIAGTraceQueue WHERE TraceId=@tid
	   						
							SELECT * FROM ::fn_trace_getinfo(@tid)		
							
		  					FETCH tc INTO @tid, @tname, @tfile
						END
			END ELSE PRINT 'No traces in the trace queue.'
			CLOSE tc
			DEALLOCATE tc
		END ELSE BEGIN
			SELECT TraceId, TraceName, TraceFile 
			FROM tempdb..SQLDIAGTraceQueue WHERE TraceId=@TraceId
			SELECT * FROM ::fn_trace_getinfo(@TraceId)		
	  END
	END ELSE PRINT 'No traces to list.'
  RETURN 0
END

-- Declare variables
DECLARE @OldQueueHandle int -- Queue handle of currently running trace queue
DECLARE @QueueHandle int -- Queue handle for new running trace queue
DECLARE @On bit, @Off bit  -- Necessary because of a bug in some of the sp_trace_xx procs
DECLARE @OurObjId int -- Used to keep us out of the trace log
DECLARE @OldTraceFile sysname -- File name of running trace
DECLARE @res int -- Result var for sp calls
SET @On=1
SET @Off=0

-- Stop the trace if running
IF OBJECT_ID('tempdb..SQLDIAGTraceQueue') IS NOT NULL BEGIN
  IF EXISTS(SELECT * FROM tempdb..SQLDIAGTraceQueue WHERE TraceName = @TraceName AND AppName=@AppName) BEGIN

    SELECT @OldQueueHandle = TraceId, @OldTraceFile=TraceFile
    FROM tempdb..SQLDIAGTraceQueue
    WHERE TraceName = @TraceName
    AND AppName=@AppName

    IF @@ROWCOUNT<>0 BEGIN
      EXEC sp_trace_setstatus @TraceId=@OldQueueHandle, @status=0
      EXEC sp_trace_setstatus @TraceId=@OldQueueHandle, @status=2
      PRINT 'Deleted trace queue ' + CAST(@OldQueueHandle AS varchar(20))+'.'
      PRINT 'The trace output file name is: '+@OldTraceFile+'.trc.'
      DELETE tempdb..SQLDIAGTraceQueue WHERE TraceName = @TraceName AND AppName=@AppName
    END
  END ELSE PRINT 'No active traces named '+@TraceName+' for '+@AppName+'.'
END ELSE PRINT 'No active traces.'

IF @OnOff='OFF' RETURN 0 -- We've stopped the trace (if it's running), so exit

-- Do some basic param validation
IF (@Cols IS NULL) BEGIN
  RAISERROR('You must specify the columns to trace.',16,10)
  RETURN -1
END

IF ((@TraceType=0) AND (@Events IS NULL)) BEGIN
  RAISERROR('You must specify either @TraceType or @Events.',16,10)
  RETURN -1
END

-- Append the datetime to the file name to create a new, unique file name.
IF @FileName IS NULL SELECT @FileName = 'c:\TEMP\tsqltrace_' + CONVERT(CHAR(8),getdate(),112) + REPLACE(CONVERT(varchar(15),getdate(),114),':','')

-- Delete the file if it exists

-- Ensure xp_cmdshell is enabled before we try this
if (select value from sys.sysconfigures WHERE config=16390)=1 BEGIN
	DECLARE @cmd varchar(8000)
	SET @cmd='DEL '+@FileName+'.trc'
	SET @cmd=REPLACE(@cmd,'sp_trace','*')  -- Delete all trace files so that SQL Server's rollover functionality works
	EXEC master..xp_cmdshell @cmd
END

-- First try accessing the output folder 
DECLARE @outputfolder varchar(255)

SET @outputfolder=@FileName

-- Remove the filename portion of the full file path
SET @outputfolder=REVERSE(@outputfolder)
SET @outputfolder=SUBSTRING(@outputfolder,CHARINDEX('\',@outputfolder)+1,255)
SET @outputfolder=REVERSE(@outputfolder)

PRINT 'Output folder='+@outputfolder

-- Create the trace queue

-- found if @filecount parameter is set 1, sql server runtime will report invalid parameter error. only the parameter value is above 1, it works
IF @FileCount<=1 SET @FileCount=NULL

--if MaxFileSize is not more than 0, sql server runtime will report invalid parameter error. In this case, set MaxFileSize default value based on BOL
IF @MaxFileSize<=0 SET @MaxFileSize=5

EXEC @res=sp_trace_create @TraceId=@QueueHandle OUT, @options=@Options, @tracefile=@FileName, @maxfilesize=@MaxFileSize, @stoptime=@StopTime, @filecount=@FileCount
IF @res<>0 BEGIN
  IF @res=1 RAISERROR('Trace not started.  Reason: Unknown error.',16,10)
  ELSE IF @res=10 RAISERROR('Trace not started.  Reason: Invalid options. Returned when options specified are incompatible.',16,10)
  ELSE IF @res=12 RAISERROR('Trace not started.  Reason: Error creating file. Returned if the file already exists, drive is out of space, the path %s does not exist, or SQL Server does not have sufficent rights to write files in it.',16,10, @outputfolder)
  ELSE IF @res=13 RAISERROR('Trace not started.  Reason: Out of memory. Returned when there is not enough memory to perform the specified action.',16,10)
  ELSE IF @res=14 RAISERROR('Trace not started.  Reason: Invalid stop time. Returned when the stop time specified has already happened.',16,10)
  ELSE IF @res=15 RAISERROR('Trace not started.  Reason: Invalid parameters. Returned when the user supplied incompatible parameters.',16,10)
  RETURN @res
END 
PRINT 'Trace started.'
PRINT 'The trace file name is : '+@FileName+'.'

-- 2004/03/25 - BD: removed Errorlog event (22) from templates to avoid shiloh bug 471314. 
-- Also note that the "Missing Column Stats" event (79) was previously removed to avoid bug 356136. 
IF @TraceType=1 SET @Events='75,76,92,93,94,95,16,21,33,67,69,55,79,80,61,25,27,59,58,14,15,81,17,10,11,34,35,36,37,38,39,50,11,12'
ELSE IF @TraceType=2 SET @Events='53,75,76,60,92,93,94,95,16,21,28,29,30,31,33,67,69,55,79,80,61,25,27,59,58,14,15,81,17,10,11,34,35,36,37,38,39,50,11,12,97,98,18,100,41,19,'

-- Specify the event classes and columns to trace
IF @Events IS NOT NULL BEGIN -- Loop through the @Events and @Cols strings, parsing out each event & column number and adding them to the trace definition
  IF RIGHT(@Events,1)<>',' SET @Events=@Events+',' -- Append a comma to satisfy the loop
  IF RIGHT(@Cols,1)<>',' SET @Cols=@Cols+',' -- Append a comma to satisfy the loop
  DECLARE @i int, @j int, @Event int, @Col int, @ColStr varchar(300)
  SET @i=CHARINDEX(',',@Events)
  WHILE @i<>0 BEGIN
    SET @Event=CAST(LEFT(@Events,@i-1) AS int)
    SET @ColStr=@Cols
    SET @j=CHARINDEX(',',@ColStr)
    WHILE @j<>0 BEGIN
      SET @Col=CAST(LEFT(@ColStr,@j-1) AS int)
	  if (@Col<>1) OR (not (@Event IN (10,11)))
		EXEC sp_trace_setevent @TraceId=@QueueHandle, @eventid=@Event, @columnid=@Col, @on=@On
	  else
		EXEC sp_trace_setevent @TraceId=@QueueHandle, @eventid=@Event, @columnid=@Col, @on=@Off
      SET @ColStr=SUBSTRING(@ColStr,@j+1,300)
      SET @j=CHARINDEX(',',@ColStr)
    END
    SET @Events=SUBSTRING(@Events,@i+1,300)
    SET @i=CHARINDEX(',',@Events)
  END
END

-- Set filters (default values avoid tracing the trace activity itself)
-- You can specify other filters like application name etc. by supplying strings to the @IncludeTextFilter/@ExcludeTextFilter parameters, separated by semicolons
--SET @ExcludeTextFilter='sp_trace%'+ISNULL(';'+@ExcludeTextFilter,'')  -- By default, keep our own activity from showing up
SET @OurObjId=OBJECT_ID('master..sp_trace')
-- EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=1, @logical_operator=0, @comparison_operator=7, @value=N'EXEC% sp_trace%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter


IF @IncludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=1, @logical_operator=0, @comparison_operator=6, @value=@IncludeTextFilter
IF @IncludeObjIdFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=22, @logical_operator=0, @comparison_operator=0, @value=@IncludeObjIdFilter
-- EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=22, @logical_operator=0, @comparison_operator=1, @value=@OurObjId
IF @ExcludeObjIdFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=22, @logical_operator=0, @comparison_operator=1, @value=@ExcludeObjIdFilter

IF @IncludeObjNameFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=34, @logical_operator=0, @comparison_operator=6, @value=@IncludeObjNameFilter
IF @ExcludeObjNameFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=34, @logical_operator=0, @comparison_operator=7, @value=@ExcludeObjNameFilter

IF @IncludeHostFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=8, @logical_operator=0, @comparison_operator=6, @value=@IncludeHostFilter
IF @ExcludeHostFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=8, @logical_operator=0, @comparison_operator=7, @value=@ExcludeHostFilter

IF @IncludeSpidFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=12, @logical_operator=0, @comparison_operator=0, @value=@IncludeSpidFilter
IF @ExcludeSpidFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=12, @logical_operator=0, @comparison_operator=1, @value=@ExcludeSpidFilter

-- Turn the trace on
EXEC sp_trace_setstatus @TraceId=@QueueHandle, @status=1

-- Record the trace queue handle for subsequent jobs.  (This allows us to know how to stop our trace.)
IF OBJECT_ID('tempdb..SQLDIAGTraceQueue') IS NULL BEGIN
  CREATE TABLE tempdb..SQLDIAGTraceQueue (TraceId int, TraceName varchar(20), TraceFile sysname, AppName sysname)
  INSERT tempdb..SQLDIAGTraceQueue VALUES(@QueueHandle, @TraceName, @FileName, @AppName)
END ELSE BEGIN
  IF EXISTS(SELECT * FROM tempdb..SQLDIAGTraceQueue WHERE TraceName = @TraceName AND AppName=@AppName) BEGIN
    UPDATE tempdb..SQLDIAGTraceQueue SET TraceId = @QueueHandle, TraceFile=@FileName WHERE TraceName = @TraceName AND AppName=@AppName
  END ELSE BEGIN
    INSERT tempdb..SQLDIAGTraceQueue VALUES(@QueueHandle, @TraceName, @FileName, @AppName)
  END
END
RETURN 0

Help:
PRINT 'sp_trace -- Starts/stops a Profiler-like trace using Transact-SQL eXtended Procedure calls.'
DECLARE @crlf char(2), @tabc char(1)
SET @crlf=char(13)+char(10)
SET @tabc=char(9)
PRINT @crlf+'Parameters:'
PRINT @crlf+@tabc+'@OnOff              varchar(3)   default: ON -- Starts/stops the trace'
PRINT @crlf+@tabc+'@FileName           sysname      default: c:\temp\YYYYMMDDhhmissmmm.trc -- Specifies the trace file name (SQL Server always appends .trc extension)'
PRINT @crlf+@tabc+'@TraceName          sysname      default: tsqltrace -- Specifies the name of the trace'
PRINT @crlf+@tabc+'@TraceType          int          default: 0 -- Specifies the type of trace to run 1=General performance, 2=Detailed performance'
PRINT @crlf+@tabc+'@Options            int          default: 2 (TRACE_FILE_ROLLOVER)'
PRINT @crlf+@tabc+'@MaxFileSize        bigint       default: 2000000000 (MB)'
PRINT @crlf+@tabc+'@StopTime           datetime     default: NULL'
PRINT @crlf+@tabc+'@FileCount          int          default: NULL'
PRINT @crlf+@tabc+'@Events             varchar(300) default: SP-related events and errors/warnings -- Comma-delimited list specifying the events numbers to trace'
PRINT @crlf+@tabc+'@Cols               varchar(300) default: All columns -- Comma-delimited list specifying the column numbers to trace'
PRINT @crlf+@tabc+'@IncludeTextFilter  sysname      default: NULL -- String mask specifying what TextData strings to include in the trace'
PRINT @crlf+@tabc+'@ExcludeTextFilter  sysname      default: NULL -- String mask specifying what TextData strings to filter out of the trace'
PRINT @crlf+@tabc+'@IncludeObjIdFilter sysname      default: NULL -- Specifies the id of an object to target with the trace'
PRINT @crlf+@tabc+'@ExcludeObjIdFilter sysname      default: NULL -- Specifies the id of an object to exclude from the trace'
PRINT @crlf+@tabc+'@TraceId            int          default: NULL -- Specified the id of the trace to list when you specify the LIST option to @OnOff'
PRINT @crlf+@tabc+'@AppName            sysname      default: SQLDIAG -- Specifies the name of the calling application'
PRINT @crlf+'Examples: '
PRINT @crlf+@tabc+'EXEC sp_trace -- Displays this help text'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'' -- Starts a trace'
PRINT @crlf+@tabc+'EXEC sp_trace ''OFF'' -- Stops a trace'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'', @Filename=''d:\mssql7\log\mytrace'' -- Starts a trace with the specified file name'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'',@Events=''37,43'' -- Starts a trace the traps the specified event classes'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'',@Cols=''1,2,3'' -- Starts a trace that includes the specified columns'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'',@IncludeTextFilter=''EXEC% FooProc%'' -- Starts a trace that includes events matching the specified TextData mask'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'',@tracename=''General Performance'' -- Starts a trace using the specified name'
PRINT @crlf+@tabc+'EXEC sp_trace ''OFF'',@tracename=''General Performance'' -- Stops a trace with the specified name'
PRINT @crlf+@tabc+'EXEC sp_trace ''ON'',@filename = ''d:\mssql7\log\mytrace'', -- Starts a trace with the specified parameters' 
PRINT @tabc+@tabc+'@TraceName = ''General Performance'','
PRINT @tabc+@tabc+'@Options = 2, '
PRINT @tabc+@tabc+'@TraceType = 0,'
PRINT @tabc+@tabc+'@MaxFileSize = 500,'
PRINT @tabc+@tabc+'@StopTime = NULL, '
PRINT @tabc+@tabc+'@FileCount = NULL, '
PRINT @tabc+@tabc+'@Events = ''10,11,14,15,16,17,27,37,40,41,55,58,67,69,79,80,98'','
PRINT @tabc+@tabc+'@Cols = DEFAULT,'
PRINT @tabc+@tabc+'@IncludeTextFilter = NULL,'
PRINT @tabc+@tabc+'@IncludeObjIdFilter = NULL,'
PRINT @tabc+@tabc+'@ExcludeObjIdFilter = NULL'
PRINT @crlf+@tabc+'To list all the traces currently running:'
PRINT @crlf+@tabc+@tabc+'sp_trace ''LIST'''
PRINT @crlf+@tabc+'To list information about a particular trace:'
PRINT @crlf+@tabc+@tabc+'sp_trace ''LIST'', @TraceId=n -- where n is the trace ID you want to list'
PRINT @crlf+@tabc+'To stop a specific trace, supply the @TraceName parameter when you call sp_trace ''OFF''.'
RETURN 0
GO
IF (CHARINDEX('9.00.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_trace09','P') IS NULL) 
	RAISERROR('Error creating sp_trace09',16,127)
GO

IF OBJECT_ID('dbo.sp_trace10','P') IS NOT NULL
  DROP PROC dbo.sp_trace10
GO
PRINT ''
RAISERROR ('===== Creating sp_trace10', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_trace10 @OnOff varchar(4)='/?',
@FileName sysname=NULL,
@TraceName sysname='tsqltrace',
@Options int=2,
@MaxFileSize bigint=4000,
@StopTime datetime=NULL,
@FileCount int=NULL,
@TraceType int=0,
@Events varchar(300)=
--  11 - RPC:Starting
--  13 - SQL:BatchStarting
--  14 - Connect
--  15 - Disconnect
--  16 - Attention
--  17 - Existing Connection
--  33 - Exception
--  42 - SP:Starting
--  43 - SP:Completed
--  45 - SP:StmtCompleted
--  55 - Hash Warning
--  67 - Execution Warnings
--  69 - Sort Warnings
--  79 - Missing Column Statistics
--  80 - Missing Join Predicate
'11,13,14,15,16,17,33,42,43,45,55,67,69,79,80',
@Cols varchar(300)=
-- All columns
'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,',
@IncludeTextFilter sysname=NULL, @ExcludeTextFilter sysname=NULL,
@IncludeObjIdFilter int=NULL, @ExcludeObjIdFilter int=NULL,
@IncludeObjNameFilter sysname=NULL, @ExcludeObjNameFilter sysname=NULL,
@IncludeHostFilter sysname=NULL, @ExcludeHostFilter sysname=NULL, 
@IncludeSpidFilter int=NULL, @ExcludeSpidFilter int=NULL,  
@TraceId int = NULL,
@AppName sysname='SQLDIAG'
AS
BEGIN
	exec dbo.sp_trace09 @OnOff, @FileName, @TraceName, @Options, @MaxFileSize, 
						@StopTime, @FileCount, @TraceType, @Events, @Cols,
						@IncludeTextFilter, @ExcludeTextFilter,
						@IncludeObjIdFilter, @ExcludeObjIdFilter,
						@IncludeObjNameFilter, @ExcludeObjNameFilter,
						@IncludeHostFilter, @ExcludeHostFilter, 
						@IncludeSpidFilter, @ExcludeSpidFilter,  
						@TraceId,
						@AppName
END
GO
/*
The board chagned the Product Version format in Katmai. The current foramt starting from Katmai will be single zero for minor version instead of two zeros in previous sql versions. " 10.m.bbbb.rr "
*/
IF (CHARINDEX('10.0.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_trace10','P') IS NULL) 
	RAISERROR('Error creating sp_trace10',16,127)
GO

/* SQL11 Version Bump */
IF OBJECT_ID('dbo.sp_trace11','P') IS NOT NULL
  DROP PROC dbo.sp_trace11
GO
PRINT ''
RAISERROR ('===== Creating sp_trace11', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_trace11 @OnOff varchar(4)='/?',
@FileName sysname=NULL,
@TraceName sysname='tsqltrace',
@Options int=2,
@MaxFileSize bigint=4000,
@StopTime datetime=NULL,
@FileCount int=NULL,
@TraceType int=0,
@Events varchar(300)=
--  11 - RPC:Starting
--  13 - SQL:BatchStarting
--  14 - Connect
--  15 - Disconnect
--  16 - Attention
--  17 - Existing Connection
--  33 - Exception
--  42 - SP:Starting
--  43 - SP:Completed
--  45 - SP:StmtCompleted
--  55 - Hash Warning
--  67 - Execution Warnings
--  69 - Sort Warnings
--  79 - Missing Column Statistics
--  80 - Missing Join Predicate
'11,13,14,15,16,17,33,42,43,45,55,67,69,79,80',
@Cols varchar(300)=
-- All columns
'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,',
@IncludeTextFilter sysname=NULL, @ExcludeTextFilter sysname=NULL,
@IncludeObjIdFilter int=NULL, @ExcludeObjIdFilter int=NULL,
@IncludeObjNameFilter sysname=NULL, @ExcludeObjNameFilter sysname=NULL,
@IncludeHostFilter sysname=NULL, @ExcludeHostFilter sysname=NULL, 
@IncludeSpidFilter int=NULL, @ExcludeSpidFilter int=NULL,  
@TraceId int = NULL,
@AppName sysname='SQLDIAG'
AS
BEGIN
	exec dbo.sp_trace10 @OnOff, @FileName, @TraceName, @Options, @MaxFileSize, 
						@StopTime, @FileCount, @TraceType, @Events, @Cols,
						@IncludeTextFilter, @ExcludeTextFilter,
						@IncludeObjIdFilter, @ExcludeObjIdFilter,
						@IncludeObjNameFilter, @ExcludeObjNameFilter,
						@IncludeHostFilter, @ExcludeHostFilter, 
						@IncludeSpidFilter, @ExcludeSpidFilter,  
						@TraceId,
						@AppName
END
GO

IF (CHARINDEX('11.0.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_trace11','P') IS NULL) 
	RAISERROR('Error creating sp_trace11',16,127)
GO

/* SQL14 Version Bump */
IF OBJECT_ID('dbo.sp_trace12','P') IS NOT NULL
  DROP PROC dbo.sp_trace12
GO
PRINT ''
RAISERROR ('===== Creating sp_trace12', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_trace12 @OnOff varchar(4)='/?',
@FileName sysname=NULL,
@TraceName sysname='tsqltrace',
@Options int=2,
@MaxFileSize bigint=4000,
@StopTime datetime=NULL,
@FileCount int=NULL,
@TraceType int=0,
@Events varchar(300)=
--  11 - RPC:Starting
--  13 - SQL:BatchStarting
--  14 - Connect
--  15 - Disconnect
--  16 - Attention
--  17 - Existing Connection
--  33 - Exception
--  42 - SP:Starting
--  43 - SP:Completed
--  45 - SP:StmtCompleted
--  55 - Hash Warning
--  67 - Execution Warnings
--  69 - Sort Warnings
--  79 - Missing Column Statistics
--  80 - Missing Join Predicate
'11,13,14,15,16,17,33,42,43,45,55,67,69,79,80',
@Cols varchar(300)=
-- All columns
'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,',
@IncludeTextFilter sysname=NULL, @ExcludeTextFilter sysname=NULL,
@IncludeObjIdFilter int=NULL, @ExcludeObjIdFilter int=NULL,
@IncludeObjNameFilter sysname=NULL, @ExcludeObjNameFilter sysname=NULL,
@IncludeHostFilter sysname=NULL, @ExcludeHostFilter sysname=NULL, 
@IncludeSpidFilter int=NULL, @ExcludeSpidFilter int=NULL,  
@TraceId int = NULL,
@AppName sysname='SQLDIAG'
AS
BEGIN
	exec dbo.sp_trace10 @OnOff, @FileName, @TraceName, @Options, @MaxFileSize, 
						@StopTime, @FileCount, @TraceType, @Events, @Cols,
						@IncludeTextFilter, @ExcludeTextFilter,
						@IncludeObjIdFilter, @ExcludeObjIdFilter,
						@IncludeObjNameFilter, @ExcludeObjNameFilter,
						@IncludeHostFilter, @ExcludeHostFilter, 
						@IncludeSpidFilter, @ExcludeSpidFilter,  
						@TraceId,
						@AppName
END
GO

IF (CHARINDEX('12.0.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_trace12','P') IS NULL) 
	RAISERROR('Error creating sp_trace12',16,127)
GO

/* SQL15 Version Bump */
IF OBJECT_ID('dbo.sp_trace13','P') IS NOT NULL
  DROP PROC dbo.sp_trace13
GO
PRINT ''
RAISERROR ('===== Creating sp_trace13', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_trace13 @OnOff varchar(4)='/?',
@FileName sysname=NULL,
@TraceName sysname='tsqltrace',
@Options int=2,
@MaxFileSize bigint=4000,
@StopTime datetime=NULL,
@FileCount int=NULL,
@TraceType int=0,
@Events varchar(300)=
--  11 - RPC:Starting
--  13 - SQL:BatchStarting
--  14 - Connect
--  15 - Disconnect
--  16 - Attention
--  17 - Existing Connection
--  33 - Exception
--  42 - SP:Starting
--  43 - SP:Completed
--  45 - SP:StmtCompleted
--  55 - Hash Warning
--  67 - Execution Warnings
--  69 - Sort Warnings
--  79 - Missing Column Statistics
--  80 - Missing Join Predicate
'11,13,14,15,16,17,33,42,43,45,55,67,69,79,80',
@Cols varchar(300)=
-- All columns
'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,',
@IncludeTextFilter sysname=NULL, @ExcludeTextFilter sysname=NULL,
@IncludeObjIdFilter int=NULL, @ExcludeObjIdFilter int=NULL,
@IncludeObjNameFilter sysname=NULL, @ExcludeObjNameFilter sysname=NULL,
@IncludeHostFilter sysname=NULL, @ExcludeHostFilter sysname=NULL, 
@IncludeSpidFilter int=NULL, @ExcludeSpidFilter int=NULL,  
@TraceId int = NULL,
@AppName sysname='SQLDIAG'
AS
BEGIN
	exec dbo.sp_trace10 @OnOff, @FileName, @TraceName, @Options, @MaxFileSize, 
						@StopTime, @FileCount, @TraceType, @Events, @Cols,
						@IncludeTextFilter, @ExcludeTextFilter,
						@IncludeObjIdFilter, @ExcludeObjIdFilter,
						@IncludeObjNameFilter, @ExcludeObjNameFilter,
						@IncludeHostFilter, @ExcludeHostFilter, 
						@IncludeSpidFilter, @ExcludeSpidFilter,  
						@TraceId,
						@AppName
END
GO

IF (CHARINDEX('13.0.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_trace13','P') IS NULL) 
	RAISERROR('Error creating sp_trace13',16,127)
GO


/*

sp_code_runner

*/

IF OBJECT_ID('dbo.sp_code_runner07','P') IS NOT NULL
  DROP PROC dbo.sp_code_runner07
GO
PRINT ''
RAISERROR ('===== Creating sp_code_runner07', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_code_runner07 @StartCmd nvarchar(4000)='/?', @StartTime char(8)=NULL, @StopCondition nvarchar(4000)=NULL,
@StopMessage nvarchar(4000)='Stop condition met.', @IterationTime char(8)=NULL, @Duration char(8)=NULL, @StopCmd nvarchar(4000)=NULL, 
@PollingInterval char(8)='00:00:05', @PauseBetweenRuns char(8)=NULL,
@OutputDir sysname=NULL, @OutputFileMask sysname=NULL, @NumFiles int=16
AS
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
SET NOCOUNT ON

IF @StartCmd='/?' GOTO Help

-- Do some minimal parm checking
IF COALESCE(@Duration, @StopCondition) IS NULL BEGIN
  RAISERROR('You must supply either the @Duration or the @StopCondition parameter.',16,10)
  RETURN -1
END
IF @OutputFileMask='*' BEGIN
  RAISERROR('You may not specify an empty file mask.',16,10)
  RETURN -1
END
IF (@OutputDir IS NOT NULL) AND (@OutputFileMask IS NULL) BEGIN
  RAISERROR('You must supply a file mask when supplying a directory.',16,10)
  RETURN -1
END

-- Wait until the start time if there is one
IF @StartTime IS NOT NULL
  WAITFOR TIME @StartTime

-- Declare some variables and assign initial values
DECLARE @Stop int, @i int, @EndTime datetime, @CurDate datetime, @CurDateStr varchar(25),
        @FName sysname, @DelCmd varchar(255), @OutputDirCmd varchar(255), @SCmd nvarchar(4000),
        @IterationDateTime datetime
SET @CurDate=getdate()
SET @EndTime=@CurDate+@Duration
SET @Stop=CASE WHEN @CurDate >= @EndTime THEN 1 ELSE 0 END  -- @Duration of 00:00:00, perhaps?
SET @i=0
SET @StopCondition='IF ('+@StopCondition+') RAISERROR('''+@StopMessage+''',11,1)'

IF @OutputDir IS NOT NULL BEGIN -- If we're going to generate file names, delete any old ones
  IF RIGHT(@OutputDir,1)<>'\' SET @OutputDir=@OutputDir+'\'
  SET @DelCmd='DEL '+@OutputDir+@OutputFileMask
--  EXEC xp_cmdshell @DelCmd, no_output -- Delete all files matching the mask
  SET @OutputDirCmd='DIR '+@OutputDir+@OutputFileMask+' /B /ON' -- Prepare for Dir listing (below)
END

--IF (@Stop<>1) AND (@StopCondition IS NOT NULL)  -- Check the stop condition - don't start if it's met
--  EXEC @Stop=sp_executesql @StopCondition
WHILE (@Stop=0) BEGIN

  IF @OutputDir IS NOT NULL BEGIN -- Gen a file name using the current date and time
    SET @CurDateStr=CONVERT(CHAR(8),getdate(),112) + REPLACE(CONVERT(varchar(15),getdate(),114),':','')
    SET @FName=REPLACE(@OutputFileMask,'*',@CurDateStr)
		IF (@@MICROSOFTVERSION >= 134217922 /* SS2K RTM */) BEGIN 
			DECLARE @p int
			SET @p=CHARINDEX('.trc',@FName)
			IF (@p<>0) SET @FName=LEFT(@FName,@p-1)	
    END
    SET @SCmd=@StartCmd+', @FileName='''+CAST(@OutputDir+@FName as nvarchar(255))+''''
  END ELSE SET @SCmd=@StartCmd

  EXEC sp_executesql @SCmd -- Execute the start command

  SET @IterationDateTime=getdate()+ISNULL(@IterationTime,'23:59:59.999')
  WHILE (@Stop=0) AND (getdate()<@IterationDateTime) BEGIN

--	  IF @IterationTime IS NOT NULL -- Do the per iteration pause
--	    WAITFOR DELAY @IterationTime

/*
	-- Special handling for .trc files
	IF (CHARINDEX('.TRC',@OutputFileMask)<>0) BEGIN
		--Cab and delete inactive trace files -- we won't be able to open active files
		SET @DelCmd='for %d in ('+@OutputDir+'*.trc) do '+@OutputDir+'compress '+@OutputDir+' %d'
    SELECT @DelCmd
		EXEC master..xp_cmdshell @DelCmd, no_output
	END
*/

	  IF @PollingInterval IS NOT NULL -- Do polling interval delay
      WAITFOR DELAY @PollingInterval 
	
	  SET @Stop=CASE WHEN getdate() >= @EndTime THEN 1 ELSE 0 END -- Check the duration
	
	  IF (@Stop<>1) AND (@StopCondition IS NOT NULL) -- Check the stop condition
	    EXEC @Stop=sp_executesql @StopCondition
	END
  IF @StopCmd IS NOT NULL -- Execute the stop command if there is one
    EXEC sp_executesql @StopCmd
	
  SET @i=@i+1
  IF (@OutputDir IS NOT NULL) AND (@i>@NumFiles) BEGIN -- Get rid of extra files

    CREATE TABLE #files (fname varchar(255) NULL)

    INSERT #files
    EXEC master..xp_cmdshell @OutputDirCmd

    SELECT TOP 1 @DelCmd='DEL '+@OutputDir+fname FROM #files WHERE fname IS NOT NULL ORDER BY fname
    IF @@ROWCOUNT<>0
      EXEC master..xp_cmdshell @DelCmd, no_output

    DROP TABLE #files

  END
	  IF @PauseBetweenRuns IS NOT NULL -- Do pause between runs delay
      WAITFOR DELAY @PauseBetweenRuns
END
RETURN 0

Help:
DECLARE @crlf char(2), @tabc char(1)
SET @crlf=char(13)+char(10)
SET @tabc=char(9)
PRINT 'Procedure: sp_code_runner'
PRINT @crlf+'Purpose: runs a specified TSQL command batch or stored procedure repetitively for a specified period of time'
PRINT @crlf+'Parameters:'
PRINT @tabc+'@StartCmd           nvarchar(4000)   default: (none)       -- the TSQL command or procedure to start'
PRINT @tabc+'@StartTime          char(8)          default: NULL         -- the time to begin processing'
PRINT @tabc+'@StopCondition      nvarchar(4000)   default: NULL         -- the condition to check to determine whether to stop @StartCmd'
PRINT @tabc+'@StopMessage        nvarchar(4000)   default: NULL         -- the message to display when the stop condition is met'
PRINT @tabc+'@IterationTime      char(8)          default: NULL         -- the time that should elapse between iterations'
PRINT @tabc+'@PollingInterval    char(8)          default: 00:00:10     -- the time to pause between checks of the @StopCondition'
PRINT @tabc+'@Duration           char(8)          default: NULL         -- the total amount of time @StartCmd should run'
PRINT @tabc+'@StopCmd            nvarchar(4000)   default: NULL         -- the TSQL command or procedure to run to stop @StartCmd'
PRINT @tabc+'@OutputDir          sysname          default: NULL         -- the target directory for the output file (if applicable -- proc must support @FileName parameter)'
PRINT @tabc+'@OutputFileMask     sysname          default: NULL         -- the filemask for output files (if applicable -- proc must support @FileName parameter)'
PRINT @tabc+'@NumFiles           int              default: 16           -- the number of output files to retain (if applicable -- proc must support @FileName parameter)'
PRINT @crlf+'Examples: '
PRINT @tabc+'EXEC sp_code_runner @StartCmd=N''EXEC sp_trace ''''ON'''''','
PRINT @tabc+'@StopCondition=N''OBJECT_ID(''''tempdb..stoptab'''') IS NOT NULL'','
PRINT @tabc+'@StopMessage=N''Trace stopped'', @IterationTime=''00:30:00'','
PRINT @tabc+'@StopCmd=N''EXEC sp_trace ''''OFF'''''','
PRINT @tabc+'@OutputDir=''c:\temp'',@OutputFileMask=''sp_trace*.trc'', @NumFiles=16'
PRINT @crlf+@tabc+'EXEC sp_code_runner @StartCmd=N''EXEC sp_trace ''''ON'''''','
PRINT       @tabc+'@IterationTime=''00:30:00'', @Duration=''12:00:00'','
PRINT       @tabc+'@StopCmd=N''EXEC sp_trace ''''OFF'''''','
PRINT       @tabc+'@OutputDir=''c:\temp'',@OutputFileMask=''sp_trace*.trc'', @NumFiles=10'
PRINT @crlf+@tabc+'EXEC sp_code_runner @StartCmd=N''EXEC sp_blocker_pss70'','
PRINT       @tabc+'@StopCondition=N''EXISTS(SELECT waittime FROM master..sysprocesses WHERE waittime>60000 AND blocked>0)'','
PRINT       @tabc+'@StopMessage=''Longterm block detected'','
PRINT       @tabc+'@IterationTime=''00:05:00'', @Duration=''12:00:00'''
PRINT @crlf+@tabc+'EXEC sp_code_runner @StartCmd=N''EXEC sp_blocker_pss70'','
PRINT       @tabc+'@StartTime=''00:22:00'', @IterationTime=''00:05:00'', @Duration=''12:00:00'''
RETURN 0
GO
IF (OBJECT_ID('dbo.sp_code_runner07') IS NULL) 
	RAISERROR('Error creating sp_code_runner07',16,127)
GO
if (CHARINDEX('8.00.',@@VERSION)<>0)
BEGIN
	PRINT ''
	RAISERROR ('===== Renaming sp_code_runner07 to sp_code_runner08', 0, 1) WITH NOWAIT
	IF OBJECT_ID('dbo.sp_code_runner08','P') IS NOT NULL drop proc dbo.sp_code_runner08
	EXEC sp_rename 'sp_code_runner07','sp_code_runner08'
	IF (OBJECT_ID('dbo.sp_code_runner08') IS NULL) 
		RAISERROR('Error renaming sp_code_runner07 to sp_code_runner08',16,127)
END
ELSE if (CHARINDEX('9.00.',@@VERSION)<>0)
BEGIN
	PRINT ''
	RAISERROR ('===== Renaming sp_code_runner07 to sp_code_runner09', 0, 1) WITH NOWAIT
	IF OBJECT_ID('dbo.sp_code_runner09','P') IS NOT NULL drop proc dbo.sp_code_runner09
	EXEC sp_rename 'sp_code_runner07','sp_code_runner09'
	IF (OBJECT_ID('dbo.sp_code_runner09') IS NULL) 
		RAISERROR('Error renaming sp_code_runner07 to sp_code_runner09',16,127)
END
/*
The board chagned the Product Version format in Katmai. The current foramt starting from Katmai will be single zero for minor version instead of two zeros in previous sql versions. " 10.m.bbbb.rr "
*/
ELSE if (CHARINDEX('10.0.',@@VERSION)<>0)
BEGIN
	PRINT ''
	RAISERROR ('===== Renaming sp_code_runner07 to sp_code_runner10', 0, 1) WITH NOWAIT
	IF OBJECT_ID('dbo.sp_code_runner10','P') IS NOT NULL drop proc dbo.sp_code_runner10
	EXEC sp_rename 'sp_code_runner07','sp_code_runner10'
	IF (OBJECT_ID('dbo.sp_code_runner10') IS NULL) 
		RAISERROR('Error renaming sp_code_runner07 to sp_code_runner10',16,127)
END
/*
For KJ  version 10.50, since sqldiag still use sp_code_runner10 to capture traces if maxfilesize=-1(if maxfilesize!=-1, sqldiag use sp_trace10 to capture traces)
we use the same store procedure name sp_code_runner10 as the katmai version 10.0 does
*/
ELSE if (CHARINDEX('10.50.',@@VERSION)<>0)
BEGIN
	PRINT ''
	RAISERROR ('===== Renaming sp_code_runner07 to sp_code_runner10', 0, 1) WITH NOWAIT
	IF OBJECT_ID('dbo.sp_code_runner10','P') IS NOT NULL drop proc dbo.sp_code_runner10
	EXEC sp_rename 'sp_code_runner07','sp_code_runner10'
	IF (OBJECT_ID('dbo.sp_code_runner10') IS NULL) 
		RAISERROR('Error renaming sp_code_runner07 to sp_code_runner10',16,127)
END
/*
SQL11 Version Bump
*/
ELSE if (CHARINDEX('11.0.',@@VERSION)<>0)
BEGIN
	PRINT ''
	RAISERROR ('===== Renaming sp_code_runner07 to sp_code_runner11', 0, 1) WITH NOWAIT
	IF OBJECT_ID('dbo.sp_code_runner11','P') IS NOT NULL drop proc dbo.sp_code_runner11
	EXEC sp_rename 'sp_code_runner07','sp_code_runner11'
	IF (OBJECT_ID('dbo.sp_code_runner11') IS NULL) 
		RAISERROR('Error renaming sp_code_runner07 to sp_code_runner11',16,127)
END

/*
SQL14 Version Bump
*/
ELSE if (CHARINDEX('12.0.',@@VERSION)<>0)
BEGIN
	PRINT ''
	RAISERROR ('===== Renaming sp_code_runner07 to sp_code_runner12', 0, 1) WITH NOWAIT
	IF OBJECT_ID('dbo.sp_code_runner12','P') IS NOT NULL drop proc dbo.sp_code_runner12
	EXEC sp_rename 'sp_code_runner07','sp_code_runner12'
	IF (OBJECT_ID('dbo.sp_code_runner12') IS NULL) 
		RAISERROR('Error renaming sp_code_runner07 to sp_code_runner12',16,127)
END

/*
SQL15 Version Bump
*/
ELSE if (CHARINDEX('13.0.',@@VERSION)<>0)
BEGIN
	PRINT ''
	RAISERROR ('===== Renaming sp_code_runner07 to sp_code_runner13', 0, 1) WITH NOWAIT
	IF OBJECT_ID('dbo.sp_code_runner13','P') IS NOT NULL drop proc dbo.sp_code_runner13
	EXEC sp_rename 'sp_code_runner07','sp_code_runner13'
	IF (OBJECT_ID('dbo.sp_code_runner13') IS NULL) 
		RAISERROR('Error renaming sp_code_runner07 to sp_code_runner13',16,127)
END
GO


/*

sp_blocker_pss

*/

if object_id('dbo.sp_blocker_pss07','P') IS NOT NULL
   drop procedure dbo.sp_blocker_pss07
GO
PRINT ''
RAISERROR ('===== Creating sp_blocker_pss07', 0, 1) WITH NOWAIT
GO
create proc sp_blocker_pss07 (@latch int = 0, @fast int = 1, @appname sysname='SQLDIAG')
as 
--version 12
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
set nocount on
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @time3 datetime

set @time = getdate()

select spid, ecid, blocked, waittype, dbid, 'ignore_app' = case when convert(varchar(128),hostname) = @appname then 1 else 0 end into #probclients from master..sysprocesses where blocked!=0 or waittype != 0x0000
create unique clustered index pc on #probclients (blocked, spid, ecid)
set @time3 = getdate()

if exists (select spid from #probclients where ignore_app != 1 or waittype != 0x020B)
begin
   set @time2 = getdate()
   print ''
   print '7 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' ' + convert(varchar(12), datediff(ms,@time,@time3))

   insert #probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from #probclients
   where blocked not in (select spid from #probclients) and blocked != 0

   if (@fast = 1)
   begin
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)
      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage,last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121), net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid
      from master..sysprocesses
      where blocked!=0 or waittype != 0x0000
         or spid in (select blocked from #probclients where blocked != 0)
         or spid in (select spid from #probclients where blocked != 0)

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 


      print ''
      print 'SYSPROC FIRST PASS'
      select spid, ecid, waittype from #probclients where waittype != 0x0000

      if exists(select blocked from #probclients where blocked != 0)
      begin
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         print ''
         print 'SPIDs at the head of blocking chains'

         select spid from #probclients
         where blocked = 0 and spid in (select blocked from #probclients where spid != 0)
         if @latch = 0
         begin
            print 'SYSLOCKINFO'
            set @time2 = getdate()

            select spid = convert (smallint, req_spid),
               ecid = convert (smallint, req_ecid),
               rsc_dbid As dbid,
               rsc_objid As ObjId,
               rsc_indid As IndId,
               Type = case rsc_type when 1 then 'NUL'
                                    when 2 then 'DB'
                                    when 3 then 'FIL'
                                    when 4 then 'IDX'
                                    when 5 then 'TAB'
                                    when 6 then 'PAG'
                                    when 7 then 'KEY'
                                    when 8 then 'EXT'
                                    when 9 then 'RID' end,
               Resource = substring (rsc_text, 1, 16),
               Mode = case req_mode + 1 when 1 then NULL
                                        when 2 then 'Sch-S'
                                        when 3 then 'Sch-M'
                                        when 4 then 'IS'
                                        when 5 then 'SIU'
                                        when 6 then 'IS-S'
                                        when 7 then 'IX'
                                        when 8 then 'SIX'
                                        when 9 then 'S'
                                        when 10 then 'U'
                                        when 11 then 'IIn-Nul'
                                        when 12 then 'IS-X'
                                        when 13 then 'IU'
                                        when 14 then 'IS-U'
                                        when 15 then 'X'
                                        when 16 then 'BU' end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end
            from master.dbo.syslockinfo s,
               #probclients p
            where p.spid = s.req_spid

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set
      end -- blocking via locks
      else
         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
   end  -- fast set

   else  
   begin  -- Fast not set
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage,last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121), net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid
      from master..sysprocesses

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 

      print ''
      print 'SYSPROC FIRST PASS'
      select spid, ecid, waittype from #probclients where waittype != 0x0000

      if exists(select blocked from #probclients where blocked != 0)
      begin 
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         print ''
         print 'SPIDs at the head of blocking chains'
         select spid from #probclients
         where blocked = 0 and spid in (select blocked from #probclients where spid != 0)
         if @latch = 0 
         begin
            print 'SYSLOCKINFO'
            set @time2 = getdate()

            select spid = convert (smallint, req_spid),
               ecid = convert (smallint, req_ecid),
               rsc_dbid As dbid,
               rsc_objid As ObjId,
               rsc_indid As IndId,
               Type = case rsc_type when 1 then 'NUL'
                                    when 2 then 'DB'
                                    when 3 then 'FIL'
                                    when 4 then 'IDX'
                                    when 5 then 'TAB'
                                    when 6 then 'PAG'
                                    when 7 then 'KEY'
                                    when 8 then 'EXT'
                                    when 9 then 'RID' end,
               Resource = substring (rsc_text, 1, 16),
               Mode = case req_mode + 1 when 1 then NULL
                                        when 2 then 'Sch-S'
                                        when 3 then 'Sch-M'
                                        when 4 then 'IS'
                                        when 5 then 'SIU'
                                        when 6 then 'IS-S'
                                        when 7 then 'IX'
                                        when 8 then 'SIX'
                                        when 9 then 'S'
                                        when 10 then 'U'
                                        when 11 then 'IIn-Nul'
                                        when 12 then 'IS-X'
                                        when 13 then 'IU'
                                        when 14 then 'IS-U'
                                        when 15 then 'X'
                                        when 16 then 'BU' end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end
            from master.dbo.syslockinfo

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set

      end
      else
         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)

   end -- Fast not set

   print ''
   print 'DBCC SQLPERF(WAITSTATS)'
   dbcc sqlperf(waitstats)
   print ''

   print ''
   Print '*********************************************************************'
   Print 'Print out DBCC INPUTBUFFER for all blocked or blocking spids.'
   Print '*********************************************************************'

   declare ibuffer cursor fast_forward for
   select cast (spid as varchar(6)) as spid, cast (blocked as varchar(6)) as blocked
   from #probclients
   where (spid <> @@spid) and (blocked!=0
      or (waittype != 0x0000 and ignore_app = 0)
      or spid in (select blocked from #probclients where blocked != 0))

   open ibuffer
   fetch next from ibuffer into @spid, @blocked
   while (@@fetch_status != -1)
   begin
      print ''
      exec ('print ''DBCC INPUTBUFFER FOR SPID ' + @spid + '''')
      exec ('dbcc inputbuffer (' + @spid + ')')

      fetch next from ibuffer into @spid, @blocked
   end
   deallocate ibuffer

   Print ''
   Print '*******************************************************************************'
   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'
   Print '*******************************************************************************'
   declare ibuffer cursor fast_forward for
   select distinct cast (dbid as varchar(6)) from #probclients
   where dbid != 0
   open ibuffer
   fetch next from ibuffer into @spid
   while (@@fetch_status != -1)
   begin
      print ''
      exec ('print ''DBCC OPENTRAN FOR DBID ' + @spid + '''')
      exec ('dbcc opentran (' + @spid + ')')
      print ''
      if @spid = '2' select @blocked = 'Y'
      fetch next from ibuffer into @spid
   end
   deallocate ibuffer
   if @blocked != 'Y' 
   begin
      print ''
      print 'DBCC OPENTRAN FOR tempdb database'
      exec ('dbcc opentran (tempdb)')
   end

   print ''
   print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
  print '7 No Waittypes: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time3)) + ' ' + ISNULL (@@servername,'(null)')
GO 
IF (CHARINDEX('7.00.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_blocker_pss07') IS NULL)
	RAISERROR('Error creating sp_blocker_pss07',16,127)
go

-- SP3 and later

if object_id('dbo.sp_blocker_pss08','P') IS NOT NULL
   drop procedure dbo.sp_blocker_pss08
GO
PRINT ''
RAISERROR ('===== Creating sp_blocker_pss08 (SP3 and later)', 0, 1) WITH NOWAIT
GO
create procedure sp_blocker_pss08 (@latch int = 0, @fast int = 1, @appname sysname='SQLDIAG')
as 
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
--version 14SP3
set nocount on
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime

set @time = getdate()
declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint, ignore_app tinyint, primary key (blocked, spid, ecid))
insert @probclients select spid, ecid, blocked, waittype, dbid, case when convert(varchar(128),hostname) = @appname then 1 else 0 end from master..sysprocesses where blocked!=0 or waittype != 0x0000

if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)
begin
   set @time2 = getdate()
   print ''
   print '8.2 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2))

   insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients
      where blocked not in (select spid from @probclients) and blocked != 0

   if (@fast = 1)
   begin
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage, last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121),net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid,
         sql_handle, stmt_start, stmt_end
      from master..sysprocesses
      where blocked!=0 or waittype != 0x0000
         or spid in (select blocked from @probclients where blocked != 0)
         or spid in (select spid from @probclients where blocked != 0)

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 

      print ''
      print 'SYSPROC FIRST PASS'
      select spid, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         print ''
         print 'SPIDs at the head of blocking chains'
         select spid from @probclients
            where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
         if @latch = 0
         begin
            print 'SYSLOCKINFO'
            select @time2 = getdate()

            select spid = convert (smallint, req_spid),
               ecid = convert (smallint, req_ecid),
               rsc_dbid As dbid,
               rsc_objid As ObjId,
               rsc_indid As IndId,
               Type = case rsc_type when 1 then 'NUL'
                                    when 2 then 'DB'
                                    when 3 then 'FIL'
                                    when 4 then 'IDX'
                                    when 5 then 'TAB'
                                    when 6 then 'PAG'
                                    when 7 then 'KEY'
                                    when 8 then 'EXT'
                                    when 9 then 'RID'
                                    when 10 then 'APP' end,
               Resource = substring (rsc_text, 1, 16),
               Mode = case req_mode + 1 when 1 then NULL
                                        when 2 then 'Sch-S'
                                        when 3 then 'Sch-M'
                                        when 4 then 'S'
                                        when 5 then 'U'
                                        when 6 then 'X'
                                        when 7 then 'IS'
                                        when 8 then 'IU'
                                        when 9 then 'IX'
                                        when 10 then 'SIU'
                                        when 11 then 'SIX'
                                        when 12 then 'UIX'
                                        when 13 then 'BU'
                                        when 14 then 'RangeS-S'
                                        when 15 then 'RangeS-U'
                                        when 16 then 'RangeIn-Null'
                                        when 17 then 'RangeIn-S'
                                        when 18 then 'RangeIn-U'
                                        when 19 then 'RangeIn-X'
                                        when 20 then 'RangeX-S'
                                        when 21 then 'RangeX-U'
                                        when 22 then 'RangeX-X'end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end,
               req_transactionID As TransID, req_transactionUOW As TransUOW
            from master.dbo.syslockinfo s,
               @probclients p
            where p.spid = s.req_spid

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set
      end
      else
         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
      print ''
   end  -- fast set

   else  
   begin  -- Fast not set
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage, last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121),net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid,
         sql_handle, stmt_start, stmt_end
      from master..sysprocesses

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 

      print ''
      print 'SYSPROC FIRST PASS'
      select spid, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         print ''
         print 'SPIDs at the head of blocking chains'
         select spid from @probclients
         where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
         if @latch = 0
         begin
            print 'SYSLOCKINFO'
            select @time2 = getdate()

            select spid = convert (smallint, req_spid),
               ecid = convert (smallint, req_ecid),
               rsc_dbid As dbid,
               rsc_objid As ObjId,
               rsc_indid As IndId,
               Type = case rsc_type when 1 then 'NUL'
                                    when 2 then 'DB'
                                    when 3 then 'FIL'
                                    when 4 then 'IDX'
                                    when 5 then 'TAB'
                                    when 6 then 'PAG'
                                    when 7 then 'KEY'
                                    when 8 then 'EXT'
                                    when 9 then 'RID'
                                    when 10 then 'APP' end,
               Resource = substring (rsc_text, 1, 16),
               Mode = case req_mode + 1 when 1 then NULL
                                        when 2 then 'Sch-S'
                                        when 3 then 'Sch-M'
                                        when 4 then 'S'
                                        when 5 then 'U'
                                        when 6 then 'X'
                                        when 7 then 'IS'
                                        when 8 then 'IU'
                                        when 9 then 'IX'
                                        when 10 then 'SIU'
                                        when 11 then 'SIX'
                                        when 12 then 'UIX'
                                        when 13 then 'BU'
                                        when 14 then 'RangeS-S'
                                        when 15 then 'RangeS-U'
                                        when 16 then 'RangeIn-Null'
                                        when 17 then 'RangeIn-S'
                                        when 18 then 'RangeIn-U'
                                        when 19 then 'RangeIn-X'
                                        when 20 then 'RangeX-S'
                                        when 21 then 'RangeX-U'
                                        when 22 then 'RangeX-X'end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end,
               req_transactionID As TransID, req_transactionUOW As TransUOW
            from master.dbo.syslockinfo

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set
      end
      else
        print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
      print ''
   end -- Fast not set

   print 'DBCC SQLPERF(WAITSTATS)'
   dbcc sqlperf(waitstats)

   Print ''
   Print '*********************************************************************'
   Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
   Print '*********************************************************************'

   declare ibuffer cursor fast_forward for
   select cast (spid as varchar(6)) as spid, cast (blocked as varchar(6)) as blocked
   from @probclients
   where (spid <> @@spid) and 
      ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))
      or spid in (select blocked from @probclients where blocked != 0))
   open ibuffer
   fetch next from ibuffer into @spid, @blocked
   while (@@fetch_status != -1)
   begin
      print ''
      print 'DBCC INPUTBUFFER FOR SPID ' + @spid
      exec ('dbcc inputbuffer (' + @spid + ')')

      fetch next from ibuffer into @spid, @blocked
   end
   deallocate ibuffer

   Print ''
   Print '*******************************************************************************'
   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'
   Print '*******************************************************************************'
   declare ibuffer cursor fast_forward for
   select distinct cast (dbid as varchar(6)) from @probclients
   where dbid != 0
   open ibuffer
   fetch next from ibuffer into @spid
   while (@@fetch_status != -1)
   begin
      print ''
      print 'DBCC OPENTRAN FOR DBID ' + @spid
      exec ('dbcc opentran (' + @spid + ')')
      print ''
      if @spid = '2' select @blocked = 'Y'
      fetch next from ibuffer into @spid
   end
   deallocate ibuffer
   if @blocked != 'Y' 
   begin
      print ''
      print 'DBCC OPENTRAN FOR tempdb database'
      exec ('dbcc opentran (tempdb)')
   end

   print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)')
GO    

-- Pre-SP3

PRINT ''
RAISERROR ('===== Creating sp_blocker_pss08 (pre-SP3)', 0, 1) WITH NOWAIT
GO
create proc sp_blocker_pss08 (@latch int = 0, @fast int = 1, @appname sysname='SQLDIAG')
as 
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
--version 14
set nocount on
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime

set @time = getdate()
declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint, ignore_app tinyint, primary key (blocked, spid, ecid))
insert @probclients select spid, ecid, blocked, waittype, dbid, case when convert(varchar(128),hostname) = @appname then 1 else 0 end from master..sysprocesses where blocked!=0 or waittype != 0x0000

if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)
begin
   set @time2 = getdate()
   print ''
   print '8 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2))

   insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients
      where blocked not in (select spid from @probclients) and blocked != 0

   if (@fast = 1)
   begin
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage,last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121), net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid
      from master..sysprocesses
      where blocked!=0 or waittype != 0x0000
         or spid in (select blocked from @probclients where blocked != 0)
         or spid in (select spid from @probclients where waittype != 0x0000)

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 

      print ''
      print 'SYSPROC FIRST PASS'
      select spid, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         print ''
         print 'SPIDs at the head of blocking chains'
         select spid from @probclients
            where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
         if @latch = 0
         begin
            print 'SYSLOCKINFO'
            select @time2 = getdate()

            select spid = convert (smallint, req_spid),
               ecid = convert (smallint, req_ecid),
               rsc_dbid As dbid,
               rsc_objid As ObjId,
               rsc_indid As IndId,
               Type = case rsc_type when 1 then 'NUL'
                                    when 2 then 'DB'
                                    when 3 then 'FIL'
                                    when 4 then 'IDX'
                                    when 5 then 'TAB'
                                    when 6 then 'PAG'
                                    when 7 then 'KEY'
                                    when 8 then 'EXT'
                                    when 9 then 'RID'
                                    when 10 then 'APP' end,
               Resource = substring (rsc_text, 1, 16),
               Mode = case req_mode + 1 when 1 then NULL
                                        when 2 then 'Sch-S'
                                        when 3 then 'Sch-M'
                                        when 4 then 'S'
                                        when 5 then 'U'
                                        when 6 then 'X'
                                        when 7 then 'IS'
                                        when 8 then 'IU'
                                        when 9 then 'IX'
                                        when 10 then 'SIU'
                                        when 11 then 'SIX'
                                        when 12 then 'UIX'
                                        when 13 then 'BU'
                                        when 14 then 'RangeS-S'
                                        when 15 then 'RangeS-U'
                                        when 16 then 'RangeIn-Null'
                                        when 17 then 'RangeIn-S'
                                        when 18 then 'RangeIn-U'
                                        when 19 then 'RangeIn-X'
                                        when 20 then 'RangeX-S'
                                        when 21 then 'RangeX-U'
                                        when 22 then 'RangeX-X'end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end,
               req_transactionID As TransID, req_transactionUOW As TransUOW
            from master.dbo.syslockinfo s,
               @probclients p
            where p.spid = s.req_spid

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set
      end
      else
         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
      print ''
   end  -- fast set

   else  
   begin  -- Fast not set
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage,last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121), net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid
      from master..sysprocesses

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 

      print ''
      print 'SYSPROC FIRST PASS'
      select spid, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         print ''
         print 'SPIDs at the head of blocking chains'
         select spid from @probclients
         where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
         if @latch = 0
         begin
            print 'SYSLOCKINFO'
            select @time2 = getdate()

            select spid = convert (smallint, req_spid),
               ecid = convert (smallint, req_ecid),
               rsc_dbid As dbid,
               rsc_objid As ObjId,
               rsc_indid As IndId,
               Type = case rsc_type when 1 then 'NUL'
                                    when 2 then 'DB'
                                    when 3 then 'FIL'
                                    when 4 then 'IDX'
                                    when 5 then 'TAB'
                                    when 6 then 'PAG'
                                    when 7 then 'KEY'
                                    when 8 then 'EXT'
                                    when 9 then 'RID'
                                    when 10 then 'APP' end,
               Resource = substring (rsc_text, 1, 16),
               Mode = case req_mode + 1 when 1 then NULL
                                        when 2 then 'Sch-S'
                                        when 3 then 'Sch-M'
                                        when 4 then 'S'
                                        when 5 then 'U'
                                        when 6 then 'X'
                                        when 7 then 'IS'
                                        when 8 then 'IU'
                                        when 9 then 'IX'
                                        when 10 then 'SIU'
                                        when 11 then 'SIX'
                                        when 12 then 'UIX'
                                        when 13 then 'BU'
                                        when 14 then 'RangeS-S'
                                        when 15 then 'RangeS-U'
                                        when 16 then 'RangeIn-Null'
                                        when 17 then 'RangeIn-S'
                                        when 18 then 'RangeIn-U'
                                        when 19 then 'RangeIn-X'
                                        when 20 then 'RangeX-S'
                                        when 21 then 'RangeX-U'
                                        when 22 then 'RangeX-X'end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end,
               req_transactionID As TransID, req_transactionUOW As TransUOW
            from master.dbo.syslockinfo

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set
      end
      else
        print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
      print ''
   end -- Fast not set

   print 'DBCC SQLPERF(WAITSTATS)'
   dbcc sqlperf(waitstats)

   Print ''
   Print '*********************************************************************'
   Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
   Print '*********************************************************************'

   declare ibuffer cursor fast_forward for
   select cast (spid as varchar(6)) as spid, cast (blocked as varchar(6)) as blocked
   from @probclients
   where (spid <> @@spid) and 
      ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))
      or spid in (select blocked from @probclients where blocked != 0))
   open ibuffer
   fetch next from ibuffer into @spid, @blocked
   while (@@fetch_status != -1)
   begin
      print ''
      print 'DBCC INPUTBUFFER FOR SPID ' + @spid
      exec ('dbcc inputbuffer (' + @spid + ')')

      fetch next from ibuffer into @spid, @blocked
   end
   deallocate ibuffer

   Print ''
   Print '*******************************************************************************'
   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'
   Print '*******************************************************************************'
   declare ibuffer cursor fast_forward for
   select distinct cast (dbid as varchar(6)) from @probclients
   where dbid != 0
   open ibuffer
   fetch next from ibuffer into @spid
   while (@@fetch_status != -1)
   begin
      print ''
      print 'DBCC OPENTRAN FOR DBID ' + @spid
      exec ('dbcc opentran (' + @spid + ')')
      print ''
      if @spid = '2' select @blocked = 'Y'
      fetch next from ibuffer into @spid
   end
   deallocate ibuffer
   if @blocked != 'Y' 
   begin
      print ''
      print 'DBCC OPENTRAN FOR tempdb database'
      exec ('dbcc opentran (tempdb)')
   end

   print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)')
GO    
IF (CHARINDEX('8.00.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_blocker_pss08') IS NULL)
	RAISERROR('Error creating sp_blocker_pss08',16,127)
go

/*

sp_sqldiag_cleanup

*/

IF OBJECT_ID('dbo.sp_sqldiag_cleanup07') IS NOT NULL
  DROP PROC dbo.sp_sqldiag_cleanup07
GO
PRINT ''
RAISERROR ('===== Creating sp_sqldiag_cleanup07', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_sqldiag_cleanup07 @AppName sysname='PSSDIAG'
AS
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
IF (CHARINDEX('7.00.',@@VERSION)<>0) AND object_id('dbo.sp_trace07') IS NOT NULL
	EXEC dbo.sp_trace07 'OFF', @AppName=@AppName
ELSE IF (CHARINDEX('8.00.',@@VERSION)<>0) AND object_id('dbo.sp_trace08') IS NOT NULL
	EXEC dbo.sp_trace08 'OFF', @AppName=@AppName

EXEC('DBCC CACHEPROFILE(2)') -- Turn off cache profiling (wrap in EXEC() to prevent batch-aborting error on 7.0)

DECLARE @spid int, @cmd varchar(30)
DECLARE osqls CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE hostname=@AppName AND spid<>@@SPID
FOR READ ONLY

OPEN osqls
FETCH osqls INTO @spid
WHILE @@FETCH_STATUS=0 BEGIN
  SET @cmd='KILL '+CAST(@spid AS varchar)
  EXEC(@cmd)
  FETCH osqls INTO @spid
END
CLOSE osqls
DEALLOCATE osqls

GO
IF (CHARINDEX('7.00.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_sqldiag_cleanup07') IS NULL)
	RAISERROR('Error creating sp_sqldiag_cleanup07',16,127)
GO
IF (CHARINDEX('8.00.',@@VERSION)<>0)
BEGIN
	PRINT ''
	RAISERROR ('===== Renaming sp_sqldiag_cleanup07 to sp_sqldiag_cleanup08', 0, 1) WITH NOWAIT
	IF OBJECT_ID('dbo.sp_sqldiag_cleanup08','P') IS NOT NULL drop proc dbo.sp_sqldiag_cleanup08
	EXEC sp_rename 'sp_sqldiag_cleanup07','sp_sqldiag_cleanup08'
	IF OBJECT_ID('dbo.sp_sqldiag_cleanup08') IS NULL
		RAISERROR('Error renaming sp_sqldiag_cleanup07 to sp_sqldiag_cleanup08',16,127)
END
GO


IF OBJECT_ID('dbo.sp_sqldiag_cleanup09') IS NOT NULL
  DROP PROC dbo.sp_sqldiag_cleanup09 
GO
PRINT ''
RAISERROR ('===== Creating sp_sqldiag_cleanup09', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_sqldiag_cleanup09 @AppName sysname='SQLDIAG'
AS
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
IF object_id('dbo.sp_trace09') IS NOT NULL BEGIN
	EXEC dbo.sp_trace09 'OFF', @AppName=@AppName
	EXEC dbo.sp_trace09 'OFF', @AppName=@AppName, @TraceName='tsqlblktrace'
END

DECLARE @spid int, @cmd varchar(30)
DECLARE osqls CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE hostname=@AppName AND spid<>@@SPID
FOR READ ONLY

OPEN osqls
FETCH osqls INTO @spid
WHILE @@FETCH_STATUS=0 BEGIN
  SET @cmd='KILL '+CAST(@spid AS varchar)
  EXEC(@cmd)
  FETCH osqls INTO @spid
END
CLOSE osqls
DEALLOCATE osqls

GO
IF (CHARINDEX('9.00.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_sqldiag_cleanup09','P') IS NULL) 
	RAISERROR('Error creating sp_sqldiag_cleanup09',16,127)
GO

IF OBJECT_ID('dbo.sp_sqldiag_cleanup10') IS NOT NULL
  DROP PROC dbo.sp_sqldiag_cleanup10 
GO
PRINT ''
RAISERROR ('===== Creating sp_sqldiag_cleanup10', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_sqldiag_cleanup10 @AppName sysname='SQLDIAG'
AS
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
IF object_id('dbo.sp_trace10') IS NOT NULL BEGIN
	EXEC dbo.sp_trace10 'OFF', @AppName=@AppName
	EXEC dbo.sp_trace10 'OFF', @AppName=@AppName, @TraceName='tsqlblktrace'
END

DECLARE @spid int, @cmd varchar(30)
DECLARE osqls CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE hostname=@AppName AND spid<>@@SPID
FOR READ ONLY

OPEN osqls
FETCH osqls INTO @spid
WHILE @@FETCH_STATUS=0 BEGIN
  SET @cmd='KILL '+CAST(@spid AS varchar)
  EXEC(@cmd)
  FETCH osqls INTO @spid
END
CLOSE osqls
DEALLOCATE osqls

GO
/*
The board chagned the Product Version format in Katmai. The current foramt starting from Katmai will be single zero for minor version instead of two zeros in previous sql versions. " 10.m.bbbb.rr "
*/
IF (CHARINDEX('10.0.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_sqldiag_cleanup10') IS NULL) 
	RAISERROR('Error creating sp_sqldiag_cleanup10',16,127)
GO

/* SQL11 Version Bump */

IF OBJECT_ID('dbo.sp_sqldiag_cleanup11') IS NOT NULL
  DROP PROC dbo.sp_sqldiag_cleanup11 
GO
PRINT ''
RAISERROR ('===== Creating sp_sqldiag_cleanup11', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_sqldiag_cleanup11 @AppName sysname='SQLDIAG'
AS
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
IF object_id('dbo.sp_trace11') IS NOT NULL BEGIN
	EXEC dbo.sp_trace11 'OFF', @AppName=@AppName
	EXEC dbo.sp_trace11 'OFF', @AppName=@AppName, @TraceName='tsqlblktrace'
END

DECLARE @spid int, @cmd varchar(30)
DECLARE osqls CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE hostname=@AppName AND spid<>@@SPID
FOR READ ONLY

OPEN osqls
FETCH osqls INTO @spid
WHILE @@FETCH_STATUS=0 BEGIN
  SET @cmd='KILL '+CAST(@spid AS varchar)
  EXEC(@cmd)
  FETCH osqls INTO @spid
END
CLOSE osqls
DEALLOCATE osqls

GO

IF (CHARINDEX('11.0.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_sqldiag_cleanup11') IS NULL) 
	RAISERROR('Error creating sp_sqldiag_cleanup11',16,127)
GO

/* SQL14 Version Bump */

IF OBJECT_ID('dbo.sp_sqldiag_cleanup12') IS NOT NULL
  DROP PROC dbo.sp_sqldiag_cleanup12 
GO
PRINT ''
RAISERROR ('===== Creating sp_sqldiag_cleanup12', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_sqldiag_cleanup12 @AppName sysname='SQLDIAG'
AS
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
IF object_id('dbo.sp_trace12') IS NOT NULL BEGIN
	EXEC dbo.sp_trace11 'OFF', @AppName=@AppName
	EXEC dbo.sp_trace11 'OFF', @AppName=@AppName, @TraceName='tsqlblktrace'
END

DECLARE @spid int, @cmd varchar(30)
DECLARE osqls CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE hostname=@AppName AND spid<>@@SPID
FOR READ ONLY

OPEN osqls
FETCH osqls INTO @spid
WHILE @@FETCH_STATUS=0 BEGIN
  SET @cmd='KILL '+CAST(@spid AS varchar)
  EXEC(@cmd)
  FETCH osqls INTO @spid
END
CLOSE osqls
DEALLOCATE osqls

GO

IF (CHARINDEX('12.0.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_sqldiag_cleanup12') IS NULL) 
	RAISERROR('Error creating sp_sqldiag_cleanup12',16,127)
GO

/* SQL15 Version Bump */

IF OBJECT_ID('dbo.sp_sqldiag_cleanup13') IS NOT NULL
  DROP PROC dbo.sp_sqldiag_cleanup13 
GO
PRINT ''
RAISERROR ('===== Creating sp_sqldiag_cleanup13', 0, 1) WITH NOWAIT
GO
CREATE PROC dbo.sp_sqldiag_cleanup13 @AppName sysname='SQLDIAG'
AS
if is_member('sysadmin')=0 begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end
IF object_id('dbo.sp_trace13') IS NOT NULL BEGIN
	EXEC dbo.sp_trace13 'OFF', @AppName=@AppName
	EXEC dbo.sp_trace13 'OFF', @AppName=@AppName, @TraceName='tsqlblktrace'
END

DECLARE @spid int, @cmd varchar(30)
DECLARE osqls CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE hostname=@AppName AND spid<>@@SPID
FOR READ ONLY

OPEN osqls
FETCH osqls INTO @spid
WHILE @@FETCH_STATUS=0 BEGIN
  SET @cmd='KILL '+CAST(@spid AS varchar)
  EXEC(@cmd)
  FETCH osqls INTO @spid
END
CLOSE osqls
DEALLOCATE osqls

GO

IF (CHARINDEX('13.0.',@@VERSION)<>0) AND (OBJECT_ID('dbo.sp_sqldiag_cleanup13') IS NULL) 
	RAISERROR('Error creating sp_sqldiag_cleanup13',16,127)
GO