SQL SERVER AGENT: : Log table cleanup script

LogTableCleanup.sql

SET NOCOUNT ON

DECLARE @current_id INT = 1,
    @db_name sysname,
	@table_name sysname,
	@target_months INT,
	@datetime_column sysname,
	@sql NVARCHAR(MAX),
	@sep VARCHAR(100) = REPLICATE('*', 80)

DECLARE @dbs TABLE (
	[id] INT NOT NULL IDENTITY PRIMARY KEY,
	[db_name] sysname NOT NULL,
	[table_name] sysname NOT NULL DEFAULT ('[dbo].[Logs]'),
	[months] int NOT NULL DEFAULT (3),
	[datetime_column] sysname NOT NULL DEFAULT ('[TimeStamp]')
)

INSERT INTO @dbs (
    [db_name],
	[table_name],
    [months],
	[datetime_column]
)
VALUES
	('[Db1]', DEFAULT, DEFAULT, DEFAULT),
	('[Db1]', '[dbo].[OtherTable]', DEFAULT, '[AddDate]'),
	('[Db2]', DEFAULT, DEFAULT, DEFAULT)

--SELECT * FROM @dbs AS [s]

WHILE EXISTS (SELECT * FROM @dbs AS [s] WHERE [s].[id] = @current_id) BEGIN
	SELECT @db_name = [s].[db_name], @table_name = [s].[table_name], @target_months = [s].[months], @datetime_column = [s].[datetime_column]
	FROM @dbs AS [s] 
	WHERE [s].[id] = @current_id

	RAISERROR('%s', 0, 1, @sep) WITH NOWAIT;
	RAISERROR('%s', 0, 1, @sep) WITH NOWAIT;

	IF DB_ID(REPLACE(REPLACE(@db_name, '[', ''), ']', '')) IS NULL BEGIN
		RAISERROR('DB %s does not exist, continuing to next database', 0, 1, @db_name) WITH NOWAIT;
		CONTINUE
	END

	RAISERROR('DELETING RECORDS FROM %s.%s OLDER THAN %d MONTHS', 0, 1, @db_name, @table_name, @target_months) WITH NOWAIT;

	SET @sql = CONCAT('
	-- use nibbling deletes to cut down on transaction size
	DECLARE @records INT = 1, @cntr int	= 0,
		@start_date DATETIME,
		@timesspan VARCHAR(12)

	WHILE @records > 0 BEGIN
		BEGIN TRAN

		SELECT @cntr += 1, @start_date = GETDATE()
		RAISERROR(''TABLE ', @db_name, '.', @table_name, ' DELETE LOOP: %d'', 0, 1, @cntr) WITH NOWAIT;

		DELETE TOP (500000) FROM ', @db_name, '.', @table_name, ' 
		WHERE ', @datetime_column , ' < DATEADD(MONTH, -ABS(', @target_months, '), GETDATE())

		SELECT @records = @@ROWCOUNT, @timesspan = CONVERT(varchar(12), GETDATE() - @start_date, 114)

		RAISERROR(''TABLE ', @db_name, '.', @table_name, ' DELETE ROW COUNT: %d, ELAPSED TIMESPAN: %s'', 0, 1, @records, @timesspan) WITH NOWAIT;

		COMMIT TRAN
		IF @records > 0 BEGIN
			WAITFOR DELAY ''00:00:03''
		END
	END
	')
	--RAISERROR(@sql, 0, 1) WITH NOWAIT;
	EXEC (@sql)

	RAISERROR('', 0, 1) WITH NOWAIT;
	SET @current_id += 1
END