Hello Geeks,
Welcome to the new series of seven wonders of SQL Server. These are not the wonders which make you say Aww. These are the ones which make you really wonder why. I will be covering the seven of such jaw dropping situations where you wonder what is happening and few bizarre reasons why it is the way it is.
Last week I have blogged about the max server memory setting and why it is important to configure the setting. This week I will blog about the daunting task of space monitoring for DBAs.
When was the last time you were proactively informed about a drive running out of space? Are all your drives monitored? What should be the ideal thresholds? When was the last time you as a DBA were involved in setting up the space monitoring alerts? A lot of questions which are really important.
To add to already existing challenges, as hosting multiple instances of SQL server on single machine or cluster has increased. With the limitation on drive letters, usage of mount points has increased. With simple commands in SQL server like xp_fixeddrives, it is misleading and hard to monitor the free space available in mount points.
If you have a third party tool like SCOM, it is easier to set a universal policy to monitor all the drives. But it is highly complicated to set threshold for individual drives? For example, for a drive of 100 GB holding log files of size 50 GB you can set the threshold to less than 10% free space for alerts. But for a drive of 2 TB holding data files of 1.85 TB you can wait till 50 GB is left rather than 10% which is 200 GB.
There are further considerations like the auto growth setting of files in the drive, etc. The thresholds should be customizable based on the usage of the drive. The worst case I have seen is the threshold set to 5 MB as there have been many alerts. The tool did not have an option to set individual alerts for each drive. It was a bulk setting across all drives. You do not want to have such monitoring on your database environment.
So, let us design a solution. These are the requirements so far.
- Monitors mount points.
- Have customizable thresholds (e.g.: drive < 50 GB raise alert for 5 GB, drive between 50 GB and 500 GB raise alert for 10% etc.).
- Have exceptions if we want to skip a drive or set it on a different threshold than rest.
With this requirement I have decided to use wmi query to get the drive free space details including mount points.
wmic volume where drivetype=3 get caption, freespace, capacity, label
Now, it just needs some filtering to figure out if it is a cluster or a non-clustered SQL instance. This will help to get only the drive details related to the SQL instance. So the first part of the requirement to collect the disk space related data is as follows.
DECLARE @xpCmd NVARCHAR(4000)
DECLARE @isclustered INT = 0
SET NOCOUNT ON
IF EXISTS(SELECT 1 FROM master.sys.dm_os_cluster_nodes)
SET @isclustered = 1
IF (OBJECT_ID('tempdb..#temp') is not null)
DROP TABLE #temp
CREATE TABLE #temp (sample VARCHAR(max))
IF (OBJECT_ID('tempdb..#temp2') is not null)
DROP TABLE #temp2
CREATE TABLE #temp2 (
[SERVER] SYSNAME
,driveletter VARCHAR(max)
,total_mb BIGINT
,freespace BIGINT
,label VARCHAR(max)
)
SET @xpCmd = 'wmic volume where drivetype=3 get caption, freespace, capacity, label'
INSERT INTO #temp
EXEC xp_cmdshell @xpCmd
DECLARE @excluded VARCHAR(max)
,@str1 VARCHAR(max)
,@str2 VARCHAR(max)
,@str3 VARCHAR(max)
,@str4 VARCHAR(max)
DECLARE @idx INT
,@i INT
DECLARE @slice VARCHAR(max)
SELECT @idx = 1
DELETE
FROM #temp
WHERE sample LIKE (
SELECT TOP 1 sample
FROM #temp
) AND len(RTRIM(ltrim(sample)))>=1 OR len(RTRIM(ltrim(sample))) IS NULL
OR len(RTRIM(ltrim(sample))) = 1
OR sample like '%----------------%'
OR sample like 'Capacity%'
OR sample = 'NULL'
OR sample LIKE '% rows affected%'
OR sample LIKE '%system reserved%'
SELECT @i = COUNT(*)
FROM #temp
WHILE (@i <>0)
BEGIN
SELECT TOP 1 @excluded = ltrim(rtrim(sample))
FROM #temp
SET @idx = charindex(' ', @excluded)
IF @idx != 0
BEGIN
SET @slice = left(@excluded, @idx - 1)
SET @str1 = @slice
SET @slice = ''
END
SET @excluded = right(@excluded, len(@excluded) - @idx)
SET @excluded = rtrim(ltrim(@excluded))
SET @idx = charindex(' ', @excluded)
IF @idx != 0
BEGIN
SET @slice = left(@excluded, @idx - 1)
SET @str2 = @slice
SET @slice = ''
END
SET @excluded = right(@excluded, len(@excluded) - @idx)
SET @excluded = rtrim(ltrim(@excluded))
SET @idx = charindex(' ', @excluded)
IF @idx != 0
BEGIN
SET @slice = left(@excluded, @idx - 1)
SET @str3 = @slice
SET @slice = ''
END
SET @excluded = right(@excluded, len(@excluded) - @idx)
SET @excluded = rtrim(ltrim(@excluded))
SET @str4 = @excluded
SET @excluded = rtrim(ltrim(@excluded))
INSERT INTO #temp2
SELECT @@SERVERNAME
,@str2
,cast(@str1 AS BIGINT) / 1024 / 1024
,cast(@str3 AS BIGINT) / 1024 / 1024
,@str4
DELETE
FROM #temp
WHERE sample LIKE (
SELECT TOP 1 sample
FROM #temp
);
SET @i = @i - 1
END
IF (@isclustered = 1)
BEGIN
SELECT
server,
driveletter,
total_mb AS DriveSize_MB,
freespace AS FreeSize_MB,
label,
(freespace*100/total_mb) AS percent_free
FROM #temp2
WHERE SUBSTRING(driveletter,1,1) in
(SELECT DriveName FROM sys.dm_io_cluster_shared_drives)
ORDER BY percent_free
END
ELSE
SELECT
server,
driveletter,
total_mb AS DriveSize_MB,
freespace AS FreeSize_MB,
label,
(freespace*100/total_mb) AS percent_free
FROM #temp2
ORDER BY percent_free
SET NOCOUNT OFF
I will cover the second part of the requirement in the blog next week. Till I blog the second part which actually sends the alert based on thresholds, use this script to monitor the size of drives including mount points in your environment and share the feedback.
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
