Goran Milanov Blog

Pitanje koliko još ima prostora na diskovima je verovatno najčešće pitanje koje sebi postavljaju administratori SQL Server-a (vrlo često sa strepnjom) što zbog svakodnevnog održavanja što što zbog toga što treba da se kreira nova baza.

Odgovor na to pitanje može da znači potrebu da se neka baza migrira na drugi disk ili da treba da se razmišlja recimo o komprimovanju. Šta god bio povod za postavljanje pitanja o preostalom slobodnom prostoru na diskovima, odgovor može da se dobije na više načina.

Najjednostavniji način za dobijanje odgovora o tome koliko je još preostalo slobodnog prostora na diskovima je korišćenje  SQL Server stored procedure (Primer 1):

exec master.sys.xp_fixeddrives

Primer 1. Korišćenje stored procedure xp_fixeddrives

Izvršavanjem TSQL koda iz primera 1 se dobija:

Slika 1. Rezultat izvršavanja stored procedure xp_fixeddrives

Naravno, da bi mogla da se izvrši ova stored procedura potrebne su adekvatne privilegije na master bazi.

Drugi način je korišćenje FSUTIL alata operativnog sistema. Za njegovo korišćenje su potrebne administrativne privilegije na nivou operativnog sistema a takođe je potrebno uključiti xp_cmdshell na nivou SQL Server-a. U primeru 2 je dat TSQL kod za korišćenje FSUTIL alata.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE;
GO
--------------------------------------------

DECLARE	@Drive TINYINT,
	@SQL VARCHAR(100)

SET	@Drive = 97

-- Setup Staging Area
DECLARE	@Drives TABLE
	(
		Drive CHAR(1),
		Info VARCHAR(80)
	)

WHILE @Drive <= 122
	BEGIN
		SET	@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''

		INSERT	@Drives
			(
				Info
			)
		EXEC	(@SQL)

		UPDATE	@Drives
		SET	Drive = CHAR(@Drive)
		WHERE	Drive IS NULL

		SET	@Drive = @Drive + 1
	END

-- Show the expected output
SELECT		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) AS TotalBytes,
		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) AS FreeBytes,
		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) AS AvailFreeBytes
FROM		(
			SELECT	Drive,
				Info
			FROM	@Drives
			WHERE	Info LIKE 'Total # of %'
		) AS d
GROUP BY	Drive
ORDER BY	Drive

GO
-------------------------------------------

EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

Primer 2. Korišćenje FSUTIL-a

Izvršavanje ovog TSQL koda dobija se rezultat koji je dat na slici 2.

Slika 2. Rezultat korišćenja FSUTIL-a

Treći način za dobijanje odgovora o tome koliko je još preostalo slobodnog prostora na diskovima je korišćenje powershell-a (Primer 3). Za korišćenje powershell-a su neophodne adekvatne privilegije.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE;
GO

-------------------------------------------------------------

declare @svrName varchar(255)
declare @sql varchar(400)
--by default it will take the current server name, we can the set the server name as well
set @svrName = CONVERT(VARCHAR(128),SERVERPROPERTY('machinename') )-- @@SERVERNAME
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+''*''}"'
--creating a temporary table
CREATE TABLE #output
(line varchar(255))
--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 GB 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)/1024,0) as 'capacity(GB)'
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #output
where line like '[A-Z][:]%'
order by drivename
--script to drop the temporary table
drop table #output

-------------------------------------------

EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

Primer 3. Korišćenje powershell-a

Korišćenjem TSQL koda iz primera 3 dobija se rezultat koji je dat na slici 3.

Slika 3. Rezultat korišćenja powershell-a