Running a SQL SCript via SQL Alerts

Question

Running a SQL SCript via SQL Alerts

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
January 15, 2016 at 2:35 pm

#307746

Hello,
I am planning to setup an alert which is nothing but to alert us if there's any orphan transaction so that we can kill them.
I think the below code (or sysprocesses even with a -ve SPID) can capture the details but to notify as alerts (dont want to setup a job again to notify).
select *
from master..syslockinfo
where req_spid = -2
Thanks.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

SQL-DBA-01 (1/15/2016)
Hello,
I am planning to setup an alert which is nothing but to alert us if there's any orphan transaction so that we can kill them.
I think the below code (or sysprocesses even with a -ve SPID) can capture the details but to notify as alerts (dont want to setup a job again to notify).
select *
from master..syslockinfo
where req_spid = -2

Instead of using a SQL 2000 compatibility view, I'd suggest using the current DMV. It's covered at https://msdn.microsoft.com/en-us/library/ms190345%28v=sql.100%29.aspx.

SELECT whatever_columns_you_need

FROM sys.dm_tran_locks

WHERE request_session_id = -2;

You can then send yourself the list using sp_send_dbmail. Or, the page that covers sys.dm_tran_locks has a link to the KILL command.

I don't understand what you're asking about alerts and jobs. Presumably, you'd have a procedure to do the check, which would be scheduled as job. It can notify you if the job fails.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lowell SSC Guru Points: 323546 More actions · Answer 2

there is no alert for that specific condition(where a spid exists with value = -2.)

so you cannot use Alerts, specifically.

for specific events you can capture, you can have an event kick off a procedure for example, and the procedure can email or perform some commands. i didn't see any alert related to connections like that.

i would suggest using a SQL job, and do as Ed Wagner suggested, select from the better DMV, and alert yourself via email.

you could use extended events to capture data, not sure if you could kick off an event if some criteria were met... i'd have to read up on that.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!