SQL Server obtains disk space usage, SQL Server

SQL Server obtains disk space usage, SQL Server

For DBA, it is necessary to monitor disk usage, and there is no simple way to obtain disk space usage information. The following summarizes the scripts collected over the years:

The most common method is to view the remaining disk space. This is a required entry for DBAs:

-- View the available disk space EXEC master. dbo. xp_fixeddrives

Xp_fixeddrives can be directly used by the system. The disadvantage is that you cannot view the total disk size or the disk information not used by SQL Server.

Use the sys. dm_ OS _volume_stats Function

-- ===================================================== =======================================-- View the disk space used by database files WITH T1 AS (select distinctreplace (. volume_mount_point, ': \', '') AS Drive_Name, CAST (. total_bytes/1024.0/1024/1024 as numeric () AS Total_Space_GB, CAST (. available_bytes/1024.0/1024/1024 as numeric () AS Free_Space_GBFROM sys. master_files AS fCROSS APPLY sys. dm_ OS _volume_stats (f. database_id, f. file_id) AS vs) SELECTDrive_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 () AS Free_Space_PercentFROM T1

Query results:

The sys. dm_ OS _volume_stats function is easy to use. It can directly query the total space and free space. Unfortunately, it only supports SQL Server 2008 R2 SP1, which is a later version. In addition, it cannot find the disks on which database files are not used.

To be compatible with earlier versions, you can use the xp_fixeddrives + xp_mongoshell method. I wrote several stored procedures to obtain disk information:

USE [monitor] GO/****** Object: StoredProcedure [dbo]. [usp_get_disk_free_size] script Date: 18:21:11 *****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- ================ =================================-- Author: GGA -- Create date: 2016-2-1 -- Description: collect information about the remaining disk space -- ========================================== =========== create procedure [dbo]. [usp_get_disk_free_size] asbegin set nocount on; set transaction isolation level read uncommitted; -- ===================================================== ===-- create related table IF OBJECT_ID ('server _ disk_usage ') is nullbegin 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 [alias] 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 @ diskEXEC xp_fixeddrives -- UPDATE the remaining space information of the current disk and update mset m. [free_siez_mb] = D. [free_siez_mb] FROM [dbo]. [server_disk_usage] as minner join @ disk as don m. [disk_num] = D. [disk_num] -- INSERT the remaining space information of the new disk INTO [dbo]. [server_disk_usage] ([disk_num], [free_siez_mb]) SELECT [disk_num], [free_siez_mb] FROM @ disk as dwhere not exists (SELECT 1 FROM [dbo]. [server_disk_usage] as m where m. [disk_num] = D. [disk_num]) ENDGO/****** Object: StoredProcedure [dbo]. [usp_get_disk_total_size] script Date: 18:21:11 *****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- ================ =================================-- Author: GGA -- Create date: 2016-2-1 -- Description: collect the total disk space information -- ============================================== =========== create procedure [dbo]. [usp_get_disk_total_size] asbegin 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 -- =================================================== ==== -- enable javasshellexec sp_configure 'show advanced options ', 1; reconfigure with override; EXEC sp_configure 'xp _ Your shell', 1; reconfigure with override -- ==================================== ==== -- CREATE a temporary TABLE to store the data of each drive letter. create table # tempDisks (id int identity ), diskSpace NVARCHAR (200 )) -- ===================================================== =====-- put the disk to be checked into a temporary table # checkDisksSELECT ROW_NUMBER () OVER (order by [disk_num]) as rid, [disk_num] INTO # checkDisksFROM [dbo]. [server_disk_usage] WHERE [total_size_mb] = 0; -- ===================================================== =====-- cyclic temporary table # checkDisks check the total number of disks DECLARE @ disk_num NVARCHAR (20) DECLARE @ total_size_mb INTDECLARE @ SQL NVARCHAR (200) DECLARE @ max INTDECLARE @ min INTSELECT @ max = MAX (RID), @ min = MIN (RID) FROM # checkDisksWHILE (@ min <= @ max) BEGINSELECT @ disk_num = [disk_num] FROM # checkDisks where rid = @ minSET @ SQL = n' EXEC sys. xp_volume shell ''fsutil volume diskfree' + @ disk_num + ':' + ''' PRINT @ sqlINSERT INTO # tempDisksEXEC sys. sp_executesql @ sqlSELECT @ 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_numUPDATE [dbo]. [server_disk_usage] SET [total_size_mb] = @ resolve [disk_num] = @ disk_num -- SELECT * FROM # tempDisksTRUNCATE TABLE # tempDisksSET @ min = @ min + 1END -- ====== ====================================================== -- using shellexec sp_configure 'xp _ mongoshell ', 0; EXEC sp_configure 'show advanced options', 1; reconfigure with override; ENDGO/****** Object: StoredProcedure [dbo]. [usp_get_disk_usage] script Date: 18:21:11 *****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- ================ =================================-- Author: GGA -- Create date: 2016-2-1 -- Description: collect the total disk space information -- ============================================== =========== create procedure [dbo]. [usp_get_disk_usage] asbegin set nocount on; set transaction isolation level read uncommitted; EXEC [dbo]. [usp_get_disk_free_size] EXEC [dbo]. [distinct] SELECT [disk_num] AS Drive_Name, CAST ([total_size_mb]/1024.0 as numeric () AS Total_Space_GB, CAST ([total_size_mb]-[free_siez_mb]) /1024.0 as numeric (1024.0) AS Used_Space_GB, CAST ([free_siez_mb]/as numeric () 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] ENDGO -- ================================= -- view disk space using EXEC [dbo]. [usp_get_disk_usage]

Effect display:

Only when the disk information is collected for the first time or when new disk information is collected for the first time will xp_mongoshell be called to obtain the total disk size and minimize the risk of enabling xp_mongoshell, it can be used with the SQL Server Agent Job to regularly call the stored procedure to refresh the disk information. The monitoring program directly accesses the data table and perhaps the disk information during the last refresh.

The disadvantage of this method is that when xp_mongoshell is enabled and the total disk size is obtained, other processes may shut down xp_mongoshell, causing the execution of the stored procedure to fail. Although the probability of occurrence is low, it exists after all.

If you want to skip the Stored Procedure + SQL Server Agent Job method, you can directly call xp_mongoshell through a program. When the program uses "RECONFIGURE WITH OVERRIDE" for configuration, the following error will be reported:

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

The error is similar to the sp_configure statement that we use to wrap transactions in SSMS, for example:

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

Error message:

The 'show advanced options' configuration option has been changed from 0 to 1. Run the RECONFIGURE statement for installation. Message 574, level 16, status 0, 3rd rows cannot use the CONFIG statement in user transactions. The 'xp _ cmdshell' configuration option has been changed from 0 to 1. Run the RECONFIGURE statement for installation. Message 574, level 16, status 0, 5th rows cannot use the CONFIG statement in user transactions.

Cannot I call the RECONFIGURE WITH OVERRIDE statement through a program?

Of course not. google only found the following related errors. If you are interested, refer:

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

After a rough look, I used the stored procedure to set the stored procedure to bypass the error reporting. I did not perform a specific test and it was too cumbersome. So I adopted a simple and crude method, since "CONFIG statements cannot be used in user transactions" is reported, can I kill "User transactions" under COMMIT first?

Based on this idea, the final test gets the following method:

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)

If you have carefully discovered that I have executed the COMMIT command first, you are not mistaken. Although this open method is weird, it is indeed an open method. In SSMS, the execution result is:

The commit transaction request of message 3902, level 16, status 1, and row 2nd does not have the corresponding begin transaction. The 'show advanced options' configuration option has been changed from 1 to 1. Run the RECONFIGURE statement for installation. The 'xp _ cmdshell' configuration option has been changed from 1 to 1. Run the RECONFIGURE statement for installation.

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

After porting this code to the Code, CATCH and discard exceptions through try catch, you can call xp_javasshell happily.

Use xp_cmdshell to open the header. Of course, you can obtain the relevant information in a similar way!

For example, to obtain information about the disk sector:

-- =============================================================== -- Use xp_cmdshell to run the CMD command -- get the disk Sector Information EXEC sp_configure 'show advanced options ', 1) Comment 'xp _ shortshell', 1 GORECONFIGUREGOEXEC xp_shortshell 'fsutil fsinfo ntfsinfo D: | find "each" '; GOsp_configure 'xp _ shortshell', 0 GORECONFIGUREGOsp_configure 'show advanced options ', 0 GORECONFIGUREGO

Running effect:

Of course, you can use fsutil fsinfo ntfsinfo D: to obtain the complete information, but the above lines are worth your attention.

Comments:

After so many years of SQL Server DBA, it is really difficult to find a decent SQL SERVER DBA. On the one hand, it is caused by the current market trend, on the other hand, it was also caused by the DBA's own "death". I saw many colleagues, including myself, still in the Age of "farming with power". If there is any problem, I should click here on the interface, it gives the outside an illusion that "SQL Server is easy to operate and maintain", and then look at the MySQL DBA, as long as you can pretend to "study the source code ", immediately give people a kind of "awesome" rush, so the annual salary of 0.5 million is no longer a dream!

The above is all the content of this article. I hope this article will help you in your study or work. I also hope to provide more support to the customer's home!