Epic Blog of Awesome

code.tech.sci.math.art.write

Enabling Email Alerts for SQL Server Deadlocks | Practical SQL Server

Of course, setting up alerts for deadlocks should be pretty easy, right? All you have to do is go into the SQL Server Agent, set up an alert for Error 1205, set up who to email/alert when it happens, and you’re done, right?Well, unfortunately, setting up alerts for deadlocks isn’t that simple. For starters, while Error 1205 is the most common error number associated with a deadlock, Errors 1211 and 3928 are also associated with deadlocks within SQL Server though Error 3928 is reserved for a particularly nasty/non-typical issue relating to deadlocks. So. There’s that issue for starters i.e., if you want to be thorough, you’ll have to set up alerts for all three error numbers.

via Enabling Email Alerts for SQL Server Deadlocks | Practical SQL Server.

Answer : limit sql agent job resource with resource governor

limit sql agent job resource with resource governor

Hi! I am trying to limit resource used by SQL agent job and its not working
for me. When the job kicks off, for some reason the process its not going to
the workload group that I have defined and it goes to the default workload
group.

Run the job. While the job is running, look at sys.dm_exec_sessions. You
will see the program_name column (which correlates to APP_NAME() in the
classifier function) has something like:

TSQL JobStep (Job 0XCDFA…)

So now your classifier function can say (don’t forget to replace the
0XCDFA… part with what you actually observe above):

DECLARE @j BINARY(16) = CONVERT(BINARY(16), 0XCDFA…);

IF APP_NAME() LIKE ‘%’ + CONVERT(VARCHAR(36), @j, 1) + ‘%’
SET @workload_group_name = ‘BackupAndDataCompression_Group’;

This will work as long as you don’t drop and re-create the job.

Complete classifier function example:

USE MASTER
GO
CREATE FUNCTION [dbo].[rgclassifier_HeadcountRefresh]() RETURNS
sysname
WITH SCHEMABINDING
AS
 BEGIN
 DECLARE @workload_group_name AS sysname
 DECLARE @j BINARY(16) = CONVERT(BINARY(16), 0xB2E92732843AB94EB01D3CF5CC5FC1F6);
IF APP_NAME() LIKE '%' + CONVERT(VARCHAR(36), @j, 1) + '%'
 SET @workload_group_name = 'BackgroundJobs';
RETURN @workload_group_name
 END;
GO

via Answer : limit sql agent job resource with resource governor.