Spreading Out Multi-Server Agent Runs

Tracy Boggiano shows how to distribute SQL Agent job runtimes for multi-server jobs using MSX/TSX:

First, you need to decide how many time blocks or hours you want the jobs to run in.  So let’s start with scenario one where you pick for example four time blocks.  First, you declare a variable with the time block in it and we will feed in the @@SERVERNAME to let determine a value for the time block that server will run.  Then we wrap our code around our time block, our example we will run Index Maintenance for a 12 period spread out for three hours.  Mind you for my index process which I probably should blog about as well I am processing one index at a time have something that BREAKs out of the procedure when it exceeds the time block it is.  So below we run Index Maintenace between start the index maintenance job on a server between the hours 6  PM and 5 AM based on the time block value we got back.

Click through for a sample.

Related Posts

Configuring SQL Agent For Linux

Steve Jones shows how to install and configure the SQL Agent for Linux: When I first started playing with this version, I noticed that SQL Agent was disabled. That’s not great, since SQL Agent is a great tool for various tasks in SQL Server. I can’t start the agent from here, as the underlying implementation […]

Read More

Alerting On tempdb Growth

Lori Brown shows how to use a SQL Agent alert to warn you if tempdb grows beyond a certain size: Lastly, create a SQL Alert to notify you as soon as tempdb grows past the threshold you stipulate. Using the GUI to create the alert, you need to fill out every field on the General page and […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

May 2018
MTWTFSS
« Apr Jun »
 123456
78910111213
14151617181920
21222324252627
28293031