Link to home
Start Free TrialLog in
Avatar of Robb Hill
Robb HillFlag for United States of America

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
SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Did you had the chance to try the "blocking alert" as described at link posted above?
Avatar of Robb Hill

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.
how would I make two email recipients on this blocking alert


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;SomoneElse@NewMail.com;OneMore@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.
Did the script to alert on locking/blocking processes from my article worked for you as well?