SQL Health Powershell

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.

Downloads

Daily Health Check Powershell Script

Create logging table script

Sample server list text file

fwPASS presentation

DISCLAIMER

THE 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 history

04/09/2014 v0.8.2 
In Check 1 convert sjh.run_date from an integer '20140409' to a datetime in returned message.

03/28/2014 v0.8.1
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.

3/13/2014 v0.8.0
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.

10/29/2013 v0.7.0
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).

10/24/2013 v0.6.1
Revise Check 4 to select max(backup_finish_date)

10/23/2013 v0.6.0
Rewrite Check 3, 4, 5 queries

10/22/2013 v0.5.0
Add variables for logging server and database. Add emailing from this script.

08/17/2013 v0.4.0
add where clause to check 5 to exclude SQL Express instance checks since there is not SQL Agent for the Express Edition

07/30/2013 v0.3.0
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)

06/28/2013 v0.2.0
write to database table

06/28/2013 v0.1.0
Added a check of last full database backup