Search This Blog

SQL SERVER MONITORING: SQL Server Local Drive Details


This is one of most important taks of DBA to make sure that the server must have sufficient disk space available to let the SQL Server work continueously. There are many free tools and script available which do the same to let you know about the disk space availability.
Step 1:

 -- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;

GO
Step2:

 USE [ToolBox]
GO
/****** Object:  StoredProcedure [dbo].[GetLocalDiskReportDetails]    Script Date: 05-12-2014 18:04:04 ******/
DROP PROCEDURE [dbo].[GetLocalDiskReportDetails]
GO
USE [ToolBox]
GO

 /****** Object:  StoredProcedure [dbo].[GetLocalDiskReportDetails]    Script Date: 05-12-2014 17:44:23 ******/
SET ANSI_NULLS ON
GO

 SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetLocalDiskReportDetails]
--exec  GetLocalDiskReportDetails
AS
BEGIN
     SET NOCOUNT ON;
 DECLARE @p_RecordCounts INT;
 DECLARE @p_DriveLetter CHAR(1);
DECLARE @p_SQL_Query NVARCHAR(4000);
DECLARE @p_Drive CHAR(1);
DECLARE @DriveLetterInfo AS TABLE
 (
 DriveLetter CHAR(1),
 MB_free BIGINT
 );
-- CREATE TABLE DriveLetterInfoID
DECLARE @DriveLetterInfoID AS TABLE
 (
ID INT,
DriveLetter CHAR(1),
MB_free BIGINT
      );
--CREATE TABLE DriveDetails
DECLARE @DriveDetails AS TABLE
 (
SizeDetails NVARCHAR(1000)
 );
 --CREATE TABLE DriveDetailsFinal
 DECLARE @DriveDetailsFinal AS TABLE
 (
Drive CHAR(1),
TotalSize_GB DECIMAL(18,2),
FreeSize_GB DECIMAL(18,2),
UsedSize_GB DECIMAL(18,2)
 );
 SET @p_SQL_Query = 'EXEC master..xp_fixeddrives';

       

       INSERT INTO @DriveLetterInfo
 EXEC (@p_SQL_Query);
 --1
 print(@p_SQL_Query);
      

       INSERT INTO @DriveLetterInfoID

       SELECT ROW_NUMBER() OVER (ORDER BY DriveLetter) AS RowNo, DriveLetter, MB_Free FROM @DriveLetterInfo;

       

       SELECT @p_RecordCounts = COUNT(*) FROM @DriveLetterInfoID;
 print(@p_RecordCounts);
 WHILE @p_RecordCounts != 0
      BEGIN
    SELECT @p_Drive = DriveLetter FROM @DriveLetterInfoID WHERE ID = @p_RecordCounts;
SET @p_SQL_Query = 'EXEC Master..xp_cmdshell ''fsutil volume diskfree ' + @p_Drive + ':'''
--2
PRINT @p_SQL_Query;

                  -- RETURN;

                   INSERT INTO @DriveDetails
                  EXEC (@p_SQL_Query);
 --3
PRINT @p_SQL_Query;
                  
 --select * from DriveDetailsFinal
                  INSERT INTO @DriveDetailsFinal
                  SELECT

                         @p_Drive AS Drive,

                         CAST((((CAST(A.TotalBytes AS DECIMAL(18,2))/1024)/1024)/1024) AS DECIMAL(18,2)) AS 'Total Size (GB)',

                         CAST((((CAST(A.FreeBytes AS DECIMAL(18,2))/1024)/1024)/1024) AS DECIMAL(18,2)) AS 'Free Size (GB)',

                         CAST(((((CAST(A.TotalBytes AS DECIMAL(18,2)) - CAST(A.FreeBytes AS DECIMAL(18,2)))/1024)/1024)/1024) AS DECIMAL(18,2)) AS 'Used Size (GB)'

                   FROM

                   (

                         SELECT           

                               SUM(CASE WHEN SizeDetails LIKE 'Total # of bytes             : %' THEN CAST(REPLACE(SUBSTRING(SizeDetails, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,

                               SUM(CASE WHEN SizeDetails LIKE 'Total # of free bytes        : %' THEN CAST(REPLACE(SUBSTRING(SizeDetails, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,

                               SUM(CASE WHEN SizeDetails LIKE 'Total # of avail free bytes  : %' THEN CAST(REPLACE(SUBSTRING(SizeDetails, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes

                         FROM

                               (

                                     SELECT SizeDetails FROM @DriveDetails WHERE      SizeDetails LIKE 'Total # of %'

                               ) AS tmp

                   ) AS A;

                   

                  -- DELETE FROM DriveDetails;

                   
 print(@p_RecordCounts)

                   SET @p_RecordCounts = @p_RecordCounts -1;
 print(@p_RecordCounts)
                  

             END;

       

       SELECT * FROM @DriveDetailsFinal ORDER BY Drive;
END;

The result will look like: