I know that for many this won't be of value, because your systems are too busy for this to make sense and I also know that a new process could start while I'm running one of my statements, so this isn't fool-proof by any means.
-- ========================================================================
-- Author: Eric Zierdt
-- Create date: 9/20/2016
-- Description: Checks database for running processes and Runs
-- series of statements when free and emails when complete
-- ========================================================================
/*********************************************************************
** CODE TO CREATE THE TABLE TO STORE THE QUERIES YOU WANT TO RUN **
*********************************************************************/
/*
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..##TABLE'))
BEGIN
DROP TABLE ##TABLE
END
CREATE TABLE ##TABLE (ID INT IDENTITY(1,1), Code VARCHAR(MAX),Complete BIT DEFAULT 0)
INSERT INTO ##TABLE
( Code )
VALUES
('UPDATE STATISTICS [Table] [Stat] WITH FULLSCAN')
*/
SET NOCOUNT ON
/*****************************************************************************************
** CREATING ##StartTime TABLE TO BE USED IN REMAINING TIME ESTIMATES IN OTHER QUERY. **
** SETTING TO GLOBAL TEMP TABLE SO ACCESSABLE IN ANOTHER WINDOW **
*****************************************************************************************/
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..##StartTime'))
BEGIN
DROP TABLE ##StartTime
END
CREATE TABLE ##StartTime (StartTime DATETIME)
INSERT INTO ##StartTime VALUES ( GETDATE() )
PRINT 'Start Time: ' + CONVERT(VARCHAR(25), GETDATE(), 101) + ' ' + CONVERT(VARCHAR(25), GETDATE(), 108)
GO
/*****************************************************************************************
** CREATING ##StartTime TABLE TO BE USED IN REMAINING TIME ESTIMATES IN OTHER QUERY. **
** SETTING TO GLOBAL TEMP TABLE SO ACCESSABLE IN ANOTHER WINDOW **
*****************************************************************************************/
DECLARE @ID INT
,@SQL VARCHAR(MAX)
,@StartTime DATETIME = GETDATE()
,@EndTime DATETIME
WHILE
(
SELECT COUNT(1)
FROM ##TABLE
WHERE Complete = 0
) >= 1
BEGIN
WHILE
(
/**************************************************************
** CHECKING TO SEE IF DATABASE HAS NO 'RUNNABLE' PROCESSES **
**************************************************************/
SELECT COUNT(1)
FROM sys.sysprocesses S
WHERE status = 'runnable'
AND DB_NAME(S.dbid) = DB_NAME()
AND S.spid <> @@SPID
) > 0
BEGIN
WAITFOR DELAY '00:00:05'; ---CHANGE TO MEET YOUR NEEDS; THIS IS SAYING, RETRY EVERY 5 SECONDS TO SEE IF DATABASE IS FREE TO DO WORK
END
/*************************************************
** NO 'RUNNING' PROCESSES DETECTED, RUN QUERY **
*************************************************/
SELECT TOP 1 @ID=ID, @SQL=Code FROM ##TABLE WHERE Complete = 0
EXEC(@SQL)
UPDATE ##TABLE SET Complete = 1 WHERE ID = @ID
END
/*********************************************
** SET END TIME AND PRINT RUN TIME STATS **
*********************************************/
SET @EndTime = GETDATE()
PRINT 'End Time: ' + CONVERT(VARCHAR(25), @EndTime, 101) + ' ' + CONVERT(VARCHAR(25), @EndTime, 108)
PRINT 'Run Time: ' + CONVERT(VARCHAR, DATEADD(ms,DATEDIFF(ms,@StartTime,@EndTime),0),108)
/*************
** CLEANUP **
*************/
DROP TABLE ##TABLE
DROP TABLE ##StartTime
/*********************************************
** SEND EMAIL NOTIFICATION OF COMPLETION **
*********************************************/
DECLARE @buffer_memory_used_MB FLOAT
,@Body VARCHAR(MAX)
,@Subject VARCHAR(255) = 'Auto Run Script Complete'
,@To VARCHAR(512) = ''
SET @Body = 'The script has completed processing queries.
Run Time: ' + CONVERT(VARCHAR, DATEADD(ms,DATEDIFF(ms,@StartTime,@EndTime),0),108) + ' '
EXEC msdb.dbo.sp_send_dbmail
@recipients = @To
, @subject = @Subject
, @body = @Body
, @body_format = 'HTML'
In another window I will often run this on occasion to get some stats and an idea of how long it will run for:
DECLARE @Completed FLOAT ,@CompletedStatements FLOAT ,@Total FLOAT ,@TotalStatements FLOAT ,@PercentComplete FLOAT ,@StartDate DATETIME SELECT @StartDate=StartTime FROM ##StartTime DECLARE @RunTime BIGINT = DATEDIFF(SECOND,@StartDate,GETDATE()) SELECT @CompletedStatements = COUNT(1) FROM ##Table WHERE fixed = 1 SELECT @TotalStatements = COUNT(1) FROM ##Table SELECT @Completed = SUM([RowCount]) FROM ##Table WHERE fixed = 1 SELECT @Total = SUM([RowCount]) FROM ##Table SELECT @PercentComplete = 100*(@Completed/@Total) DECLARE @SecondPerRecord FLOAT = CAST(@RunTime AS FLOAT)/@Completed DECLARE @EstRemainingSeconds BIGINT = @SecondPerRecord*(@Total-@Completed) SELECT @Total TotalRecords, @Completed CompletedRecords, @TotalStatements as TotalStatements, @CompletedStatements as CompletedStatements, @PercentComplete [% Complete], @RunTime RunTimeSeconds, @SecondPerRecord SecondPerRecord, @EstRemainingSeconds EstRemainingSeconds ,@EstRemainingSeconds/60.0 EstRemainingMins, @EstRemainingSeconds/3600.0 EstRemainingHoursPlease comment below and let me know if you found value in this post.
Eric
