Problem Statement:
While troubleshooting an issue in SQL Server, I wanted to know the space information of the server. Since we did not have access to the server, we have come up with a simple script as below. I am sharing the script here hoping this helps you in similar situation.Script:
declare @svrName varchar(255) declare @sql varchar(400) --User Options(By default Server name) set @svrName = Cast(SERVERPROPERTY('MachineName') as varchar(255)) 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 #Details (line varchar(255)) --inserting disk name, total space and free space value in to temporary table insert #Details EXEC xp_cmdshell @sql --Retrieve the Capacity values in GB from PS Script Details 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 #Details where line like '[A-Z][:]%' order by drivename --Drop the temporary table drop table #Details
See Also:
Using windows PowerShell to get the server disk space I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!