New answers tagged sql-server-agent
I know that the post is old, but to make this work be sure that the user you set as Run As in the Job, has access to the proxy and is in the list of the proxy Principals. If the user is systemadmin in sql it will not get saved in the list and you will get the error.
Replace 'drop database [myDb_snapshot]' with: declare @cmd nvarchar(max); set @cmd = 'drop database [myDb_snapshot]'; exec (@cmd); The entire SQL statement is evaluated for correctness prior to running it. Since the snapshot doesn't exist, it won't run. Dynamic SQL removes that limitation.
There are couple of steps that you can do to address the problem that you faced : Have a startup stored procedure using sp_procoption that runs and checks and alerts you that SQL Agent is not running. IF EXISTS ( SELECT 1 FROM MASTER.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher') BEGIN SELECT @@SERVERNAME AS ...
For a powershell solution, simply starting the SQL Server Agent service automatically brings up the SQL Server Service since it depends on it. Stop-Service -Name MSSQLSERVER -Force; #Brings down both services Start-Service -Name SQLSERVERAGENT; #Starts both services
Querying the linked server seems to get it to reconnect. Calling sp_testlinkedserver doesn't always work, but a select statement will. Sometimes there is a two minute lag from the start of the downed server to the next successful run of the job. In any case, it's simply a matter of waiting.
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 ...
You can do it in bulk using this statement: UPDATE msdb.dbo.sysjobsteps SET on_fail_action = 2 -- Quit the job reporting failure However, please note that you probably don't want to that without checking what the job steps do and whether some jobs are configured for specific additional steps in case of failure or simply ignore errors. Be careful!
Use Script to update Job steps In Bulk USE MASTER GO SET QUOTED_IDENTIFIER ON GO DECLARE @job_id VARCHAR(1000), @Step_id INT DECLARE JobList CURSOR FOR SELECT jb.job_ID, jstp.step_id FROM msdb.dbo.sysjobs jb INNER JOIN msdb.dbo.sysjobsteps jstp ON ...
Expand the option of SQL Server Agent Expand Jobs Right Click on the respective job and click properties On job properties, click on second option saying Steps. Select the step and click on Edit Now select the second tab saying Advanced. Here on the tab which says ON failure action, select from drop down saying "Quit the job reporting failure" instead of go ...
Every step you seperate with \CR \LF in a Script you have written previously only can be executed in the AgentJob if you choose one of those two options: Everey seperate line in your bat-script hast to be a seperate step in the AgentJob delete all \CR \LF and in your Step of the AgentJob and put a && between your commands
Top 50 recent answers are included