Same SQL Server Agent job, different databases

  • We have a script that is executed when a new database, a "test" version of our standard database, is attached to SQL Server 2005. Specifically, it sets up nightly maintenance jobs that defrags indexes, backup/truncate and shrink the transaction logs, etc.. At a later date/time, a "production" version of the the same database will be attached to the same instance of SQL Server. What appears to be happening is that the 2nd time the script is executed, the "production" version of the db is not included in the jobs set up on the first run of the script. The jobs should be executed for *both* the "test" and "production" databases. Here's the script:

    Any help would be greatly appreciated.

    DECLARE @returnCode AS INT

    DECLARE @jobId AS BINARY(16)

    DECLARE @jobCategory AS NVARCHAR(128)

    DECLARE @databaseName AS SYSNAME

    SET @returnCode = 0

    SET @jobId = NULL

    SET @jobCategory = 'Nightly Jobs'

    SET @databaseName = db_name()

    /* create the nightly jobs category if it doesn't exist */

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name = @jobCategory)

    BEGIN

    EXEC @returnCode = msdb.dbo.sp_add_category

    @name=@jobCategory

    IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback

    END

    /* create the Maintenance job */

    EXEC @returnCode = msdb.dbo.sp_add_job

    @job_name=N' Nightly Maintenance',

    @enabled=1,

    @notify_level_eventlog=0,

    @notify_level_email=0,

    @notify_level_netsend=0,

    @notify_level_page=0,

    @delete_level=0,

    @description=N'No description available.',

    @category_name=@jobCategory,

    @owner_login_name=N'sa',

    @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback

    /* create the user aging job step (Step 1)*/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep

    @job_id=@jobId,

    @step_name=N'User Aging',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @on_success_step_id=0,

    @on_fail_action=3,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0,

    @subsystem=N'TSQL',

    @command=N'EXECUTE dbo.usp_UserAging',

    @database_name=@databaseName,

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /* create the index defrag job step (Step 2) */

    EXEC @returnCode = msdb.dbo.sp_add_jobstep

    @job_id=@jobId,

    @step_name=N'Defragment Indexes',

    @step_id=2,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @on_success_step_id=0,

    @on_fail_action=3,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0,

    @subsystem=N'TSQL',

    @command=N'EXECUTE dbo.usp_defragindexes',

    @database_name=@databaseName,

    @flags=0

    IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback

    /* create the adjust on order quantities job step (Step 3) */

    EXEC @returnCode = msdb.dbo.sp_add_jobstep

    @job_id=@jobId,

    @step_name=N'AdjustOnOrderQuantities',

    @step_id=3,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @on_success_step_id=0,

    @on_fail_action=3,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0,

    @subsystem=N'TSQL',

    @command=N'EXECUTE dbo.usp_FixOnOrderQuantity',

    @database_name=@databaseName,

    @flags=0

    IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback

    /* create the archive job step (Step 4) */

    EXEC @returnCode = msdb.dbo.sp_add_jobstep

    @job_id=@jobId,

    @step_name=N'Archive',

    @step_id=4,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @on_success_step_id=0,

    @on_fail_action=3,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0,

    @subsystem=N'TSQL',

    @command=N'EXECUTE dbo.usp_ArchiveControl',

    @database_name=@databaseName,

    @flags=0

    IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback

    /* create the log backup and shrink job step (Step 5) */

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep

    @job_id=@jobId,

    @step_name=N'Backup Transaction Log',

    @step_id=5,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0,

    @subsystem=N'TSQL',

    @command=N'EXECUTE dbo.usp_BackupTruncateShrinkTLog',

    @database_name=@databaseName,

    @flags=0

    IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback

    EXEC @returnCode = msdb.dbo.sp_update_job

    @job_id = @jobId,

    @start_step_id = 1

    IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback

    /* create the schedule to execute the job steps */

    EXEC @returnCode = msdb.dbo.sp_add_jobschedule

    @job_id=@jobId,

    @name=N'Nightly Maintenance',

    @enabled=1,

    @freq_type=4, -- daily

    @freq_interval=1, -- once a day

    @freq_subday_type=1, -- at a specified time

    @freq_subday_interval=0,

    @freq_relative_interval=0,

    @freq_recurrence_factor=0,

    @active_start_date=20080905,

    @active_end_date=99991231,

    @active_start_time=13000, -- start at 0130

    @active_end_time=235959

    IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback

    EXEC @returnCode = msdb.dbo.sp_add_jobserver

    @job_id = @jobId,

    @server_name = N'(local)'

    IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback

    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    EndSave:

    GO

  • Are you saying that the second job is not being created?

    How are you running this script after the production database is attached?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Hello,

    Solution is very simple you need to copy the code to query analyzer and change the database to "production" and than execute the script.

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • RBarryYoung (1/19/2009)


    Are you saying that the second job is not being created?

    Actually the jobs are not getting created for the 2nd database that's attached to the server. Here's the scenario... a "test" database is attached to the server first. A series of scripts are executed via the VB app mentioned below. When the scripts are finished, I can see a "Nightly Maintenance" job with several job steps; User Aging, Defragment Indexes, Adjust On Order Quantities, Archive, and Backup Transaction Log. Looking at the job steps, I can see that they are executing on the "test" database, as I would expect.

    At some point, after a period of testing, a "production" version of the database is attached to the server and the same scripts are executed as before. When I look at the job steps, they should be executing against *both* the "test" and "production" databases but they aren't.

    How are you running this script after the production database is attached?

    The script is executed via a VB6 application, which shells out and runs the osql command line utility.

  • mj12 (1/20/2009)


    At some point, after a period of testing, a "production" version of the database is attached to the server and the same scripts are executed as before. When I look at the job steps, they should be executing against *both* the "test" and "production" databases but they aren't.

    OK, then the problem is that this script does not do this.

    First of all it appears that these jobsteps can only run against one database at a time, not multiple databases at once (this is normal). Therefore I must presume that what you meant is that this Job will run against both databases, with a new set of Job steps for the second (production) database. Again, this script does not do this.

    What it does do is to add a new Job named ' Nightly Maintenance' and then add jobsteps in the current database to that newly made job. Since Job names must be unique within a server instance, the "sp_add_job" command will fail the second time that you run it and the script will then jump to its error-handler, rollback the changes and exit. This is what is happening to you.

    Finally, either the script is not reporting the error/faliure or the VB code is not detecting it.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • First of all it appears that these jobsteps can only run against one database at a time, not multiple databases at once (this is normal).

    You're absolutely right. What I've settled on is checking to see if 'Nightly Maintenance' job already exists. If so, drop it. In hind sight, it make little sense to have these jobs executing against a "test" database once the live "production" database is attached. An alternative would've been to create unique names for the job and job steps, i.e. appending the name of the database to the job step names. Here's a snippet of the script, changes are in bold.

    DECLARE @returnCode AS INT

    DECLARE @jobName AS SYSNAME

    DECLARE @jobCategory AS NVARCHAR(128)

    DECLARE @databaseName AS SYSNAME

    SET @returnCode = 0

    SET @jobName = 'Nightly Maintenance'

    SET @jobCategory = 'Nightly Jobs'

    SET @databaseName = db_name()

    /* create the nightly jobs category if it doesn't exist */

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name = @jobCategory)

    BEGIN

    EXEC @returnCode = msdb.dbo.sp_add_category

    @name=@jobCategory

    IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback

    END

    /* delete any pre-existing nightly maintenance job and all the steps */

    IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @jobName)

    BEGIN

    EXEC msdb.dbo.sp_delete_job

    @job_name=@jobName,

    @delete_unused_schedule=1,

    @delete_history=1

    END

    remainder of script unchanged. Thanks for your input!

  • Glad I could help.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

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