Zajętość dysków serwera SQL

Zapytanie wzracające ilość miejsca na dysku na którym znajdują się pliki bazodanowe

SELECT distinct(volume_mount_point),
total_bytes/1048576 as Size_in_MB,
available_bytes/1048576 as Free_in_MB,
(select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage
FROM sys.master_files AS f CROSS APPLY
sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576,
available_bytes/1048576 order by 1

volume_mount_point   Size_in_MB           Free_in_MB           FreePercentage
----------------------------------------------------------------------------------------------
C:\                  99511                15444                15.519892273216

(1 row affected)

Niestety zapytanie to zwraca TYLKO dysk z plikami bazy danych. Nie zwraca on zajętości pozostałych dysków na serwerze.

Możemy użyć procedury SQL zwracajacej ilość wolnego miejsca ale to też nie są wystarczające informacje

EXEC master..xp_fixeddrives

drive MB free
----- -----------
C     15446
D     1773
E     39597
F     104710
G     21279

(5 rows affected)

Najlepszym rozwiązaniem jakie znalazłem jest wykorzystanie zapytania połączonego z poleceniem PowerShell


DECLARE @svrName VARCHAR(255)
DECLARE @sql VARCHAR(400)
DECLARE @output TABLE (line VARCHAR(255))

--by default it will take the current server name, we can the SET the server name as well

SET @svrName = @@SERVERNAME
IF CHARINDEX ('\', @svrName) > 0
SET @svrName = SUBSTRING(@svrName, 1, CHARINDEX('\',@svrName)-1)

SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | SELECT name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

--INSERTing disk name, total space and free space value in to temporary table

INSERT @output
EXEC xp_cmdshell @sql

--script to retrieve the values in MB FROM PS Script output

SELECT RTRIM(LTRIM(SUBSTRING(line,1,CHARINDEX('|',line) -1))) AS drivename
,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) AS FLOAT),0) AS 'capacity(MB)'
,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) AS FLOAT),0) AS 'freespace(MB)'
,CAST (((ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) AS FLOAT),0)))*100/
(ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) AS FLOAT),0)) AS INT) AS 'freespace %'

FROM @output
WHERE line LIKE '[A-Z][:]%'
ORDER BY drivename

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 0]
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

(0 rows affected)

Wykonanie tego zapytania wymaga włączonej funkcji XP_CMDSHELL na serwerze SQL:

EXEC sp_configure 'show advanced options', '1'
RECONFIGURE
-- this enables xp_cmdshell
EXEC sp_configure 'xp_cmdshell', '1'
RECONFIGURE

Ponowne wykonania zapytania zwraca:

(8 rows affected)
drivename		capacity(MB)           freespace(MB)          freespace %
---------------------------------------------------------------------------------
C:\				99512                  15440                  15
D:\				143695                 1773                   1
E:\				169289                 39598                  23
F:\				150000                 104710                 69
G:\				157646                 21279                  13

(5 rows affected)

Więcej na :

Monitoring SQL Server with PowerShell Core Object Setup (mssqltips.com)
https://www.mssqltips.com/sqlservertip/6354/monitoring-sql-server-with-powershell-core-object-setup/

How To Check Disk Space in SQL Server (mssqltips.com)
https://www.mssqltips.com/sqlservertip/6731/how-to-check-disk-space-in-sql-server/

Ten wpis został opublikowany w kategorii Microsoft SQL. Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz