SQL Server Agent – Skip Job Processing when Database is in Read-Only mode

Background

One of our scheduled jobs has been failing on a regular basis. When ran manually it works, but in the middle of the night at 3 AM, it claims to have ran successfully, but our numbers do not add up.

 

Trouble Shooting

As part of endless thought processing on reasons and basis for failure noticed that the SSIS\DTS log table ( [dbo].[sysdtslog90] ) contains entries for both the primary and secondary server.

Right away, disabled the job on the secondary server.

But, wanted a more programmable and secondary trap to make sure that when the job is being ran on a fail-over box, it will not run.

Here is one way .. simply checking if the target database is READ_ONLY. If so skip further job processing, by simply failing.

SQL Server Agent does not provide a lot of flexibility for Job Step processing, it is a simple light switch (YES/ NO).

SQL

Programmable

dbo.fn_ErrorIfDatabaseIsReadOnly

Coverage

Here we are creating a transact SQL function that errors out when the target database is in READ_ONLY mode.

Code


use master
go

if object_id('dbo.fn_ErrorIfDatabaseIsReadOnly') is null
begin

	exec(
			'create function dbo.fn_ErrorIfDatabaseIsReadOnly()
		     returns bit
			 begin
				return 0
		     end
		    '
		)	 	

end
go


alter function [dbo].[fn_ErrorIfDatabaseIsReadOnly]
(
	@database sysname
)
returns bit
begin

	declare @UpdateabilityFlag sysname
	declare @UpdateabilityValue sysname
	declare @strLog varchar(600)

	set @UpdateabilityFlag = 'Updateability'

	set @strLog = 0

	set @UpdateabilityValue
			 = cast
					(
						databasepropertyex
							(
								  @database
								, @UpdateabilityFlag
							)
							as sysname
					)

	if ( @UpdateabilityValue = 'READ_ONLY')
	begin

		--raiserror('Database is readonly', 16, 1)
		set @strLog = 'Database is ReadOnly'
	end

	return(
			cast
				( @strLog as int)
		  )	
end


go


grant execute on [dbo].[fn_ErrorIfDatabaseIsReadOnly] to [public]
go


 

SQL Server Agent Job

Coverage

As the initial step, invoke our new function ( master.dbo.fn_ErrorIfDatabaseIsReadOnly ).

If successful, we continue.  On the other-hand, if it fails, we quit the job reporting failure.

Steps

Step – 1 – Check If Target Database Is ReadOnly, if so fail and exit job

Tab – General

jobstep-general

 

Tab – Advanced

jobstep-advanced

Explanation
  1. On Success action
    • Go to the next step
  2. On failure action
    • Quit the job reporting failure

 

Honorable Mentions

Vladimir Korolev

Where Is but on Stack-overflow, Vladimir Korolev, brilliantly answers the question on how to throw exceptions in Transact SQL Functions.

Here is the link

usingcasttothrowmeaningfulerror

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

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