USE master
GO
DECLARE @SQL VARCHAR(MAX) = ''
SELECT @SQL = @SQL + 'kill ' + LTRIM(STR(SPID)) + '
'
FROM sys.sysprocesses (NOLOCK)
WHERE dbid = DB_ID('SqlExecDb')
PRINT @SQL
IF @SQL <> ''
EXEC(@SQL )
GO
IF DB_ID('SqlExecDb') IS NOT NULL
EXEC ('DROP DATABASE SqlExecDb')
GO
CREATE DATABASE SqlExecDb
GO
USE SqlExecDb
GO
USE SqlExecDb
EXEC SP_CHANGEDBOWNER
'SA'
ALTER DATABASE SqlExecDb SET TRUSTWORTHY ON
GO
--#region Drop Objects
IF OBJECT_ID('dbo.GetNumbers') IS NOT NULL
DROP FUNCTION dbo.GetNumbers
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'SQLExecution' )
DROP TABLE [SE].[SQLExecution]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'SQLExecutionActive' )
DROP VIEW [SE].[SQLExecutionActive]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'SQLExecutionAll' )
DROP VIEW [SE].[SQLExecutionAll]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'SQLExecutionCleanup' )
DROP PROCEDURE [SE].[SQLExecutionCleanup]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'SQLExecutionExecProc' )
DROP PROCEDURE [SE].[SQLExecutionExecProc]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'SQLExecutionGetGroup' )
DROP FUNCTION [SE].[SQLExecutionGetGroup]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'SQLExecutionGroup' )
DROP TABLE [SE].[SQLExecutionGroup]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'SQLExecutionGroupInsert' )
DROP PROCEDURE [SE].[SQLExecutionGroupInsert]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'SQLExecutionKillAll' )
DROP PROCEDURE [SE].[SQLExecutionKillAll]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'SQLExecutionStatus' )
DROP TABLE [SE].[SQLExecutionStatus]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'SQLExecutionTracker' )
DROP TABLE [SE].[SQLExecutionTracker]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'SQLExecutionTrackerPivot' )
DROP VIEW [SE].[SQLExecutionTrackerPivot]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'SQLExecutionWait' )
DROP PROCEDURE [SE].[SQLExecutionWait]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE s.name = 'SE'
AND o.name = 'usp_SQLExecutionInsert' )
DROP PROCEDURE [SE].[usp_SQLExecutionInsert]
GO
IF EXISTS ( SELECT *
FROM sys.objects (NOLOCK)
WHERE OBJECT_ID = OBJECT_ID(N'[SE].[SQLExecutionActive]') )
DROP VIEW [SE].[SQLExecutionActive]
GO
IF EXISTS ( SELECT *
FROM sys.objects (NOLOCK)
WHERE OBJECT_ID = OBJECT_ID(N'[SE].[SQLExecutionAll]') )
DROP VIEW [SE].[SQLExecutionAll]
GO
IF EXISTS ( SELECT *
FROM sys.objects (NOLOCK)
WHERE OBJECT_ID = OBJECT_ID(N'[SE].[SQLExecutionTrackerPivot]') )
DROP VIEW [SE].[SQLExecutionTrackerPivot]
GO
IF EXISTS ( SELECT *
FROM sys.objects (NOLOCK)
WHERE OBJECT_ID = OBJECT_ID(N'[SE].[SQLExecutionGetGroup]') )
DROP FUNCTION [SE].[SQLExecutionGetGroup]
GO
IF EXISTS ( SELECT *
FROM sys.objects (NOLOCK)
WHERE OBJECT_ID = OBJECT_ID(N'[SE].[SQLExecutionCleanup]') )
DROP PROCEDURE [SE].[SQLExecutionCleanup]
GO
IF EXISTS ( SELECT *
FROM sys.objects (NOLOCK)
WHERE OBJECT_ID = OBJECT_ID(N'[SE].[SQLExecutionExecProc]') )
DROP PROCEDURE [SE].[SQLExecutionExecProc]
GO
IF EXISTS ( SELECT *
FROM sys.objects (NOLOCK)
WHERE OBJECT_ID = OBJECT_ID(N'[SE].[SQLExecutionGroupInsert]') )
DROP PROCEDURE [SE].[SQLExecutionGroupInsert]
GO
IF EXISTS ( SELECT *
FROM sys.objects (NOLOCK)
WHERE OBJECT_ID = OBJECT_ID(N'[SE].[SQLExecutionKillAll]') )
DROP PROCEDURE [SE].[SQLExecutionKillAll]
GO
IF EXISTS ( SELECT *
FROM sys.objects (NOLOCK)
WHERE OBJECT_ID = OBJECT_ID(N'[SE].[SQLExecutionWait]') )
DROP PROCEDURE [SE].[SQLExecutionWait]
GO
IF EXISTS ( SELECT *
FROM sys.objects (NOLOCK)
WHERE OBJECT_ID = OBJECT_ID(N'[SE].[usp_SQLExecutionInsert]') )
DROP PROCEDURE [SE].[usp_SQLExecutionInsert]
GO
IF EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
WHERE name = 'SE' )
DROP SCHEMA SE
--#endregion
GO
--#region Create Tables
IF NOT EXISTS ( SELECT *
FROM sys.schemas s (NOLOCK)
WHERE name = 'SE' )
EXEC('CREATE SCHEMA SE')
GO
CREATE TABLE SE.SQLExecutionStatus (StatusId TINYINT NOT NULL,
[Description] VARCHAR(256) NOT NULL)
GO
ALTER TABLE SE.SQLExecutionStatus ADD CONSTRAINT SQLExecutionStatus_PKC PRIMARY KEY CLUSTERED(StatusId)
GO
ALTER TABLE SE.SQLExecutionStatus ADD CONSTRAINT SQLExecutionStatus_UNIQ UNIQUE NONCLUSTERED([Description])
GO
INSERT SE.SQLExecutionStatus
(StatusId, [Description])
VALUES (10, 'Initial'),
(20, 'Running'),
(30, 'Failed'),
(35, 'Commit Wait'),
(40, 'Completed')
GO
CREATE TABLE SE.SQLExecutionGroup (ExecutionGroupId INT IDENTITY
NOT NULL,
Description VARCHAR(256) NOT NULL,
UseTransaction BIT NOT NULL
CONSTRAINT DF_SQLExecutionGroup_UseTransaction DEFAULT (0),
DateAdded DATETIME2(2) NOT NULL
CONSTRAINT DF_SQLExecutionGroup_DateAdded DEFAULT (GETDATE()),
UserName VARCHAR(256) NOT NULL
CONSTRAINT DF_SQLExecutionGroup_UserName DEFAULT (SUSER_SNAME()))
GO
ALTER TABLE SE.SQLExecutionGroup ADD CONSTRAINT SQLExecutionGroup_PKC PRIMARY KEY CLUSTERED(ExecutionGroupId)
GO
CREATE TABLE SE.SQLExecutionTracker (ExecutionId INT NOT NULL,
StatusId TINYINT NOT NULL,
SessionId SMALLINT NOT NULL,
RowCnt INT NOT NULL
CONSTRAINT DF_SQLExecutionTracker_RowCnt DEFAULT 0,
RolledBack BIT NOT NULL
CONSTRAINT DF_SQLExecutionTracker_RolledBack DEFAULT (0),
QueName VARCHAR(256) NULL,
DateAdded DATETIME2(2) NOT NULL
CONSTRAINT DF_SQLExecutionTracker_DateAdded DEFAULT (GETDATE()),
ErrorNumber INT NULL,
ErrorSeverity INT NULL,
ErrorState INT NULL,
ErrorProcedure VARCHAR(256) NULL,
ErrorLine INT NULL,
ErrorMessage VARCHAR(8000) NULL)
GO
ALTER TABLE SE.SQLExecutionTracker ADD CONSTRAINT SQLExecutionTracker_PKC PRIMARY KEY CLUSTERED(ExecutionId, StatusId)
GO
CREATE TABLE SE.SQLExecution (ExecutionId INT IDENTITY
NOT NULL,
ExecutionGroupId INT NULL,
[Description] VARCHAR(256) NULL,
UseTransaction BIT NOT NULL
CONSTRAINT DF_SQLExecution_UseTransaction DEFAULT (0),
DatabaseName VARCHAR(256) NOT NULL,
CommandText VARCHAR(MAX) NOT NULL,
DateAdded DATETIME2(2) NOT NULL
CONSTRAINT DF_SQLExecution_DateAdded DEFAULT (GETDATE()),
UserName VARCHAR(256) NOT NULL
CONSTRAINT DF_SQLExecution_UserName DEFAULT (SUSER_SNAME()))
GO
ALTER TABLE SE.SQLExecution ADD CONSTRAINT SQLExecution_PKC PRIMARY KEY CLUSTERED(ExecutionId) WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE)
GO
-- ALTER TABLE SE.SQLExecution ADD CONSTRAINT SQLExecution_SQLExecutionGroup_FK FOREIGN KEY (ExecutionGroupId)REFERENCES SE.SQLExecutionGroup(ExecutionGroupId)
GO
CREATE INDEX ExecutionGroupId ON SE.SQLExecution (ExecutionGroupId) WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE)
GO
--#endregion
GO
IF OBJECT_ID('dbo.GetNumbers') IS NOT NULL
DROP FUNCTION dbo.GetNumbers
GO
CREATE FUNCTION dbo.GetNumbers (@StartVal INT,
@EndVal INT)
RETURNS @Digits TABLE (digit INT)
AS
BEGIN
;
WITH N0
AS (SELECT n
FROM ( VALUES ( 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), ( 65), ( 66), ( 67), ( 68), ( 69), ( 70), ( 71), ( 72), ( 73), ( 74), ( 75), ( 76),
( 77), ( 78), ( 79), ( 80), ( 81), ( 82), ( 83), ( 84), ( 85), ( 86), ( 87), ( 88), ( 89), ( 90), ( 91), ( 92), ( 93), ( 94), ( 95),
( 96), ( 97), ( 98), ( 99), ( 100), ( 101), ( 102), ( 103), ( 104), ( 105), ( 106), ( 107), ( 108), ( 109), ( 110), ( 111), ( 112),
( 113), ( 114), ( 115), ( 116), ( 117), ( 118), ( 119), ( 120), ( 121), ( 122), ( 123), ( 124), ( 125), ( 126), ( 127), ( 128),
( 129), ( 130), ( 131), ( 132), ( 133), ( 134), ( 135), ( 136), ( 137), ( 138), ( 139), ( 140), ( 141), ( 142), ( 143), ( 144),
( 145), ( 146), ( 147), ( 148), ( 149), ( 150), ( 151), ( 152), ( 153), ( 154), ( 155), ( 156), ( 157), ( 158), ( 159), ( 160),
( 161), ( 162), ( 163), ( 164), ( 165), ( 166), ( 167), ( 168), ( 169), ( 170), ( 171), ( 172), ( 173), ( 174), ( 175), ( 176),
( 177), ( 178), ( 179), ( 180), ( 181), ( 182), ( 183), ( 184), ( 185), ( 186), ( 187), ( 188), ( 189), ( 190), ( 191), ( 192),
( 193), ( 194), ( 195), ( 196), ( 197), ( 198), ( 199), ( 200), ( 201), ( 202), ( 203), ( 204), ( 205), ( 206), ( 207), ( 208),
( 209), ( 210), ( 211), ( 212), ( 213), ( 214), ( 215), ( 216), ( 217), ( 218), ( 219), ( 220), ( 221), ( 222), ( 223), ( 224),
( 225), ( 226), ( 227), ( 228), ( 229), ( 230), ( 231), ( 232), ( 233), ( 234), ( 235), ( 236), ( 237), ( 238), ( 239), ( 240),
( 241), ( 242), ( 243), ( 244), ( 245), ( 246), ( 247), ( 248), ( 249), ( 250), ( 251), ( 252), ( 253), ( 254), ( 255), ( 256) ) AS ddata (n)),
N1
AS (SELECT 1 AS n
FROM N0 A
CROSS JOIN N0 B),
N2
AS (SELECT 1 AS n
FROM N1 A
CROSS JOIN N1 B),
Nums(n)
AS (SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM N2)
INSERT @Digits
(digit)
SELECT n + @StartVal - 1 AS Digit
FROM Nums
WHERE n BETWEEN 1 AND (@EndVal - @StartVal + 1) ;
RETURN
END
GO
--#region SQLExecutionGroupInsert
CREATE PROCEDURE SE.SQLExecutionGroupInsert
@ExecutionGroupId INT = NULL OUTPUT,
@Description VARCHAR(256),
@UseTransaction BIT = 0
AS
SET NOCOUNT ON
INSERT [SE].[SQLExecutionGroup]
([Description], UseTransaction)
VALUES (@Description, @UseTransaction)
SET @ExecutionGroupId = SCOPE_IDENTITY()
GO
--#endregion
GO
--#region usp_SQLExecutionInsert
GO
CREATE PROCEDURE [SE].[usp_SQLExecutionInsert]
@ExecutionGroupId INT = NULL OUTPUT,
@ExecutionGroupDescription VARCHAR(256) = NULL,
@ExecutionInt INT = NULL OUTPUT,
@Description VARCHAR(256) = NULL,
@UseTransaction BIT = 0,
@DatabaseName VARCHAR(256),
@CommandText VARCHAR(MAX)
AS
SET NOCOUNT ON
IF @ExecutionGroupId IS NULL
AND @ExecutionGroupDescription <> ''
EXEC SE.SQLExecutionGroupInsert
@ExecutionGroupId = @ExecutionGroupId OUTPUT,
@Description = @ExecutionGroupDescription
INSERT INTO [SE].[SQLExecution]
([ExecutionGroupId],
[Description],
[UseTransaction],
[DatabaseName],
[CommandText])
SELECT @ExecutionGroupId,
@Description,
@UseTransaction,
@DatabaseName,
@CommandText
SET @ExecutionInt = SCOPE_IDENTITY()
GO
--#endregion
--#region SE.SQLExecutionExecProc
GO
CREATE PROCEDURE SE.SQLExecutionExecProc
@ExecutionId INT,
@QueName VARCHAR(256)
AS
SET NOCOUNT ON
DECLARE @UseTransaction TINYINT,
@GroupTransaction TINYINT,
@ExecutionGroupId INT,
@DatabaseName VARCHAR(256),
@CommandText VARCHAR(MAX),
@SQL VARCHAR(MAX),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorProcedure VARCHAR(256),
@ErrorLine INT,
@ErrorMessage VARCHAR(8000),
@RowCnt INT = 0,
@FailedCount INT,
@InitialRunningCount INT,
@CommitWait BIT = 0,
@RolledBack BIT = 0 ;
IF EXISTS ( SELECT *
FROM SE.SQLExecutionTracker (NOLOCK)
WHERE ExecutionId = @ExecutionId )
RETURN 50
BEGIN TRY
INSERT SE.SQLExecutionTracker
(ExecutionId,
StatusId,
SessionId,
QueName,
RolledBack)
VALUES (@ExecutionId,
20,
@@SPID,
@QueName,
@RolledBack)
IF @@ROWCOUNT <> 1
RETURN 80
END TRY
BEGIN CATCH
RETURN 40
END CATCH
SELECT @GroupTransaction = ISNULL(g.UseTransaction, 0),
@ExecutionGroupId = g.ExecutionGroupId,
@UseTransaction = CASE WHEN g.UseTransaction = 1 THEN 0
ELSE t.UseTransaction
END,
@DatabaseName = t.DatabaseName,
@CommandText = t.CommandText
FROM SE.SQLExecution t (NOLOCK)
LEFT OUTER JOIN SE.SQLExecutionGroup g (NOLOCK) ON g.ExecutionGroupId = t.ExecutionGroupId
AND t.ExecutionGroupId IS NOT NULL
WHERE t.ExecutionId = @ExecutionId
IF @@ROWCOUNT <> 1
RETURN 30
DECLARE @ContextBin VARBINARY(128) = CAST(@ExecutionId AS VARBINARY(128))
SET CONTEXT_INFO @ContextBin
SET @SQL = 'USE ' + @DatabaseName + '
EXEC(''' + REPLACE(@CommandText, '''', '''''') + ''')'
BEGIN TRY
IF @UseTransaction = 1
OR @GroupTransaction = 1
BEGIN TRANSACTION
EXEC(@SQL)
SET @RowCnt = @@ROWCOUNT
IF @UseTransaction = 1
AND @GroupTransaction = 0
AND @@TRANCOUNT > 0
COMMIT TRANSACTION
IF @GroupTransaction = 0
BEGIN
INSERT SE.SQLExecutionTracker
(ExecutionId,
RowCnt,
StatusId,
SessionId,
QueName,
RolledBack)
VALUES (@ExecutionId,
@RowCnt,
40,
@@SPID,
@QueName,
@RolledBack)
SET CONTEXT_INFO 0x
RETURN
END
IF @ExecutionGroupId IS NOT NULL
BEGIN
WHILE 1 = 1
BEGIN
SELECT @FailedCount = ISNULL(SUM(CASE WHEN ISNULL(b.StatusId, 10) = 30 THEN 1
ELSE 0
END), 0),
@InitialRunningCount = ISNULL(SUM(CASE WHEN ISNULL(b.StatusId, 10) IN (10, 20) THEN 1
ELSE 0
END), 0)
FROM SE.SQLExecution a (NOLOCK)
OUTER APPLY (SELECT TOP 1
b.StatusId
FROM SE.SQLExecutionTracker b (NOLOCK)
WHERE a.ExecutionId = b.ExecutionId
ORDER BY StatusId DESC) b
WHERE ExecutionGroupId = @ExecutionGroupId
AND ExecutionId <> @ExecutionId
AND b.StatusId <> 40
IF @FailedCount > 0
BEGIN
IF @GroupTransaction = 1
AND @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
SET @RolledBack = 1
INSERT SE.SQLExecutionTracker
(ExecutionId,
RowCnt,
StatusId,
SessionId,
QueName,
RolledBack)
VALUES (@ExecutionId,
@RowCnt,
40,
@@SPID,
@QueName,
@RolledBack)
END
BREAK
END
IF @InitialRunningCount > 0
AND @GroupTransaction = 1
AND @@TRANCOUNT > 0
BEGIN
IF @CommitWait = 0
BEGIN
SET @CommitWait = 1
INSERT SE.SQLExecutionTracker
(ExecutionId,
RowCnt,
StatusId,
SessionId,
QueName,
RolledBack)
VALUES (@ExecutionId,
@RowCnt,
35,
@@SPID,
@QueName,
@RolledBack)
END
WAITFOR DELAY '00:00:01'
CONTINUE
END
IF @InitialRunningCount = 0
BEGIN
IF @GroupTransaction = 1
AND @@TRANCOUNT > 0
COMMIT TRANSACTION
INSERT SE.SQLExecutionTracker
(ExecutionId,
RowCnt,
StatusId,
SessionId,
QueName,
RolledBack)
VALUES (@ExecutionId,
@RowCnt,
40,
@@SPID,
@QueName,
@RolledBack)
BREAK
END
END
END
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE()
IF (@UseTransaction = 1
OR @GroupTransaction = 1)
AND @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
SET @RolledBack = 1
END
INSERT SE.SQLExecutionTracker
(ExecutionId,
RowCnt,
StatusId,
SessionId,
QueName,
RolledBack,
ErrorNumber,
ErrorSeverity,
ErrorState,
ErrorProcedure,
ErrorLine,
ErrorMessage)
VALUES (@ExecutionId,
@RowCnt,
30,
@@SPID,
@QueName,
@RolledBack,
@ErrorNumber,
@ErrorSeverity,
@ErrorState,
@ErrorProcedure,
@ErrorLine,
@ErrorMessage)
END CATCH
SET CONTEXT_INFO 0x
GO
--#endregion
--#region SQLExecutionKillAll
GO
CREATE PROCEDURE SE.SQLExecutionKillAll
@ExecutionGroupId INT = NULL,
@ExecutionId INT = NULL,
@PrintSQL BIT = 1,
@ExecSQL BIT = 0
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(MAX) = ''
SELECT @SQL = @SQL + 'KILL ' + CAST(ex.session_id AS VARCHAR) + '
'
FROM (SELECT DISTINCT
t.ExecutionId,
t.SessionId
FROM SE.SQLExecutionTracker t (NOLOCK)
WHERE t.ExecutionId IN (SELECT ExecutionID
FROM SE.SQLExecution e (NOLOCK)
WHERE e.ExecutionGroupId = @ExecutionGroupId
AND @ExecutionGroupId IS NOT NULL)
OR t.ExecutionId = @ExecutionId) a
CROSS APPLY (SELECT TOP 1
*
FROM sys.dm_exec_requests ex (NOLOCK)
WHERE ex.session_id = a.SessionId
AND ex.CONTEXT_INFO = CAST(a.ExecutionId AS VARBINARY(128))) ex
IF @PrintSQL = 1
PRINT @SQL
IF @ExecSQL = 1
AND @SQL <> ''
EXEC(@SQL )
GO
--#endregion
--#region SQLExecutionTrackerPivot
GO
CREATE VIEW SE.SQLExecutionTrackerPivot
AS
SELECT ExecutionId,
MAX(SessionId) AS SessionId,
MAX(QueName) AS QueName,
(SELECT Description FROM SE.SQLExecutionStatus s (NOLOCK) WHERE s.StatusId = MAX (a.StatusId)) AS [Status],
NULLIF(MAX(ISNULL(CASE WHEN StatusId = 20 THEN DateAdded
END, '19000101')), '19000101') AS SessionStart,
NULLIF(MAX(ISNULL(CASE WHEN StatusId = 35 THEN DateAdded
END, '19000101')), '19000101') AS CommitWait,
NULLIF(MAX(ISNULL(CASE WHEN StatusId IN (30, 40) THEN DateAdded
END, '19000101')), '19000101') AS SessionEnd,
MAX(RowCnt) AS RowCnt,
MAX(a.StatusId) AS StatusId,
MAX(CAST(RolledBack AS TINYINT)) AS RolledBack,
NULLIF(MAX(ISNULL(CASE WHEN StatusId = 30 THEN ErrorNumber
END, 0)), 0) AS ErrorNumber,
NULLIF(MAX(ISNULL(CASE WHEN StatusId = 30 THEN ErrorSeverity
END, 0)), 0) AS ErrorSeverity,
NULLIF(MAX(ISNULL(CASE WHEN StatusId = 30 THEN ErrorState
END, 0)), 0) AS ErrorState,
NULLIF(MAX(ISNULL(CASE WHEN StatusId = 30 THEN ErrorProcedure
END, '')), '') AS ErrorProcedure,
NULLIF(MAX(ISNULL(CASE WHEN StatusId = 30 THEN ErrorLine
END, 0)), 0) AS ErrorLine,
NULLIF(MAX(ISNULL(CASE WHEN StatusId = 30 THEN ErrorMessage
END, '')), '') AS ErrorMessage
FROM SE.SQLExecutionTracker a (NOLOCK)
WHERE StatusId IN (20, 30, 35, 40)
GROUP BY ExecutionId
GO
--#endregion
--#region SQLExecutionAll
GO
CREATE VIEW SE.SQLExecutionAll
AS
SELECT e.ExecutionId,
g.Description AS GroupDescription,
g.UseTransaction AS GroupTransaction,
e.ExecutionGroupId,
e.Description,
ex.session_id AS SessionId,
b.SessionId AS OrigSessionId,
NULLIF(ex.blocking_session_id, 0) AS Blocked,
b.SessionStart,
b.CommitWait,
b.SessionEnd,
DATEDIFF(SECOND, b.SessionStart, ISNULL(b.SessionEnd, GETDATE())) AS SessionDur,
DATEDIFF(SECOND, e.DateAdded, b.SessionStart) AS SessionDelay,
b.Status,
b.RowCnt,
b.RolledBack,
b.StatusId,
e.DatabaseName,
b.ErrorNumber,
b.ErrorSeverity,
b.ErrorState,
b.ErrorProcedure,
b.ErrorLine,
b.ErrorMessage,
e.DateAdded,
e.UserName,
e.UseTransaction,
b.QueName,
e.CommandText,
(SELECT 1 AS tag, NULL AS parent, CHAR (13) + e.CommandText + CHAR (13) AS [Q!1!Q!CDATA]
FOR
XML EXPLICIT,
TYPE) AS CommandXml
FROM SE.SQLExecution e (NOLOCK)
LEFT OUTER JOIN SE.SQLExecutionTrackerPivot b (NOLOCK) ON e.ExecutionId = b.ExecutionId
LEFT OUTER JOIN SE.SQLExecutionGroup g (NOLOCK) ON e.ExecutionGroupId = g.ExecutionGroupId
OUTER APPLY (SELECT TOP 1
*
FROM sys.dm_exec_requests ex (NOLOCK)
WHERE ex.session_id = b.SessionId
AND ex.CONTEXT_INFO = CAST(e.ExecutionId AS VARBINARY(128))) ex
GO
--#endregion
--#region SQLExecutionGetGroup
GO
CREATE FUNCTION SE.SQLExecutionGetGroup (@ExecutionGroupId INT)
RETURNS TABLE
AS
RETURN
SELECT *
FROM SE.SQLExecutionAll (NOLOCK)
WHERE ExecutionGroupId = @ExecutionGroupId
GO
--#endregion
--#region SQLExecutionActive
GO
CREATE VIEW SE.SQLExecutionActive
AS
SELECT *
FROM SE.SQLExecutionAll
WHERE StatusId IN (20, 35)
GO
--#endregion
--#region SQLExecutionWait
GO
CREATE PROCEDURE SE.SQLExecutionWait
@ExecutionGroupId INT = NULL,
@ExecutionId INT = NULL,
@InitialCount INT = NULL OUTPUT,
@RunningCount INT = NULL OUTPUT,
@ActiveSessionCount INT = 0 OUTPUT,
@CommitWaitCount INT = NULL OUTPUT,
@ErrorCount INT = NULL OUTPUT,
@CompleteCount INT = NULL OUTPUT,
@SumRowCnt INT = NULL OUTPUT,
@DistRowCnt INT = NULL OUTPUT,
@Duration INT = NULL OUTPUT,
@TotalDuration INT = NULL OUTPUT,
@ErrorReport VARCHAR(MAX) = NULL OUTPUT,
@TimeOut INT = 7200,
@GenerateErrorOnTimeout BIT = 0,
@SelectResults BIT = 0,
@PrintError BIT = 0,
@PrintStatus BIT = 0
AS
SET NOCOUNT ON
DECLARE @Status VARCHAR(MAX),
@TimeStart DATETIME2(2) = GETDATE(),
@TimeoutTime DATETIME2(2) = CASE WHEN @TimeOut = 0 THEN DATEADD(DAY, 1, GETDATE())
ELSE DATEADD(SECOND, @TimeOut, GETDATE())
END
IF ABS(SIGN(ISNULL(@ExecutionGroupId, 0))) + ABS(SIGN(ISNULL(@ExecutionId, 0))) <> 1
BEGIN
RAISERROR('Only one of @ExecutionGroupId %d and @ExecutionId %d can be populated!', 16, 1, @ExecutionGroupId, @ExecutionId)
RETURN 30
END
DECLARE @ExecutionIdList TABLE (ExecutionId INT NOT NULL
PRIMARY KEY CLUSTERED)
INSERT @ExecutionIdList
(ExecutionId)
SELECT ExecutionId
FROM SE.SQLExecution (NOLOCK)
WHERE @ExecutionId IS NOT NULL
AND ExecutionId = @ExecutionId
UNION
SELECT ExecutionId
FROM SE.SQLExecution (NOLOCK)
WHERE @ExecutionGroupId IS NOT NULL
AND ExecutionGroupId = @ExecutionGroupId
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('Could not find @ExecutionGroupId %d or @ExecutionId %d in SE.SQLExecution table!', 16, 1, @ExecutionGroupId, @ExecutionId)
RETURN 35
END
WHILE GETDATE() < @TimeoutTime
BEGIN
SELECT @InitialCount = ISNULL(SUM(CASE WHEN ISNULL(StatusId, 10) = 10 THEN 1
ELSE 0
END), 0),
@RunningCount = ISNULL(SUM(CASE WHEN StatusId = 20 THEN 1
ELSE 0
END), 0),
@CommitWaitCount = ISNULL(SUM(CASE WHEN StatusId = 35 THEN 1
ELSE 0
END), 0),
@ErrorCount = ISNULL(SUM(CASE WHEN StatusId = 30 THEN 1
ELSE 0
END), 0),
@CompleteCount = ISNULL(SUM(CASE WHEN StatusId = 40 THEN 1
ELSE 0
END), 0),
@SumRowCnt = ISNULL(SUM(ISNULL(RowCnt, 0)), 0),
@DistRowCnt = ISNULL(SUM(CASE WHEN RowCnt > 0 THEN 1
ELSE 0
END), 0),
@ActiveSessionCount = SUM(CASE WHEN ex.session_id > 0 THEN 1
ELSE 0
END)
FROM SE.SQLExecution (NOLOCK) a
OUTER APPLY (SELECT TOP 1
b.StatusId,
b.SessionId,
b.RowCnt
FROM SE.SQLExecutionTracker b (NOLOCK)
WHERE a.ExecutionId = b.ExecutionId
ORDER BY StatusId DESC) t
INNER JOIN @ExecutionIdList b ON a.ExecutionId = b.ExecutionId
OUTER APPLY (SELECT *
FROM sys.dm_exec_requests ex (NOLOCK)
WHERE ex.session_id = t.SessionId
AND ex.CONTEXT_INFO = CAST(a.ExecutionId AS VARBINARY(128))) ex
IF (@InitialCount + @RunningCount + @CommitWaitCount = 0)
OR (@ActiveSessionCount = 0
AND DATEDIFF(SECOND, @TimeStart, GETDATE()) > 5)
BREAK
IF @PrintStatus = 1
BEGIN
SET @Status = SUBSTRING(CASE WHEN @InitialCount > 0 THEN ', Initial ' + CAST(@InitialCount AS VARCHAR)
ELSE ''
END + CASE WHEN @RunningCount > 0 THEN ', Running ' + CAST(@RunningCount AS VARCHAR)
ELSE ''
END + CASE WHEN @ActiveSessionCount > 0 THEN ', Active Connections ' + CAST(@ActiveSessionCount AS VARCHAR)
ELSE ''
END + CASE WHEN @ErrorCount > 0 THEN ', Failed ' + CAST(@ErrorCount AS VARCHAR)
ELSE ''
END + CASE WHEN @CommitWaitCount > 0 THEN ', Commit Wait ' + CAST(@CommitWaitCount AS VARCHAR)
ELSE ''
END + CASE WHEN @CompleteCount > 0 THEN ', Completed ' + CAST(@CompleteCount AS VARCHAR)
ELSE ''
END + CASE WHEN @SumRowCnt > 0 THEN ', TotalRows ' + CAST(@SumRowCnt AS VARCHAR)
ELSE ''
END + CASE WHEN @DistRowCnt > 0
THEN ', Processes with Rows ' + CAST(@DistRowCnt AS VARCHAR)
ELSE ''
END, 3, 500)
PRINT @Status
END
WAITFOR DELAY '00:00:01'
END
SELECT @InitialCount = ISNULL(SUM(CASE WHEN ISNULL(StatusId, 10) = 10 THEN 1
ELSE 0
END), 0),
@RunningCount = ISNULL(SUM(CASE WHEN StatusId = 20 THEN 1
ELSE 0
END), 0),
@CommitWaitCount = ISNULL(SUM(CASE WHEN StatusId = 35 THEN 1
ELSE 0
END), 0),
@ErrorCount = ISNULL(SUM(CASE WHEN StatusId = 30 THEN 1
ELSE 0
END), 0),
@CompleteCount = ISNULL(SUM(CASE WHEN StatusId = 40 THEN 1
ELSE 0
END), 0),
@SumRowCnt = ISNULL(SUM(ISNULL(RowCnt, 0)), 0),
@DistRowCnt = ISNULL(SUM(CASE WHEN RowCnt > 0 THEN 1
ELSE 0
END), 0),
@ActiveSessionCount = SUM(CASE WHEN ex.session_id > 0 THEN 1
ELSE 0
END),
@Duration = ISNULL(DATEDIFF(SECOND, MIN(a.DateAdded), MAX(sd.SessionEnd)), 0),
@TotalDuration = ISNULL(SUM(ISNULL(DATEDIFF(SECOND, SessionStart, SessionEnd), 0)), 0)
FROM SE.SQLExecution a (NOLOCK)
LEFT OUTER JOIN SE.SQLExecutionTrackerPivot sd (NOLOCK) ON sd.ExecutionId = a.ExecutionId
INNER JOIN @ExecutionIdList l ON a.ExecutionId = l.ExecutionId
OUTER APPLY (SELECT *
FROM sys.dm_exec_requests ex (NOLOCK)
WHERE ex.session_id = sd.SessionId
AND ex.CONTEXT_INFO = CAST(a.ExecutionId AS VARBINARY(128))) ex
SELECT @ErrorReport = ISNULL(@ErrorReport, '') + ISNULL('ErrorNumber: ' + CAST(ErrorNumber AS VARCHAR) + '
ErrorSeverity: ' + ISNULL(CAST(ErrorSeverity AS VARCHAR), 'NULL') + '
ErrorState: ' + ISNULL(CAST(ErrorState AS VARCHAR), 'NULL') + '
ErrorProcedure: ' + ISNULL(ErrorProcedure, 'NULL') + '
ErrorLine: ' + ISNULL(CAST(ErrorLine AS VARCHAR), 'NULL') + '
ErrorMessage: ' + ErrorMessage + '
Command: ' + CommandText + '
--------------------------------------------------
', '')
FROM SE.SQLExecution a (NOLOCK)
LEFT OUTER JOIN SE.SQLExecutionTrackerPivot sd (NOLOCK) ON sd.ExecutionId = a.ExecutionId
INNER JOIN @ExecutionIdList l ON a.ExecutionId = l.ExecutionId
OUTER APPLY (SELECT *
FROM sys.dm_exec_requests ex (NOLOCK)
WHERE ex.session_id = sd.SessionId
AND ex.CONTEXT_INFO = CAST(a.ExecutionId AS VARBINARY(128))) ex
IF GETDATE() >= @TimeoutTime
AND @GenerateErrorOnTimeout = 1
AND (@InitialCount + @RunningCount + @CommitWaitCount) > 0
BEGIN
RAISERROR('There are still Initial, Running or Commit wait processes!', 16, 1)
RETURN 40
END
IF @ActiveSessionCount = 0
AND (@InitialCount + @RunningCount + @CommitWaitCount) > 0
BEGIN
RAISERROR('There are no active sessions but pending requests. ActiveSessionCount %d, InitialCount %d, RunningCount %d CommitWaitCount %d', 16, 1, @ActiveSessionCount,@InitialCount,@RunningCount,@CommitWaitCount)
RETURN 45
END
IF @SelectResults = 1
SELECT *
FROM SE.SQLExecutionAll a (NOLOCK)
INNER JOIN @ExecutionIdList b ON a.ExecutionId = b.ExecutionId
IF @PrintError = 1
AND @ErrorReport <> ''
PRINT @ErrorReport
GO
--#endregion
GO
--#region SQLExecutionCleanup
CREATE PROCEDURE SE.SQLExecutionCleanup
@DaysToKeepHistory TINYINT = 4
AS
DECLARE @DeleteBefore DATETIME = DATEADD(DAY, -1 * @DaysToKeepHistory, GETDATE())
DELETE e
FROM SE.SQLExecution e
WHERE DateAdded < @DeleteBefore
AND NOT EXISTS ( SELECT *
FROM SE.SQLExecutionTracker a
WHERE a.StatusId = 20
AND a.ExecutionId = e.ExecutionId
AND NOT EXISTS ( SELECT *
FROM SE.SQLExecutionTracker b
WHERE a.ExecutionId = b.ExecutionId
AND b.StatusId IN (30, 40) ) )
DELETE g
FROM SE.SQLExecutionGroup g
WHERE NOT EXISTS ( SELECT *
FROM SE.SQLExecution e
WHERE e.ExecutionGroupId = g.ExecutionGroupId )
AND DateAdded < @DeleteBefore
DELETE g
FROM SE.SQLExecutionTracker g
WHERE NOT EXISTS ( SELECT *
FROM SE.SQLExecution e
WHERE e.ExecutionId = g.ExecutionId )
AND DateAdded < @DeleteBefore
GO
--#endregion