September 8, 2016 at 1:18 pm
I have a stored procedure that takes a full backup of all online databases (except tempdb) that runs fine when executed from SSMS query window.
I have an Agent Job that runs this procedure daily and dumps the results to a log file. If I run the Agent Job manually, then all of the 30 or so databases on the server are backed up with no problems. However, when the job runs on the schedule, it only makes it through the first 4 or 5 databases, generates no errors, and seems to quit successfully. Only one time in the last 2 weeks did the scheduled job complete all of the backups.
The SQL Server logs show no errors, and the server logs indicate no problems occurred. The times the job only backs up the first 4 or 5 databases, the duration of the job is under a minute.. usually 20-30 seconds. When it completes all the backups, that takes more like 4-6 hours.
Any thoughts? I'm not even sure how I can troubleshoot an issue like this! Incidentally, I have an almost identical setup for transaction log backups that occur hourly, and that job gets all the databases every time.
ALTER PROCEDURE [dbo].[BackupDatabases_Full]
AS BEGIN
DECLARE @bkFilePath VARCHAR(100) = '\\dfs\SQLBACKUPS\';
DECLARE @dateStamp VARCHAR(100);
SET @dateStamp = FORMAT(GETDATE(),'yyyy_MM_dd','en-US');
PRINT CHAR(13)+CHAR(13)+'[dbo].[BackupDatabases_Full] Started on ['+@@SERVERNAME+'] AT '+CONVERT(VARCHAR,GETDATE(),120)+CHAR(13)+CHAR(13);
-- Only run if PRIMARY:
IF (@@SERVERNAME = (SELECT [replica_server_name]
FROM [sys].[dm_hadr_availability_replica_states] s
JOIN [sys].[availability_replicas] r ON s.[replica_id] = r.[replica_id]
WHERE s.[role_desc] = 'PRIMARY'))
BEGIN
DECLARE @dbName NVARCHAR(1000);
DECLARE @bkFileName NVARCHAR(1000);
DECLARE c CURSOR FOR
SELECT[name]
FROM [sys].[databases]
WHERE [state_desc] = 'ONLINE'
AND [name] <> 'tempdb'
ORDER BY [name];
-- Loop through all databases and take backup:
OPEN c;
FETCH NEXT FROM c INTO @dbName;
WHILE @@FETCH_STATUS = 0 BEGIN
SET @bkFileName = @bkFilePath + @dbName + '_Full_Bk_' + @dateStamp + '.bak';
PRINT CHAR(13)+CHAR(13)+'BACKUP DATABASE [' + @dbName + '] TO DISK = "' + @bkFileName + '"';
BACKUP DATABASE @dbName TO DISK = @bkFileName;
FETCH NEXT FROM c INTO @dbName;
END;
-- Clear cursor:
CLOSE c;
DEALLOCATE c;
END;
ELSE
PRINT 'Not Primary - Skipping Backups!';
-- Update the agent job and "increment" the output file name for the next run:
DECLARE @nextLogFileName NVARCHAR(1000);
SET @nextLogFileName = '\\dfs\Logs\_Maint_BackupDatabases_Full_'
+ @@SERVERNAME + '_'
+ FORMAT(DATEADD(DAY,1,GETDATE()),'yyyy_MM_dd','en-US')
+ '.log';
EXEC [msdb].[dbo].[sp_update_jobstep]
@job_name = '_Maint_BackupDatabases_Full'
, @step_id = 1
, @output_file_name = @nextLogFileName
, @flags=2;
PRINT CHAR(13)+CHAR(13)+'[dbo].[BackupDatabases_Full] Completed on ['+@@SERVERNAME+'] at '+CONVERT(VARCHAR,GETDATE(),120)+CHAR(13)+CHAR(13);
END;
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_Maint_BackupDatabases_Full',
@enabled=1,
@notify_level_eventlog=3,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'SQLExceptions', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BackupDatabases_Full',
@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'EXEC [dbo].[BackupDatabases_Full]',
@database_name=N'master',
@output_file_name=N'\\dfs\Logs\_Maint_BackupDatabases_Full_SERVER_2016_09_08.log',
@flags=2
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'BackupDatabases_Full',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160826,
@active_end_date=99991231,
@active_start_time=170000,
@active_end_time=235959,
@schedule_uid=N'4c2e34e3-890f-462e-b864-6bf12e523a92'
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
September 8, 2016 at 1:38 pm
Just a quick thought -
It could just the the same issues hit when using the sp_MSForech stored procedures - objects can end up skipped due to changes in the underlying table. Try creating your cursor as static.
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply