Script to run in SQL Server Agent to monitor DMVs, log results and take action depending upon results
Here’s a script to play with which is useful for simple management tasks. I wrote it to help someone with a TokenAndPermUserStore cache issue (https://support.microsoft.com/kb/927396/) which are relatively uncommon nowadays. I actually sent it to someone today which is why it came to mind, but it occurred to me that the template of the script itself is quite useful for any such TSQL SQL Agent based task, i.e. it does these tasks:
Runs a DMV query
Checks results of query and if appropriate takes an action
Writes what it did to the SQL error log as an info message
I use the theory for many different tasks. Feel free to use and alter it as you will, subject to the usual legal caveats of course!