58

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


24

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, select security, click the Add button and enter the SQLSERVERAGENT account(make sure your domain isn't selected in the From this ...


21

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


17

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


17

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


15

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


15

I'm really glad you asked this question. I had this very same need for SQL Server jobs in a Visual Studio database project and job deployments along with a database. I wanted the solution file to contain everything that is needed for the SQL database process I had implemented, all in one place, to Create this solution AND Deploy this solution to any ...


14

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


13

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


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

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


11

Your code is backwards. You are sending the email, then generating the query results. The sp_send_dbmail stored procedure will run the query for you, and append the results either in the body of the email or as an attachment. In case you aren't aware, Microsoft has extensive, comprehensive, and free documentation on their website for SQL Server. For ...


11

It's not the way the table was created, it's the options your query runs with. Agent does indeed run with the wrong settings. SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND ( ansi_nulls = 0 OR ansi_padding = 0 OR ansi_warnings = 0 OR arithabort = 0 OR concat_null_yields_null = 0 OR ...


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

You can get this information by the standard reports available in sql server: Make a right click on SQL server agent> Standard reports and select the desired report like job executions "failed ones or the ones taking lot of time" ..Select the report as per you're need: Or, You can use below script to find jobs failed in last 24 hours: -- ...


10

SQL agent jobs are stored in the system msdb database, so backing up that database will do what you need. In a disaster, you can restore the entire msdb database, or, if necessary, recover individual jobs from a restored copy of msdb: Serverfault: Recovering a specific job definition from an MSDB backup? If you want them in a more immediately usable ...


10

Thank you for reporting this. There is a feedback item in https://feedback.azure.com/forums/908035-sql-server/suggestions/39754837-ssms-18-4-sqlserver-2019-cu2-object-explorer-fails. All updates will be provided there as we investigate. FYI, the bug introduced in CU2 with xp_sqlagent_enum_jobs is now understood and a fix will ship in CU3. Meanwhile, if you'...


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

The way SQL Server Agent works is exactly how you want already - if you schedule the job for every 10 minutes, and at 11:00 it runs for 12 minutes, it won't run again until 11:20 (the 11:10 instance won't start, because an instance of the job was already running).


9

Discontinued Database Engine Functionality in SQL Server 2016 indicates ActiveX subsytem is discontinued. Use command line or PowerShell scripts instead.


8

Quite frankly, I would just not use the limited operators facility through the UI and instead: Create a step that only gets reached when the job succeeds, which e-mails person 1 Create another step that only gets reached when any step fails, which e-mails person 2 (You may need to modify multiple steps that could potentially fail, such that when they do ...


8

Personally I would create another job and schedule it to run each morning at 7am. A job step within that job could execute something like USE msdb ; GO EXEC dbo.sp_stop_job N'<Your job name>' ;


8

You have maintenance plans. Try to follow these steps: Find the maintenance plan name and id that you want to delete. Write down the id of the one you want to delete. SELECT name, id FROM msdb.dbo.sysmaintplan_plans --Place the id of the maintenance plan you want to delete into the below query to delete the entry from the log table: DELETE FROM msdb....


8

The @name parameter is a parameter of msdb.dbo.sp_add_category and not EXEC. See the sp_add_category documentation: [ @name = ] 'name' The name of the category to be added. The name must be unique within the specified class. name is sysname, with no default. [Uncategorized (Local)] just indicates a default category when you didn't specify a ...


8

Maybe the wording is unclear, but least privilege applies to all activities and should never be ignored. The least privilege you need to perform database maintenance is sysadmin. Lower privileged users could perform those activities too (I'm thinking of db_owners), but the least privilege needed to perform any activity on a SQL Server instance is sysadmin, ...


8

For T-SQL Job Steps, the "output" refers to "messages" -- notices sent via PRINT and RAISERROR. Result sets are also included as "output", but only if there are no PRINT / RAISERROR messages, else it is only the PRINT / RAISERROR messages that are included. Try this test: Job Step 1 Definition: PRINT ' ** Line 1 ** '; SELECT ' ** Line 2 ** ' AS [Line ...


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