365DBA Blog

Database Stuff

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
Insert Into @LargeLogs (DatabaseName, LogSpaceUsed, LogSize)
Select DatabaseName, LogSpaceUsed, LogSize From @LogSpace Where (LogSpaceUsed >= @PercentThreshold) And LogSize >= @MinSize
If (Select Count(*) From @LargeLogs) > 0
Declare @Count Int, @Counter Int
Select @Count = Max(ID) From @LargeLogs
Select @Counter = Min(ID) From @LargeLogs
While @Counter <= @Count
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