3. SQL Server database Status Monitoring

The database is used to store data. Therefore, it is necessary to monitor disk space. 1. Available disk space 1. Operating System commands or scripts, interfaces or tools (1) doscommand: fsutilvolume

The database is used to store data. Therefore, it is necessary to monitor disk space. 1. Available disk space 1. Operating System commands or scripts, interfaces or tools (1) doscommand: fsutilvolume

The database is used to store data. Therefore, it is necessary to monitor disk space.

I. Available disk space

1. Operating System commands, scripts, interfaces, or tools

(1) doscommand: fsutil volume diskfree

C: \ windows \ system32> fsutil volume diskfree C:

Total # of free bytes: 9789493248

Total # of bytes: 64424505344

Total # of avail free bytes: 9789493248

Here, fsutil is used, a file system utility. There should be other commands or scripts.

(2) WMI/WMIC: wmic logicaldisk

WMI is a management interface for Windows systems. Before WMIC appeared, if you want to use the WMI management system, you must use some specialized WMI applications, such as SMS, or use the WMI Script Programming API, alternatively, you can use tools such as CIM Studio. It is difficult to use the WMI management system if you are not familiar with programming languages such as C ++ or VBScript or do not have basic knowledge about WMI namespaces. WMIC changes this situation and provides a powerful and friendly command line interface for the WMI namespace.

C: \ windows \ system32> wmic logicaldisk get caption, freespace, size

Caption FreeSpace Size

C: 9789071360 64424505344

D: 189013438464 255331397632

Here, several parameter columns of logicaldisk are obtained through the wmic get command.

(3) Performance Monitor

LogicalDisk: % Free Space

LogicalDisk: Free Megabytes

Total size = LogicalDisk: Free Megabytes/LogicalDisk: % Free Space

Although the Performance Monitor is very convenient for field diagnosis, it is not very easy to implement automated monitoring.

2. SQL statements

(1) Extended Stored Procedure xp_mongoshell (still calling the operating system command)

DECLARE @ Drive TINYINT, @ SQL VARCHAR (100) DECLARE @ Drives TABLE (Drive CHAR (1), Info VARCHAR (80 )) SET @ Drive = 97 WHILE @ Drive <= 122 begin set @ SQL = 'exec xp_{shell ''fsutil volume diskfree' + CHAR (@ Drive) + ': '''insert @ Drives (Info) EXEC (@ SQL) UPDATE @ Drives SET Drive = CHAR (@ Drive) WHERE Drive IS NULL SET @ Drive = @ Drive + 1 ENDSELECT Drive, SUM (case when Info LIKE 'total # of bytes % 'then cast (REPLACE (SUBSTRING (Info, 32, 48), CHAR (13), '') as bigint) else cast (0 as bigint) END)/1024.0/1024/1024 AS TotalMBytes, SUM (case when Info LIKE 'total # of free bytes % 'then cast (REPLACE (SUBSTRING (Info, 32, 48), CHAR (13), '') as bigint) else cast (0 as bigint) END)/1024.0/1024/1024 AS FreeMBytes, SUM (case when Info LIKE 'total # of avail free bytes % 'then cast (REPLACE (SUBSTRING (Info, 32, 48), CHAR (13), '') as bigint) else cast (0 as bigint) END)/1024.0/1024/1024 AS AvailFreeMBytesFROM (SELECT Drive, Info FROM @ Drives WHERE Info LIKE 'total # of % ') AS dGROUP BY DriveORDER BY Drive

Xp_cmdshell can execute the command line of the operating system. This script uses the fsutil volume diskfree command to traverse the drive letters of 26 letters. It is not very good. It is easier to use wmic instead, as shown below:

EXEC xp_mongoshell 'wmic logicaldisk get caption, freespace, size ';

(2) Extended Stored Procedure xp_fixeddrives

-- Exec xp_fixeddrivesIF object_id ('tempdb .. # drivefreespace ') is not nulldrop table # drivefreespaceCREATE TABLE # drivefreespace (Drive CHAR (1), FreeMb bigint) INSERT # drivefreespace EXEC ('exec xp_fixeddrives') SELECT * FROM # drivefreespace

Drive FreeMb

C 9316

D 180013

It does not depend on the operating system command. However, this stored procedure can only return the available disk space without the total disk space.

(3) DMV/DMF: sys. dm_ OS _volume_stats

Select distinct @ SERVERNAME as [server], volume_mount_point as drive, cast (available_bytes/1024.0/1024.0/1024.0 as int) as free_gb, cast (total_bytes/1024.0/1024.0/1024.0/as int) as total_gbFROM sys. master_files AS fCROSS APPLY sys. dm_ OS _volume_stats (f. database_id, f. file_id) order by @ SERVERNAME, volume_mount_point

Server drive free_gb total_gb

... C: \ 9 59

... D: \ 175 237

Starting from SQL Server 2008 R2 SP1, this useful DMF: sys. dm_ OS _volume_stats makes up for the lack of total disk space in xp_fixeddrives.

However, you can see from its parameters that the disks that are not used by any databases cannot be viewed. Therefore, xp_fixeddrives is necessary.

2. available database space

1. view the available file space