365DBA Blog

Database Stuff

SQL Server Monitoring Scripts - 9 - Putting It All Together

So now that you have some scripts to perform basic monitoring and status checking, you want to run it against your collection of servers and set it up as an automated process, so in comes PowerShell to help you out. The following script reads a list of servers that you specify in a separate text file and loops through all files with a .sql extension in a folder that you specify, running them against your servers and outputting the results into your specified output folder. All of it can be easily customized by changing a few lines here and there. Also, the commented sqlcmd line is for using SQL authentication and passing in the user name and password parameters when calling the PS script so if using SQL authentication, all user names and passwords will have to be the same.

#######################################
#PS Script (change the variables as necessary):

#The list of server to run the scripts against:
$ServerListFile = "C:\Users\davidzahner\Documents\PSScripts\ServerList1.txt"
$Servers = Get-Content $ServerListFile

#Set the folder location and file type filter:
$path = "C:\Users\davidzahner\Documents\PSScripts"
$filter = "*.sql"

#Set the output folder:
$OutputFolder = "C:\Users\davidzahner\Documents\CheckResults"

#Set U and P values from parameters:
$User = $Args[0]
$Pass = $Args[1]

$Date = Get-Date -UFormat "%Y%m%d"
$Date = $Date -replace "/", "_"

ForEach ($Server in $Servers)
{
$ServerString = $Server -replace "\\", "-"
$OutputFile = $OutputFolder + "\" + $Date + "__" + $ServerString + ".txt"
get-childitem -recurse -path $path -filter $filter | % {
        $file = $_
        #Sqlcmd -S $Server -U$User -P$Pass -i $file.FullName | Out-File -append -filePath "$OutputFile"
        Sqlcmd -S $Server -E -i $file.FullName | Out-File -append -filePath "$OutputFile"
    }
}

#######################################
#Server List Example:
SERVER05
SERVER05\INSTANCE2
SERVER04
SERVER04\INSTANCE2
SERVER03
SERVER03\INSTANCE2
SERVER02
SERVER02\INSTANCE2
SERVER01
SERVER01\INSTANCE2

SQL Server Monitoring Scripts - 8 - General SQL Errors Check

To wrap up the SQL scripts for this segment, here is a script to comb the logs and look for some common errors. You can add and remove checks by modifying the calls to xp_readerrorlog so you can catch errors that you are most concerned about in your environment.

Set NoCount On
Set ANSI_Warnings Off
Select '---------------General SQL Errors Check---------------'
Declare @LastCheck DateTime
Select @LastCheck = DateAdd(dd, -365, GetDate())
If (@LastCheck Is Null)
Begin
Set @LastCheck = '1-1-2000'
End
If Object_ID('tempdb.dbo.#ErrLog1') Is Not Null
Drop Table #ErrLog1;
Create Table #ErrLog1 (LogDate DateTime, ProcessInfo VarChar(50), LogText VarChar(2000))
Insert Into #ErrLog1 (LogDate, ProcessInfo, LogText) 
Exec xp_readerrorlog 0, 1, N'STACK DUMP'
Insert Into #ErrLog1 (LogDate, ProcessInfo, LogText)
Exec xp_readerrorlog 0, 1, N'Severity';
Insert Into #ErrLog1 (LogDate, ProcessInfo, LogText)
Exec xp_readerrorlog 0, 1, N'Login', N'failed';
Insert Into #ErrLog1 (LogDate, ProcessInfo, LogText)
Exec xp_readerrorlog 0, 1, N'I/O requests';
Delete From #ErrLog1 Where LogDate <= @LastCheck
Delete From #ErrLog1 Where LogText Like 'Error: 18456%' --Login Failed (removing since we are capturing it explicitly above)
Delete From #ErrLog1 Where LogText Like 'Error: 18210%' --VDI Backup Failures (http://support.microsoft.com/kb/2497575)
Delete From #ErrLog1 Where LogText Like 'Error: 3041%' --Log Backup Failures (removing since we are capturing it explicitly in other scripts)

Select Cast(LogDate As VarChar(25)), Left(LogText, 500) From #ErrLog1
If Object_ID('tempdb.dbo.#ErrLog1') Is Not Null
Drop Table #ErrLog1;

SQL Server Monitoring Scripts - 7 - SQL Restart Check

