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/
