SQL Server Storage Report

When working with Oracle databases, especially Oracle Cloud Control 12C, it was easy enough for me to generate a daily report early morning that would be mailed to all the stakeholders regarding the Storage Usage for all the Oracle instances. This mail would contain information something like shown below:

Oracle Storage Report

Oracle Storage Report

One look at the email early morning would allow me and my team to work on creating enough space on the servers with the label ‘LOW SPACE’, so that there is no bottleneck in terms of storage shortage.

As I was working on this server storage report for Oracle, I wondered how the same could be done for SQL Servers. It was then when I stumbled upon this piece of code, that allowed me to generate similar server storage reports for SQL Servers. That piece of code is as shown below:

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 = @@SERVERNAME
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName '+@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

The output from the above query gave me results as shown below:

SQL Server Storage Report

SQL Server Storage Report

Beware that the query shown above uses xp_cmdshell and also requires powershell. Let me know what you think about this method, and also share, how do you generate such report for multiple servers under your administration/monitoring?

Advertisements
This entry was posted in ms sql, reporting and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s