New answers tagged

0

For testing purposes: Add the account you successfully created the objects with to the SQL Agent restart the Agent Re-run your job Let me know you findings please.


2

I've always done this by making a step's next action on failure be a specific step that sends an e-mail about that step only, then that step's on complete action is to continue to {failure step + 1}. Yes it requires some manual interaction with the jobs up front, but I can't think of any way to change the defaults without some level of manual tinkering.


2

To make this happen, you need to give the computer domain account on which you have SQL Server installed rights to write to the share [Folder] on [ComputerName]. Say you have a computer SRV-SQLSERVER, and SQL services you run with NT Service*, but in some situation you can't create a domain account for them. But you need to store backups on other domain ...


1

Naga this error would be where I would start looking SQLServer Error: 27, Registry information is corrupt or missing.Make sure the provider installed and registered correctly. [SQLSTATE 08001] If you are able to a repair of the installation should resolve this.


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


2

Set the SQL Agent service to Automatic(Delayed). The first thing it will do is try to connect to SQL Server, and if SQL Server hasn't finished starting yet, SQL Agent can fail. Delaying the start for SQL Agent will give SQL Server the chance to finish.


0

Make sure the SQL Agent service user (the one in services.msc) is a valid login on the SQL Server instance. If it is, look for 18456 errors in the SQL log to see why that login can't log in. Specific details in the MSDN Select an Account for the SQL Server Agent Service article


2

Please refer to Create a User-Defined Event By default, user-defined messages of severity lower than 19 are not sent to the Microsoft Windows application log when they occur. User-defined messages of severity lower than 19 therefore do not trigger SQL Server Agent alerts. So your message should be EXEC sp_addmessage @msgnum = 50005, @severity = ...


1

Adding "with log" to your raiserror statement will make the alert fire. RAISERROR (50005, -- Message id. 16, -- Severity, 1, -- State, N'My custom message') WITH LOG; you will need sysadmin or alter trace to use with log(https://msdn.microsoft.com/en-us/library/ms178592.aspx)


1

Regarding the status of job run, that's the correct steps. However, its recommended you configure the alerts for any database corruption, HW/SW failures or any other cause for an outage. Read here to configure the same. In order to test, create a TEST job and purposefully make it fail and see if you receive the failure notification email or not.



Top 50 recent answers are included