Tag Info

Hot answers tagged

7

Disclaimer: I work for SQL Sentry. Our SQL Sentry Event Manager product has a facility built exactly for this: to chain jobs and arrange them in various workflow orders. I started using SQL Sentry years ago, before I ever joined the company, to do exactly this. What I wanted was a way to start a restore job on our test server immediately after the backup ...


7

Short Answer It looks like the data in msdb.dbo.sysjobschedules is updated by a background thread in SQL Agent, identified as SQLAgent - Schedule Saver, every 20 minutes (or less frequently, if xp_sqlagent_notify has not been called and no jobs have run in the meantime). For more accurate information, look at next_scheduled_run_date in ...


7

Within your SQL Server Agent job, have some conditional logic to test for if the current instance is serving the particular role you are looking for on you availability group: if (select ars.role_desc from sys.dm_hadr_availability_replica_states ars inner join sys.availability_groups ag on ars.group_id = ag.group_id where ag.name = ...


6

At my previous job I did exactly this, mostly because the jobs were all run from our central, primary cluster, which was the most visible server. I could see all of our scheduled tasks in one place instead of having to go check command line stuff on a bunch of servers. While this is largely subjective (and it's going to be hard to derive a "correct" answer ...


6

To find the first and last days of the month: DECLARE @MonthStart DATETIME DECLARE @Date DATETIME DECLARE @next DATETIME -- create the required date. because today is the 21st i subtracted month. -- since you're running the job on the first day of the new month, -- you may want to change MONTH to DAY. however, if the job fails that change -- that may ...


6

You don't want to use BETWEEN to satisfy this query for the reasons I outline in this blog post: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx Instead you want an open-ended date range. Begins on the first of the month in question, and is less than the next month. To determine the first of the ...


5

Did some googling around and here are some software that may interest you: SQL Job Vis (this seems the most popular, according to some forum and blog posts) http://www.sqlsoft.co.uk/sqljobvis.php SQL Job Manager http://www.idera.com/Free-Tools/SQL-job-manager/ Query Currently Running SQL Server Agent Jobs ...


5

You will have to attach the MSDB DB under a different name like 'jobsdb' and pull the jobs out of jobsdb. The jobs will be stored under jobsdb.dbo.sysjobs and the job steps will be under jobsdb.dbo.sysjobsteps. They can be joined by the job_id column. The schedules can be found under jobsdb.dbo.sysjobschedules joined with jobsdb.dbo.sysschedules on ...


5

The way I've done this in the past is added a final step to the job, then I can use Database Mail to customize everything about the message (subject, body, recipients, from, etc) based on the server, the status/outcome of the job, and even the duration. It is cumbersome if you have a lot of jobs, but I did this for a variety of other reasons as well (for ...


5

For SQL Server 2000, I quite honestly wouldn't bother trying to get operators to work. SQL Mail is a royal PITA and requires Outlook or a similar mail client to be installed on the server. I would rather just setup each job to have a step called "mail on failure", which uses a token to identify the job, and then calls a stored procedure. You would only reach ...


5

Or you can have one job that runs every 5 minutes with 2 steps: Step #1 -- checks the CPU Usage (PowerShell), just google and there are plenty of scripts out there; If <20% exit with success go to Step 2, Else Exit with Error and finish the job (No Step #2) Step#2 -- is the actual UPDATE. Or as Ken suggested.


4

During the install process for SQL 2008 R2 it gives you the option to change the start-up type. I can see a slight desire to not have it start automatically depending on what you might be using it for. I have some setups that the user has some Agent jobs that clean up data or immediately start processing data. If I am troubleshooting, or in general the ...


4

You need access to SQL Server Agent in order to do what you need to do. This is done through msdb permissions: SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole. You can create a job to adhere to your schedule, and then the job will execute whatever task it is that you need done. As for the performance impact on the server, SQL Server Agent ...


4

In order to change the name (or any other property of an operator), you'd make a call to msdb.dbo.sp_update_operator. For instance: exec msdb.dbo.sp_update_operator @name = 'OldOperatorName', @new_name = 'NewOperatorName'; go As for SSMS: most things you do in SSMS you have to option to script out the changes you are making in the GUI. So, ...


4

Yes, you will need to create the jobs on any other replicas that you would want those specific jobs to run if they were the primary replica. You will need to create your own logic for if/when each SQL Server Agent job will run. For instance, do you want to run a job only if the current instance is the primary replica of a particular Availability Group? ...


4

I'd say the easiest possible way to do this is through PowerShell and SMO. Take the following code for instance: [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null $SqlServerNames = "Server1", "Server2" $SqlJobName = "YourJob" foreach ($SqlServerName in $SqlServerNames) { $SqlServer = New-Object ...


4

I don't think there's a built-in way to do this, but here are a few alternatives: Use OPENDATASOURCE like this: EXEC OPENDATASOURCE('SQLNCLI', 'Data Source=MyServer;Integrated Security=SSPI;Initial Catalog=master;Application Name="test app"') .master.sys.sp_configure; Note that using OPENDATASOURCE requires you to turn on the Ad hoc Distributed ...


4

I don't think you can work with that single job to get that exact schedule. You might want to consider having another job that disables/enables the job for the period of time that you don't want it to run. Another solution would be to add another conditional logic to your existing job and do a check on the current day of the week and what time it is, and ...


4

I believe your issue is going to be the SQLPS provider. Since PowerShell steps in SQL Server Agent automatically put you into the context of that provider some commands that work in your normal console will not function the same way. A write up was done here with Set-Location. You basically have to tell SQLPS the provider you want to use. Your code would ...


4

I am not sure how dirty you want to get on this one but the only method is going to be opening up SQL Server Data Tools and building (or modifying) an SSIS package. One would be to import the maintenance plan into SQL Server 2012 Data Tools and modify the SSIS package. The other would be to create it from scratch. You have the same options as the maintenance ...


3

Have you checked which SQL user is actually running the query? Depending on the permission level, SQL Agent may be allowing the user to 'impersonate' a higher level account. Create a job with the T-SQL step: SELECT ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME(); Make it log output to a table or text file and review which user is actually running the final ...


3

If you are not granted the needed permissions, you will not be able to see the Agent, no matter whether you run Enterprise/Standard/Datacenter, etc... The roles needed are sysadmin, or the individual roles here - http://msdn.microsoft.com/en-us/library/ms188283.aspx


3

This is likely a permissions issue. Verify that your replication agent accounts have the necessary permissions listed in the section Permissions That Are Required by Agents in Replication Agent Security Model.


3

How about mutiple, recurring Monthly Schedules where you have selected the first Monday of every 1 month on Schedule 1, the first Tuesday of every 1 month on Schedule 2, ... the second Friday of every 1 month? See the following pic to get the idea of what I'm thinking:


3

Assuming service has been restored and you're now attending to this without urgency, I'd be inclined to restore msdb to a separate instance if available. You are correct that the agent job definitions are stored in msdb, so following restore you can script the jobs you want to recover and re-create on the primary server. Whether restoring to the primary ...


3

Here you go. From: http://www.toadworld.com/platforms/sql-server/w/wiki/10351.sql-server-agent-job-query-samples.aspx SET NOCOUNT ON DECLARE @MaxLength INT SET @MaxLength = 50 DECLARE @xp_results TABLE ( job_id uniqueidentifier NOT NULL, last_run_date nvarchar (20) NOT NULL, ...


3

Personally I think the biggest caveat would be the difficulty in keeping the list of jobs organized. As far as I'm aware you can't create folders to organize the jobs, so a large number would be cumbersome. I'm not 100% sure of this, though, since none of my servers has more than a dozen or so jobs. Server 2008 and later's Task Scheduler allows for much ...


3

Well, you can possibly update msdb..sysjobschedules manually if you don't want to wait 20 minutes for it to correct itself by the background thread (as described in this answer). UPDATE msdb.dbo.sysjobschedules SET next_run_date = CONVERT(INT, CONVERT(CHAR(8), GETDATE(), 112)) WHERE CONVERT(CHAR(4), next_run_date) > '2020'; However, I haven't tried ...


3

In my opinion, this is one of those operations that would definitely benefit from PowerShell. All you'd need to do is gather the list of job steps that have your old string (in this case, your old location) and replace it with the new string (read: new location). [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null ...



Only top voted, non community-wiki answers of a minimum length are eligible