This is a simple script to let you know if the SQL instance has been restarted in the past 24 hours (configurable with the @Threshold variable. 

Set NoCount On
Set ANSI_Warnings Off
Select '---------------SQL Restart Check---------------'
Declare @RestartTime DateTime, @Threshold DateTime
Select @RestartTime = crdate From sys.sysdatabases Where [name] = 'tempdb'
Select @Threshold = DateAdd(hh, -24, GetDate())
If DateDiff(mi, @Threshold, @RestartTime) > 5
Begin
Select 'Server: ' + Cast(ServerProperty('MachineName') As sysname) + IsNull('\' + Cast(ServerProperty('InstanceName') As sysname), '') + ' restarted at: ' + Cast(@RestartTime As VarChar(25))
End

SQL Server Monitoring Scripts - 6 - Missing Differential Backups

And to round out the missing backups, we need to add the check for missing differentials (if your backup strategy includes differential backups). As with the other related scripts, the threshold is in hours so you can adjust as necessary. 

Set NoCount On
Set ANSI_Warnings Off
Select '---------------Differential Backup Check---------------'
Declare @MissingBackups Table(ID Int Identity, DatabaseName VarChar(100))
Declare @Threshold Int
Select @Threshold = 28 --hours
Insert Into @MissingBackups (DatabaseName)
Select d.[name] From sys.databases d 
Left Join msdb.dbo.backupset b On d.[name] = b.database_name
And b.[type] = 'I' 
Inner Join sys.database_mirroring m On d.[database_id] = m.[database_id]
Where DatabasePropertyEx(d.[name], 'Updateability') = 'READ_WRITE'  
And DatabasePropertyEx(d.[name], 'Status') = 'ONLINE'
And d.[name] Not In ('tempdb')
And ((m.mirroring_guid Is Null)
Or (m.mirroring_guid Is Not Null And mirroring_role = 1))
Group By d.[name]
Having IsNull(Max(b.backup_finish_date), '1/1/2000') < DateAdd(hh, (-1 * @Threshold), GetDate())
If (Select Count(*) From @MissingBackups) > 0
Begin
Declare @Count Int, @Counter Int
Select @Count = Max(ID) From @MissingBackups
Select @Counter = Min(ID) From @MissingBackups
While @Counter <= @Count
Begin
Select 'Server: ' + Cast(ServerProperty('MachineName') As sysname) + IsNull('\' + Cast(ServerProperty('InstanceName') As sysname), '') + 
'. Missing differential backups for DB: ' + DatabaseName From @MissingBackups Where ID = @Counter
Set @Counter = @Counter + 1
End
End

SQL Server Monitoring Scripts - 5 - Missing Full Backups

We also want to know about any databases that do not have a full backup; maybe they're new or maybe the backup failed for some reason and we weren't notified, whatever the case, it definitely deserves some investigating.

Set NoCount On
Set ANSI_Warnings Off
Select '---------------Full Backup Check---------------'
Declare @MissingBackups Table(ID Int Identity, DatabaseName VarChar(100))
Declare @Threshold Int
 --This threshold is in hours so you can specify the total hours or 24 * number of days in your full backup cycle
Select @Threshold = (24 * 7)
Insert Into @MissingBackups (DatabaseName)
Select d.[name] From sys.databases d 
Left Join msdb.dbo.backupset b On d.[name] = b.database_name
And b.[type] = 'D' 
Inner Join sys.database_mirroring m On d.[database_id] = m.[database_id]
Where DatabasePropertyEx(d.[name], 'Updateability') = 'READ_WRITE' 
And DatabasePropertyEx(d.[name], 'Status') = 'ONLINE' 
And d.[name] Not In ('tempdb')
And ((m.mirroring_guid Is Null)
Or (m.mirroring_guid Is Not Null And mirroring_role = 1))
Group By d.[name]
Having IsNull(Max(b.backup_finish_date), '1/1/2000') < DateAdd(hh, (-1 * @Threshold), GetDate())
If (Select Count(*) From @MissingBackups) > 0
Begin
Declare @Count Int, @Counter Int
Select @Count = Max(ID) From @MissingBackups
Select @Counter = Min(ID) From @MissingBackups
While @Counter <= @Count
Begin
Select 'Server: ' + Cast(ServerProperty('MachineName') As sysname) + IsNull('\' + Cast(ServerProperty('InstanceName') As sysname), '') + 
'. Missing full backups for DB: ' + DatabaseName From @MissingBackups Where ID = @Counter
Set @Counter = @Counter + 1
End
End