USE Util
GO
--#region Drop Objects
DROP PROCEDURE [SE].[usp_SQLExecutionInsert]
GO
DROP PROCEDURE SE.SQLExecutionGroupInsert
GO
DROP PROCEDURE SE.SQLExecutionExecProc
GO
DROP TABLE SE.SQLExecutionTracker
GO
DROP TABLE SE.SQLExecution
GO
DROP TABLE SE.SQLExecutionGroup
GO
DROP TABLE SE.SQLExecutionStatus
GO
DROP SCHEMA SE
--#endregion
GO
--#region Create Tables
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
--#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
DROP PROCEDURE SE.SQLExecutionExecProc
GO
CREATE PROCEDURE SE.SQLExecutionExecProc
@ExecutionId INT,
@QueName VARCHAR(256)
-- WITH EXECUTE AS CALLER
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
DROP PROCEDURE SE.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 = dbo.StringConcat(DISTINCT '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
EXEC dbo.PrintLargeText
@SQL
IF @ExecSQL = 1
AND @SQL <> ''
EXEC(@SQL )
GO
--#endregion
--#region SQLExecutionTrackerPivot
DROP VIEW SE.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
DROP VIEW SE.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
DROP FUNCTION SE.SQLExecutionGetGroup
GO
CREATE FUNCTION SE.SQLExecutionGetGroup (@ExecutionGroupId INT)
RETURNS TABLE
AS
RETURN
SELECT *
FROM SE.SQLExecutionAll (NOLOCK)
WHERE ExecutionGroupId = @ExecutionGroupId
GO
--#endregion
--#region SQLExecutionActive
DROP VIEW SE.SQLExecutionActive
GO
CREATE VIEW SE.SQLExecutionActive
AS
SELECT *
FROM SE.SQLExecutionAll
WHERE StatusId IN (20, 35)
GO
--#endregion
--#region SQLExecutionWait
DROP PROCEDURE SE.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,
@Raiserror BIT = 0,
@TimeOutTillStartRunning INT = 300
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()) > @TimeOutTillStartRunning)
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),
@ErrorReport = dbo.StringConcat('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 <> ''
EXEC dbo.PrintLargeText
@ErrorReport
IF @Raiserror = 1
AND @ErrorReport <> ''
RAISERROR(@ErrorReport,16,1)
GO
--#endregion
GO
--#region SQLExecutionCleanup
DROP PROCEDURE SE.SQLExecutionCleanup
GO
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
--#region SQLExecWait
DROP PROCEDURE SE.SQLExecWait
go
CREATE PROCEDURE SE.SQLExecWait
@GroupName VARCHAR(256),
@DatabaseName VARCHAR(256),
@ExecListGOTerminated VARCHAR(MAX),
@GroupTransaction BIT = 0,
@ErrorReport VARCHAR(MAX) = NULL OUTPUT,
@Raiserror BIT = 1,
@PrintError BIT = 1,
@SetContextInfo BIT = 1
AS
SET NOCOUNT ON
INSERT SE.SQLExecutionGroup
(Description, UseTransaction)
VALUES (@GroupName, @GroupTransaction)
DECLARE @ExecutionGroupId INT = SCOPE_IDENTITY(),
@CompleteCount INT,
@RowCount INT
IF @SetContextInfo = 1
SET CONTEXT_INFO @ExecutionGroupId
INSERT SE.SQLExecution
(ExecutionGroupId,
Description,
DatabaseName,
CommandText)
SELECT @ExecutionGroupId AS ExecutionGroupId,
@GroupName + ' ' + CAST(fieldnum AS VARCHAR) AS Description,
@DatabaseName AS DatabaseName,
Field AS CommandText
FROM dbo.ParseDelimited(@ExecListGOTerminated, '
GO
')
SET @RowCount = @@ROWCOUNT
EXEC SE.SQLExecutionWait
@ExecutionGroupId = @ExecutionGroupId,
@CompleteCount = @CompleteCount OUTPUT,
@ErrorReport = @ErrorReport OUTPUT,
@Raiserror = @Raiserror,
@PrintError = @PrintError
IF @CompleteCount <> @RowCount
BEGIN
RAISERROR('Invalid Completecount %d from SQLExecutionWait, should be %d', 16, 1, @CompleteCount, @RowCount)
RETURN 40
END
GO
--#endregion