Sometimes I want to run a bunch of statements (like updating a bunch of stats or re-indexing a number of indexes) and I want to reduce the chance that these statements will interfere with other processes in the database. I created a script which loops over each statement to be executed and then checks to see if the database is currently in use (has any "Runnable" processes and if it does, waits until all "Runnable" processes have completed, then runs the statement and then starts over with the next statement.

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 EstRemainingHours

Please comment below and let me know if you found value in this post.

Eric