42

I posted a query a while back for getting a list of currently running jobs here. SELECT ja.job_id, j.name AS job_name, ja.start_execution_date, ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id, Js.step_name FROM msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id ...


40

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 = '...


18

To specifically answer your question, here's how you give disk access rights to the built in SQL Server Agent account. But read on as I answer what I believe to be the real issue: 1.> Right-click your drive, select properties, click the Add button and enter the SQLSERVERAGENT account(make sure your domain isn't selected in the From this location text box, ...


18

Is there any way to extract the full text of these messages? You can achieve it in 2 ways - Go in job step and select the Advanced tab: a. Output to a file (<== My preferred method) b. "Log to table" and "Include step output in history" (<== You need to trim msdb..sysjobhistory in long-run as the messages are stored as nvarchar(max) instead of ...


18

The problem looks more complex than it is. Since you are using SQL 2014 you are probably being bitten by the new security features introduced in 2012. The only thing that actually matters is: Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL. Description: Login failed for user '{domain user that runs SSIS ...


16

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


14

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 msdb.dbo....


14

Rather than doing this on a per job basis (checking every job for the state of the server before deciding to continue), I've created a job running on both servers to check to see what state the server is in. If its primary, then enable any job that has a step targeting a database in the AG. If the server is secondary, disable any job targeting a ...


14

The problem is that the PowerShell session is using the SqlServer provider by default. I added this statement into the script before the Out-File cmdlet: Set-Location c: This changed the provider to the FileSystem provider, and allowed the Out-File cmdlet to correctly find the file share and create the file. This article contrasts how SQLPS loads in SQL ...


14

If you are configuring a T-SQL job step go to the Advanced Page and configure the "Run as user" to the login of your choice. If you are working with other job step types like PowerShell it will require a proxy account to be configured.


13

Did some googling around and here are some software that may interest you: SQL Job Manager https://www.idera.com/productssolutions/freetools/sqljobmanager Query Currently Running SQL Server Agent Jobs http://sqlconcept.com/2011/06/25/how-to-query-currently-running-sql-server-agent-jobs/ If anyone has more links to add, go ahead! For point 3, the link ...


13

The default for QUOTED_IDENTIFIERin SSMS is ON while it is OFF for SQLCMD, OSQL, BCP and in your case especially SQL Server Agent. For reference, these are the different default options per application +-------------------------+------------+------+-------------------+------------+ | | ADO .Net, | SSMS | SQLCMD,OSQL, BCP | ISQL ...


12

That job has to do with policy-based management, not SQL Server Agent jobs in general. In Object Explorer right-click SQL Server Agent and select Properties, then move to the History tab. What does it say here: You can adjust those settings so that it keeps more rows overall, more rows per job, and keeps history longer. I am guessing either you have a lot ...


12

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some other things worth reading are a series on TRUSTWORTHY by Sebastian Meine, the BOL topic, and a KB article (even though the assembly portions may not be relevant ...


12

In the job step advanced properties, send output to a flat file by inputing the "Output file" location and filename. That should include all of your output for later viewing. Or, if you want all of the output directly in the history of the job, you can select the check box "Include step output in history". It'll use history entry rollover to include all ...


12

The "close" button simply closes the dialog; it does not stop the running job. You can use the following stored procedure to see the current runtime state of a given job: EXEC msdb.dbo.sp_help_jobactivity @job_name = '<job name here>'; To prove the "close" button does not actually stop a running job, I've created a test job: USE [msdb] GO BEGIN ...


10

You could create a job that checks the msdb.dbo.sysjobhistory table every minute (or however frequently you want). You might want to implement a queue table so you only ever send the message for any single instance failure once. USE msdb; GO CREATE TABLE dbo.ReportServerJob_FailQueue ( job_id UNIQUEIDENTIFIER, run_date INT, run_time INT, -- horrible ...


10

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 ...


10

SQL Server Agent is only available with certain versions of SQL Server. For instance, it is not available at all with SQL Server Express. If you have SQL Server Standard or Enterprise it will be available in SQL Server Management Studio (SSMS) at the bottom of the listing of server items.


10

Use the better version of the undocumented sp_MSForEachDB to iterate through the databases in a SQL Server Agent job. Or simply use a WHILE loop yourself on sys.database entries if you have a naming pattern.


10

There is already an existing answer posted by Aaron Bertrand for SQL Server Express 2012. First install SQL Server 2014 Express with Advanced Services as you did. Then read Aaron's instructions at: https://stackoverflow.com/questions/10407337/express-with-advanced-services-cant-create-full-text-index The short version is that the user interface does not ...


10

I don't think you will really get "all" the job information with one query since jobs can be configured to go to output files. Output files can at times get more information than is reported or written to the msdb tables. However, the view that is found by looking at the job history via SSMS can be pulled with this query to return only failed jobs (e.g. if ...


9

Expand SQL Agent Expand Proxies Find your proxy, right-click on it and choose Properties Go to the Principals tab Add the NT AUTHORITY\SYSTEM account This will give that account permission to use the proxy.


9

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 ...


9

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, ...


9

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 ...


9

I'm aware of two concepts to accomplish this. Prerequisite: Based on Thomas Stringer's answer, I created two functions in the master db of our two servers: CREATE FUNCTION [dbo].[svf_AgReplicaState](@availability_group_name sysname) RETURNS bit AS BEGIN if EXISTS( SELECT ag.name FROM sys.dm_hadr_availability_replica_states AS ars ...


9

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 ...


9

The job owner determines the execution context of the job's steps. For a T-SQL job step: If the job owner is a sysadmin then the step will execute as either the SQL Server Agent service account or a specified database user (set on the Advanced page of the Job Step Properties dialog). If the job owner is not a sysadmin then the step will execute as the job ...


8

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 ...


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