slq database name from first job step in sql agent

  • I'm looking to build a "dynamic" job step that is the first step in every job (due to availability  groups) that quits if it is not primary

    I have most of the code, but the bit i'm missing is how to find the name of the job that is currently running

    I have the basics (which is based on widely published code on the internet)

    CREATE PROC isprimary as
    DECLARE @role VARCHAR(50)
    DECLARE @db sysname
    SELECT @db=database_name FROM sysjobsteps
    SELECT @role = [role_desc] FROM [sys].[dm_hadr_availability_replica_states] hars
    INNER JOIN [sys].[availability_databases_cluster] adc
    ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] = DB_NAME()

    IF @role = 'PRIMARY'
    BEGIN
    PRINT 'Primary - Job can proceed'
    END
    ELSE
    BEGIN
    -- Deliberately cause a Failure
    SELECT 1/0
    END

    so this will be step 1 in every job , but i need to check the database for the next step in the job (sysjobsteps etc) - but getting the proc to figure out which job you are running is bugging me

     

     

    MVDBA

  • Have you taken a look at Job tokens Mike?

    https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps?view=sql-server-ver15

     

    Also any reason your not using sys.fn_hadr_is_primary_replica() instead of the query to determine if your on the right replica

  • tokens is an interesting idea

    the idea behind the proc is that we are consolidating quite a few servers onto a multi node server which is quite experimental hardware (I think we are the first to get it from HP) with a Greenlake/Nimble SAN - this means I have more than 400 sql agent jobs to move -

    needless to say I don't want to recode it all by hand and manually set that first step.. I can script up the add job step (I can't put it in the proc calls in the job step, I have to use a 1st step in the job approach)

    hence i'm looking at the database for the next step... easy, but making it automatically identify which job is eluding me.

    bear in mind i'm trying to make this process as easy as possible because we have non-dbas making jobs and i'm trying to build a system to close new jobs or modified jobs  between the 2 synchronous nodes (I don't care about the async reporting node) and none of these guys know anything about AG let alone server hardware

    MVDBA

  • DECLARE @Primary BIT, @DB SYSNAME
    SELECT @DB = database_name from msdb.dbo.sysjobsteps where job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) and step_id = 2

    SELECT @Primary = sys.fn_hadr_is_primary_replica (@DB)
    IF @Primary = 1
    BEGIN
    SELECT N'Primary Woohoo Continue'
    END
    ELSE
    BEGIN
    RAISERROR (50001,16,1,N'Not the Primary I must silently fail')
    END

    Something like the above Mike would do it.

    I'm too not a fan of adding the function call in on each step, gets a bit clunky doing that and have always done it as Step1 to run or skip the job depending on the above.

     

  • This is what I use in a lot of places for SQL jobs.   Not elegant, but it works.

    On my newest set of AG servers, when I create a job that is dependent upon the primary, I added the string "AG Dependent" in the description of each job.  I set up an alert for Error 1480: AG - Role Change, and in the response I execute a job that test for primary or secondary, and disables or re enables the jobs where the description LIKE 'AG Dependent'. Again, not elegant, and it is dependent upon a human (me!) remembering to add the right thing to any job description.

     

    /****** Object:  UserDefinedFunction [dbo].[IsPrimaryInAG]    Script Date: 3/4/2020 10:21:47 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    CREATE FUNCTION [dbo].[IsPrimaryInAG]
    (
    @AG_Name sysname = NULL
    )
    RETURNS tinyint
    AS
    BEGIN
    DECLARE @PrimaryReplica sysname;

    If @AG_Name IS NULL Begin
    SELECT
    @AG_Name = AG.name
    FROM sys.availability_groups AG
    End
    SELECT
    @PrimaryReplica = HAGS.primary_replica
    FROM sys.dm_hadr_availability_group_states HAGS
    INNER JOIN sys.availability_groups AG ON HAGS.group_id = AG.group_id
    WHERE AG.name = @AG_Name;


    IF UPPER(@PrimaryReplica) = UPPER(@@Servername) OR @AG_Name IS NULL BEGIN
    RETURN 1
    END; -- primary
    RETURN 0; -- not primary
    END;

    GO


    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Ant-Green wrote:

    DECLARE @Primary BIT, @DB SYSNAME
    SELECT @DB = database_name from msdb.dbo.sysjobsteps where job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) and step_id = 2

    SELECT @Primary = sys.fn_hadr_is_primary_replica (@DB)
    IF @Primary = 1
    BEGIN
    SELECT N'Primary Woohoo Continue'
    END
    ELSE
    BEGIN
    RAISERROR (50001,16,1,N'Not the Primary I must silently fail')
    END

    Something like the above Mike would do it.

    I'm too not a fan of adding the function call in on each step, gets a bit clunky doing that and have always done it as Step1 to run or skip the job depending on the above.

    ok - nice and simple but can you explain the $escape_none(jobid) bit... this sounds like it is the bit I am looking for - I've never used it. does it just return back the current job id... if so then im more than happy

    MVDBA

  • MVDBA (Mike Vessey) wrote:

    ok - nice and simple but can you explain the $escape_none(jobid) bit... this sounds like it is the bit I am looking for - I've never used it. does it just return back the current job id... if so then im more than happy

    The following link explains it... there's much more there, as well.

    https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps?view=sql-server-ver15

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • thanks guys - I think this will work nicely  for me - also something I should have been researching since 2016, just never had the need to

    MVDBA

  • This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply