SQL Job to Delete Orphaned Records

We improved the Expense Transfer Process. The delete would cause incredible lag so we orphan those deleted records and created a scheduled job to delete those records. This will need to be set up to run nightly.

USE [msdb]
GO

/****** Object: Job [AIM-3598 Delete Orphaned Expense Recores] Script Date: 2/20/2020 10:48:17 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 2/20/2020 10:48:17 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'AIM-3598 Delete Orphaned Expense Recores',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'During business hours, Transfer expenses are recalcualted, and old versions are not deleted. This job will clean up these records off hours.',
@category_name=N'Database Maintenance',
@owner_login_name=N'VGNET\tony.ornelas', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Delete Orphan Expenses For All LIVE Databases] Script Date: 2/20/2020 10:48:18 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete Orphan Expenses For All LIVE Databases',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
declare @databases table (TableName varchar(100));
insert into @databases
select name from sys.databases
where state = 0
and name like ''Live_%_User''
;

while exists (select 1 from @databases)
begin
declare @db varchar(100);
select top 1 @db = TableName from @databases;

declare @version varchar(100);
declare @sql nvarchar(200);
declare @params nvarchar(200);
set @sql = N''select @versionOut=cast(value as varchar(100)) from ''+@db+''.sys.extended_properties where name = ''''aim_version'''''';
set @params = N''@versionOut varchar(100) output''
exec sp_executesql @sql,@params,@versionOut = @version output;

print @db
print @version

if (@version != '''')
begin
print ''Disabling Expense Trigger''
set @sql = N''exec ''+@db+''.dbo.spAutoAuditDrop ''''Expense'''''';
exec sp_executesql @stmt=@sql;
print ''Mass-Delete Orphan Expenses''
set @sql = N''delete from ''+@db+''.dbo.Expense where GroupID = 0''
exec sp_executesql @stmt=@sql;
print ''Enabling Expense Trigger''
set @sql = N''exec ''+@db+''.dbo.spAutoAudit ''''Expense'''''';
exec sp_executesql @stmt=@sql;
end

delete from @databases where TableName = @db;
end
;
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run Nightly',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20200219,
@active_end_date=99991231,
@active_start_time=220000,
@active_end_time=235959,
@schedule_uid=N'2a6186ab-043e-447e-88da-4cea7912cf53'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO