WAITFOR (T-SQL)

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.

Code
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’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘CheckDB’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘CleanHistory’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘DailyMonitor’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘DefrageIndexes’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘RemoveOldBU files’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘SysDB’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘UserDBFull’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘trackDBGrowth’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘UserDBLog’
go

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

Conclusion

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.

Cheers!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s