SQL Server Daily Health Check Powershell Script
I wrote this to provide Peace of Mind of the health of my SQL Servers when I found a number of miscellaneous reasons why I was not receiving notifications of various failures through the normal (immediate) alert process when jobs failed or backups didn't verify successfully. It is not intended as a replacement for the immediate alerts or detailed logging, just a daily dashboard all servers that had issues in the prior day. It is easily customizable to add additional checks and is not necessarily a SQL Server-only tool. It could be altered to be an Oracle, Postgres, or MySQL Daily Health check, if desired.
DownloadsDaily Health Check Powershell Script
Create logging table script
Sample server list text file
DISCLAIMERTHE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ON INFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
Version history04/09/2014 v0.8.2
In Check 1 convert sjh.run_date from an integer '20140409' to a datetime in returned message.
Add variable to $excludeSnapshots if VEEAM is configured as "SQL Aware"AND SQL volumes are NOT included in the VEEAM backup. Added try/catch blocks to checks 1-4 to log possible connection timeout (or other general exception). Refactor individual checks into calls to the new RunCheckNumber function.
Add $debug variable and don't insert into db when running in debug mode. Changed d.Name and d.NAME to d.name. In Check 2 add AND b.is_snapshot=0 to the join to exclude possible VEEAM snapshot backups that don't have a physical backup file/location associated with them. After a vCenter crash and rebuild, one of my servers with an identical SQL version to another that isn't showing a daily full started showing daily successful FULL's (isSnapshot=1 physical_device_name is a guid) in addition to the daily DIFF's run with the normal agent job, so they stopped showing up on the report as exceptions.
Change report query to add DISTINCT, commented out ID and CreatedOn so if it was run multiple consecutive times it would not duplicate results. Add Check 6 currently running jobs (looking for long running daily full backups still running).
Revise Check 4 to select max(backup_finish_date)
Rewrite Check 3, 4, 5 queries
Add variables for logging server and database. Add emailing from this script.
add where clause to check 5 to exclude SQL Express instance checks since there is not SQL Agent for the Express Edition
put try catch finally block around step 5 to fix issue. turned out to be server name field varchar(20) was too short for sqlexpress instance. changed to varchar(50)
write to database table
Added a check of last full database backup