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

SQL Server Monitoring Scripts - 4 - Missing Transaction Log Backups

We don't want any databases in full recovery going without their log backups so here is a script to let us know when we have one. You will need to adjust the @Threshold variable to your specific needs (which is true for all of the scripts in this series) and then you are set. This script takes into account whether the database is mirrored, it's status, and if it's a system database.

Set NoCount On
Set ANSI_Warnings Off
Select '---------------Log Backup Check---------------'
Declare @MissingLogBackups Table(ID Int Identity, DatabaseName VarChar(100))
Declare @Threshold Int
Select @Threshold = 4
Insert Into @MissingLogBackups (DatabaseName)
Select d.[name] From sys.databases d 
Left Join msdb.dbo.backupset b On d.[name] = b.database_name
And b.[type] = 'L' 
Inner Join sys.database_mirroring m On d.[database_id] = m.[database_id]
Where DatabasePropertyEx(d.[name], 'Recovery') <> 'SIMPLE'
and DatabasePropertyEx(d.[name], 'Status') = 'ONLINE'
And d.[name] Not In ('master', 'msdb', 'tempdb', 'model')
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 @MissingLogBackups) > 0
Begin
Declare @Count Int, @Counter Int
Select @Count = Max(ID) From @MissingLogBackups
Select @Counter = Min(ID) From @MissingLogBackups
While @Counter <= @Count
Begin
Select 'Server: ' + Cast(ServerProperty('MachineName') As sysname) + IsNull('\' + Cast(ServerProperty('InstanceName') As sysname), '') + 
'. Missing log backups for DB: ' + DatabaseName From @MissingLogBackups Where ID = @Counter
Set @Counter = @Counter + 1
End
End

SQL Server Monitoring Scripts - 3 - Login Failures

In this script, I gather information on failed login attempts so that if there are any users trying to access data which they are not authorized for or if there is a default database in a connection string that might not exist, etc. we can address the issue. You can change the @LastCheck variable to whatever interval you run the checks.

Set NoCount On
Set ANSI_Warnings Off
Select '---------------Login Failure Check---------------'
Declare @LastCheck DateTime
Select @LastCheck = DateAdd(dd, -2, 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'Login', N'failed'
Delete From #ErrLog1 Where LogDate <= @LastCheck
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 - 2 - Low Disk Space

The second monitoring script in this series is a simple script for obtaining disk space information and alerting if it is below a given threshold. Once again, the formatting is specific to the purpose of using it with the remote PowerShell script which is the reason for the formatting.

Set NoCount On
Set ANSI_Warnings Off
Select '---------------Disk Space Check---------------'
Declare @Threshold Int
Set @Threshold = 30720 --30 GB
Declare @AllDrives Table (drive Char(1), [MB free] Int)
Declare @LowDrives Table (ID Int Identity, Drive Char(1), MBFree Int)
Insert Into @AllDrives
Exec master.dbo.xp_fixeddrives
Insert Into @LowDrives (Drive, MBFree)
Select drive, [MB free] From @AllDrives Where [MB free] <= @Threshold
If (Select Count(*) From @LowDrives) > 0
Begin
Declare @Count Int, @Counter Int
Select @Count = Max(ID) From @LowDrives
Select @Counter = Min(ID) From @LowDrives
While @Counter <= @Count
Begin
Select 'Server: ' + Cast(ServerProperty('MachineName') As sysname) + IsNull('\' + Cast(ServerProperty('InstanceName') As sysname), '') + 
' low disk space: ' + Drive + ' = ' + Cast(MBFree As VarChar(10)) + ' is below threshold of ' + Cast(@Threshold As VarChar(10))
From @LowDrives Where ID = @Counter
Set @Counter = @Counter + 1
End
End

SQL Server Monitoring Scripts - 1 - High Log Usage

I was recently in a position where I needed to monitor several SQL Server instances on disparate machines and I didn't have a central server instance that I could make use of for data collection, job scheduling, etc. and I was not able to create any custom scheduled tasks in Windows (the scope of "administration" in this case is very narrow) so I ported my standard monitoring scripts to a format that could be used from a remote location and then created a PowerShell script to run all of my scripts against all of the specified servers and pipe the output to a single file per server. This method allows you to easily add servers and scripts to your process and modify input and output locations as necessary. I will first be providing the scripts and then the PowerShell script to wrap it all together.

The first script is for detecting any databases which are currently utilizing a high percentage of the log so that you can catch possible issues such as replication, log backup issues, long-running transactions, etc. Again, these particular scripts are for use with the PowerShell script which is the reason for the options specified. For this script, a minimum size is specified so that we don't get alerted to a 20 MB transaction log that has a usage which exceeds our threshold of 30%. Also, in these scripts, I use the ServerProperty function to guarantee a value is returned in case the global server name variable is returning a null value or previous machine name.



Set NoCount On
Set ANSI_Warnings Off
Select '---------------Log Usage Check---------------'
Declare @PercentThreshold Int, @SizeThreshold Int, @MinSize Int, @SQL VarChar(100)
Set @SQL = 'DBCC SQLPerf(LogSpace)'
Set @PercentThreshold = 30
Set @SizeThreshold = 10240 --10 GB
Set @MinSize = 512
Declare @LogSpace Table (DatabaseName VarChar(100), LogSize Real, LogSpaceUsed Real, [Status] Int)
Declare @LargeLogs Table (ID Int Identity, DatabaseName VarChar(100), LogSpaceUsed Real, LogSize Real)
Insert Into @LogSpace
Exec(@SQL)
Insert Into @LargeLogs (DatabaseName, LogSpaceUsed, LogSize)
Select DatabaseName, LogSpaceUsed, LogSize From @LogSpace Where (LogSpaceUsed >= @PercentThreshold) And LogSize >= @MinSize
If (Select Count(*) From @LargeLogs) > 0
Begin
Declare @Count Int, @Counter Int
Select @Count = Max(ID) From @LargeLogs
Select @Counter = Min(ID) From @LargeLogs
While @Counter <= @Count
Begin
Select 'Log threshold violation for DB: ' + DatabaseName + ' on server: ' + 
Cast(ServerProperty('MachineName') As sysname) + IsNull('\' + Cast(ServerProperty('InstanceName') As sysname), '') + 
'. Percent used: ' + Cast(LogSpaceUsed As VarChar(15)) + ', size: ' + Cast(LogSize As VarChar(15)) 
From @LargeLogs Where ID = @Counter
Set @Counter = @Counter + 1
End
End