For DBA, monitoring disk usage is a necessary task. There is no easy way to obtain disk space usage information. Here's a summary of the scripts saved over the years:

The most commonly used way to view disk free space, which is a must-remember entry to DBA:

-- View Disk Available Space
EXEC master.dbo.xp_fixeddrives

The xp_fixeddrives mode is a bit self-contained and can be used directly. The disadvantage is that you can't view the total size of the disk and the unused disk information of SQL Server.

==============================================================

Using the sys.dm_os_volume_stats function

--======================================================================
--View disk space usage for database files
WITH T1 AS (
SELECT DISTINCT
REPLACE(vs.volume_mount_point,':\','') AS Drive_Name ,
CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Total_Space_GB ,
CAST(vs.available_bytes / 1024.0 / 1024 / 1024  AS NUMERIC(18,2)) AS Free_Space_GB
FROM    sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
)
SELECT
Drive_Name,
Total_Space_GB,
Total_Space_GB-Free_Space_GB AS Used_Space_GB,
Free_Space_GB,
CAST(Free_Space_GB*100/Total_Space_GB AS NUMERIC(18,2)) AS Free_Space_Percent
FROM T1

Query effect:

The sys.dm_os_volume_stats function is very useful. It can query the total space and free space directly. Unfortunately, only SQL Server 2008 R2 SP1, or higher version, is supported. In addition, the unused disk of database file can not be found.

==============================================================

In order to be compatible with the low version, it can be obtained by xp_fixeddrives+xp_cmdshell. I have written several stored procedures to obtain disk information:

USE [monitor]
GO

/****** Object:  StoredProcedure [dbo].[usp_get_disk_free_size]    Script Date: 2016/5/25 18:21:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        GGA
-- Create date:    2016-2-1
-- Description:    Gathering Remaining Space Information on Disks
-- =============================================
CREATE PROCEDURE [dbo].[usp_get_disk_free_size]
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--==========================================
--Create related tables

IF OBJECT_ID('server_disk_usage') IS NULL
BEGIN
    CREATE TABLE [dbo].[server_disk_usage](
        [disk_num] [nvarchar](10) NOT NULL,
        [total_size_mb] [bigint] NOT NULL CONSTRAINT [DF_server_disk_usage_total_size_mb]  DEFAULT ((0)),
        [free_siez_mb] [bigint] NOT NULL CONSTRAINT [DF_server_disk_usage_free_siez_mb]  DEFAULT ((0)),
        [disk_info] [nvarchar](400) NOT NULL CONSTRAINT [DF_server_disk_usage_disk_info]  DEFAULT (''),
        [check_time] [datetime] NOT NULL CONSTRAINT [DF_server_disk_usage_check_time]  DEFAULT (getdate()),
         CONSTRAINT [PK_server_disk_usage] PRIMARY KEY CLUSTERED 
        (
            [disk_num] ASC
        )
    ) ON [PRIMARY]
END


--==========================================
--View the remaining disk space used by all databases
DECLARE @disk TABLE(
        [disk_num] VARCHAR(50),
        [free_siez_mb] INT)
INSERT INTO @disk
EXEC xp_fixeddrives

--Update the remaining space information of the current disk
UPDATE M
SET M.[free_siez_mb]=D.[free_siez_mb]
FROM [dbo].[server_disk_usage] AS M
INNER JOIN @disk AS D
ON M.[disk_num]=D.[disk_num]

--Insert the remaining space information of the new disk
INSERT INTO [dbo].[server_disk_usage]
(
    [disk_num],
    [free_siez_mb]
)
SELECT 
[disk_num],
[free_siez_mb]
FROM @disk AS D
WHERE NOT EXISTS(
    SELECT 1
    FROM [dbo].[server_disk_usage] AS M 
    WHERE M.[disk_num]=D.[disk_num] )

END



GO

/****** Object:  StoredProcedure [dbo].[usp_get_disk_total_size]    Script Date: 2016/5/25 18:21:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:        GGA
-- Create date:    2016-2-1
-- Description:    Collecting total disk space information
-- =============================================
CREATE PROCEDURE [dbo].[usp_get_disk_total_size]
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    
IF NOT EXISTS(SELECT * FROM [dbo].[server_disk_usage]
        WHERE [total_size_mb] = 0)
BEGIN
    RETURN;
END

--==========================================
--open CMDShell
EXEC sp_configure 'show advanced options',1;

RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'xp_cmdshell',1;

RECONFIGURE WITH OVERRIDE

--========================================
--Create temporary tables to store data for each disk character
CREATE TABLE #tempDisks
(
    ID INT IDENTITY(1,1),
    DiskSpace NVARCHAR(200)
)
--============================================
--Put the disks that need to be checked into the temporary table#checkDisks
SELECT 
ROW_NUMBER()OVER(ORDER BY [disk_num]) AS RID,
[disk_num]
INTO #checkDisks
FROM [dbo].[server_disk_usage] 
WHERE [total_size_mb] = 0;

--============================================
--Circulating Temporary Table#Check Disks checks the total number of disks per disk

DECLARE @disk_num NVARCHAR(20)
DECLARE @total_size_mb INT
DECLARE @sql NVARCHAR(200)
DECLARE @max INT
DECLARE @min INT
SELECT @max=MAX(RID),@min=MIN(RID) FROM #checkDisks

WHILE(@min<=@max)
BEGIN
SELECT @disk_num=[disk_num] 
FROM #checkDisks WHERE RID=@min

SET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree '+@disk_num+':'+''''
PRINT @sql

INSERT INTO #tempDisks
EXEC sys.sp_executesql @sql

SELECT  @total_size_mb=CAST((RIGHT(DiskSpace,LEN(DiskSpace)
    -CHARINDEX(': ',DiskSpace)-1)) AS BIGINT)/1024/1024
FROM #tempDisks WHERE id = 2

SELECT @total_size_mb,@disk_num

UPDATE [dbo].[server_disk_usage]
SET [total_size_mb]=@total_size_mb
WHERE [disk_num]=@disk_num

--SELECT * FROM  #tempDisks

TRUNCATE TABLE #tempDisks

SET @min=@min+1

END


--==========================================
--CMDShell

EXEC sp_configure 'xp_cmdshell',0;

EXEC  sp_configure 'show advanced options',1;

RECONFIGURE WITH OVERRIDE;


END




GO

/****** Object:  StoredProcedure [dbo].[usp_get_disk_usage]    Script Date: 2016/5/25 18:21:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        GGA
-- Create date:    2016-2-1
-- Description:    Collecting total disk space information
-- =============================================
CREATE PROCEDURE [dbo].[usp_get_disk_usage]
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    
    EXEC [dbo].[usp_get_disk_free_size]
    EXEC [dbo].[usp_get_disk_total_size]
    
    SELECT 
    [disk_num] AS Drive_Name
    ,CAST([total_size_mb]/1024.0 AS NUMERIC(18,2)) AS Total_Space_GB
    ,CAST(([total_size_mb]-[free_siez_mb])/1024.0 AS NUMERIC(18,2)) AS Used_Space_GB
    ,CAST([free_siez_mb]/1024.0 AS NUMERIC(18,2)) AS Free_Space_GB
    ,CAST([free_siez_mb]*100/[total_size_mb] AS NUMERIC(18,2)) AS Free_Space_Percent
    ,[disk_info]
    ,[check_time]
    FROM [monitor].[dbo].[server_disk_usage]

END
GO


--==================================
--View Disk Space Use
EXEC [dbo].[usp_get_disk_usage]
 

The results show that:

Only the first time to collect disk information or the first time to collect new disk information will xp_cmdshell be invoked to obtain the total size of the disk and minimize the risk of xp_cmdshell opening. It can be used in conjunction with SQL Server Agent Job, periodically call stored procedures to refresh disk information, and monitor programs directly access data tables or Allow disk information for the last refresh.

The disadvantage of this method is that other processes may shut down xp_cmdshell and cause stored procedure execution failure during the period of getting the total disk size after opening xp_cmdshell. Although the probability is low, it still exists after all.

==============================================================

If you want to skip the stored procedure + SQL Server Agent Job mode and call xp_cmdshell directly through the program, when the program is configured with "RECONFIGURE WITH OVERRIDE", the following error will be reported:

CONFIG statement cannot be used inside a user transaction.DB-Lib error message 574

Errors are similar to our use of transaction wrapping sp_configuration statements in SSMS, such as:

BEGIN TRAN
EXEC sp_configure 'show advanced options',1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell',1;
RECONFIGURE WITH OVERRIDE;
COMMIT

The error message is:

The configuration option'show advanced options'has been changed from 0 to 1. Please run the RECONFIGURE statement for installation.
Message 574, level 16, status 0, line 3
CONFIG statements cannot be used in user transactions.
The configuration option'xp_cmdshell'has been changed from 0 to 1. Please run the RECONFIGURE statement for installation.
Message 574, level 16, status 0, line 5
CONFIG statements cannot be used in user transactions.

Can't the RECONFIGURE WITH OVERRIDE statement be invoked programmatically?

Of course not, the relevant errors under google, only found the following correlation, interested can refer to the following:

https://www.sqlservercentral.com/Forums/Topic1349778-146-1.aspx

After a rough look, I used the stored procedure suite stored procedure to bypass the error reporting. I did not have specific tests and felt too cumbersome. So I used a simple and rough way. Since the report "CONFIG statement can not be used in user affairs", how can I get rid of "user affairs" under COMMIT first?

Based on this idea, the final test obtains the following ways:

DECLARE @sql VARCHAR(2000)
SET @sql ='
COMMIT;
EXEC sp_configure ''show advanced options'',1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ''xp_cmdshell'',1;
RECONFIGURE WITH OVERRIDE;
'
EXEC(@sql)

Careful friends found that I first executed COMMIT, you see correctly, although this open way is strange, but it is indeed an open way, the results of execution in SMS are as follows:

Message 3902, level 16, status 1, line 2
COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
The configuration option'show advanced options'has been changed from 1 to 1. Please run the RECONFIGURE statement for installation.
The configuration option'xp_cmdshell'has been changed from 1 to 1. Please run the RECONFIGURE statement for installation.

Although the error is reported, but the value of xp_cmdshell has been set to 1, that is, the script execution takes effect!

Porting this code into the code, and then capturing and discarding exceptions through TRY CATCH, you can happily call xp_cmdshell.

==============================================================

Start with xp_cmdshell, and of course the relevant information can be obtained in a similar way!

For example, get sector information of disk:

--====================================
--Use xp_cmdshell Execution CMD command
--Getting disk sector information
EXEC sp_configure 'show advanced options',1 
GO
RECONFIGURE
GO
sp_configure 'xp_cmdshell',1 
GO
RECONFIGURE
GO
EXEC xp_cmdshell 'fsutil fsinfo ntfsinfo D: | find "each"';
GO
sp_configure 'xp_cmdshell',0 
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0 
GO
RECONFIGURE
GO

The operation effect is as follows:

Of course, you can use fsutil fsinfo ntfsinfo D: to get complete information, but more noteworthy are the lines above.

==============================================================

Feelings:

After so many years of SQL Server DBA, it is not easy to find a decent job of SQL SERVER DBA. On the one hand, it is caused by the current market trend, on the other hand, it is also caused by our own "death" of DBA. I see that many colleagues, including myself, are still in the "slash and burn" era. If there are problems, I have to point to the interface. Give the outside world the illusion that "SQL Server is easy to operate and maintain", and look at MySQL DBA, as long as you can pretend to "study the source code", immediately give people a "very compelling" catch-up, so the annual salary of 3.5 million is no longer a dream!

The slogan of Intelligent Operations and Maintenance has sounded. On the way to MySQL, I still remember that I am an old SQL Server DBA from time to time.

==============================================================

Chairman Chiang Kai-shek's words are encouraged by all the gentlemen.