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.
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).
Here we are creating a transact SQL function that errors out when the target database is in READ_ONLY mode.
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
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.
Step – 1 – Check If Target Database Is ReadOnly, if so fail and exit job
Tab – General
Tab – Advanced
- On Success action
- Go to the next step
- On failure action
- Quit the job reporting failure
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