I don’t write a lot of code. Most of what I do write is little pieces here and there that make my job as an Operational DBA easier. After all I do my very best to not work hard. I try and work smarter. This post falls right into those lines.
I have a few scripts I use when setting up and configuring a new SQL Server. One of the little scripts runs all the SQL Agent Jobs after setup to confirm they all work and should they fail to fix them before I confirm the Change Orders are all complete and the server is setup.
After running all the scripts to create the Jobs this is the last one that runs.
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘AuditFailedLogins’
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘CheckDB’
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘CleanHistory’
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘DailyMonitor’
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘DefrageIndexes’
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘RemoveOldBU files’
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘SysDB’
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘UserDBFull’
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘trackDBGrowth’
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘UserDBLog’
So, What’s The Problem?
Well, I am glad you asked. The problem is/was the Job “UserDBLog” backs up the transaction log and this error is produced.
“BACKUP failed to complete the command BACKUP LOG db_stats. Check the backup application log for detailed messages.”
When checking the job history I found the following error.
“Executed as user: user_name. BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.”
According to this error the database has never been backed up. Actually, that is somewhat correct. As part of my setup and testing I create a small database. In the script above the “UserDBFull” job backs up the database. What is happening is the “UserLog” job is firing before the user database backup has completed.
Wait For It
To fix this minor issue I am just going to add the following line.
WAITFOR DELAY ’00:00:05′;
This will delay the firing of the log backup long enough (5 seconds) for the database backup to complete. Make sure you understand and read more about this little snippet of code before using it. You can read more about the WATIFOR code here: http://msdn.microsoft.com/en-us/library/ms187331.aspx
There are many things within SQL Server to discover. Only yesterday I used the WAITFOR for the first time. Look around, you might just be surprised by what you find.