Retryable SQL

Retryable SQL Script.md

Retryable SQL Script

Allows retrying a SQL script until a lock can be achieved necessary to do the work.

retry.sql

USE [YourDatabase];

SET LOCK_TIMEOUT 1000;

DECLARE @msg nvarchar(100)
DECLARE @attempts int
DECLARE @completed bit

SELECT  @completed = 0, @attempts = 0

WHILE (@completed = 0)
BEGIN
  BEGIN TRY    
    SET @attempts = @attempts + 1
    
    SET @msg = '[' + CONVERT(nvarchar, @attempts) + '] Trying ...'
    RAISERROR (@msg, 10, 1) WITH NOWAIT
    
    BEGIN TRAN
    
    
    -- Put your retrying code here...
    -- For example...
    ALTER TABLE [YourTable]
    ALTER COLUMN [Whatever] NVARCHAR(254)

    
    SET @completed = 1
    COMMIT TRAN
    
    SET @msg = '[' + CONVERT(nvarchar, @attempts) + '] Done.'
    RAISERROR (@msg, 10, 1) WITH NOWAIT
  END TRY
  BEGIN CATCH
    ROLLBACK TRAN
    
    SET @msg = '[' + CONVERT(nvarchar, @attempts) + '] Failed: ' + ERROR_MESSAGE()
    RAISERROR (@msg, 10, 1) WITH NOWAIT
  END CATCH  
END