Robb Hill
asked on
powershell and sql server - alerting
I would like to have a job..that could check if sql had blocking processes..
and if it did send some type of email alert with information about the blocking process.
Also would be nice if the process could detect any other items like bad fragmentation on indexes..or anything else that would cause bad performance.
I have 100s of the databases all in remote locations...so I need something that can alert my team without be having to check.
The sql blocking would be the best.
I run the spwho3 stored proc to test for this..but this is manual and after the fact...
I need to know when sql has stopped due to this so I can be more responsive.
Please help
and if it did send some type of email alert with information about the blocking process.
Also would be nice if the process could detect any other items like bad fragmentation on indexes..or anything else that would cause bad performance.
I have 100s of the databases all in remote locations...so I need something that can alert my team without be having to check.
The sql blocking would be the best.
I run the spwho3 stored proc to test for this..but this is manual and after the fact...
I need to know when sql has stopped due to this so I can be more responsive.
Please help
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you had the chance to try the "blocking alert" as described at link posted above?
ASKER
testing it now actually.
Can we add more to the output in the csv file...
I think the spid and the actual query would be good things to add and the blocking id.
Can we add more to the output in the csv file...
I think the spid and the actual query would be good things to add and the blocking id.
ASKER
how would I make two email recipients on this blocking alert
Thanks
Thanks
To add multiple recipients one option is to have a Exchange (or other email) Group and add that group to the distribution list. This is so you don't need to make the @recipients_in list "custom" to each server. If you want you can also add them separated by ";" like in any mailing list so you would pass a parameter like:
@recipients_in = 'lcohan@mail.com;SomoneEls e@NewMail. com;OneMor e@SomeMail .com' --ETC...
As far as modifications to the output - sure please feel free to add whatever you need but if you test it in the CSV you have the root blocker and blocked processes SPID and code if I'm not mistaken.
@recipients_in = 'lcohan@mail.com;SomoneEls
As far as modifications to the output - sure please feel free to add whatever you need but if you test it in the CSV you have the root blocker and blocked processes SPID and code if I'm not mistaken.
Did the script to alert on locking/blocking processes from my article worked for you as well?