Thursday, February 14, 2013

Monitoring Disk space using T-SQL and Powershell

UPDATE: New version with bug fixes. Now works on named instances !! (2014-10-14)

Hello everybody,

Here is a handy script that allows you to get disks information using T-SQL and Powershell. It is useful to monitor the free space on each disk so we can create a sql job to run it periodically and send out a notification when space is getting low

Here is the script


USE master
GO
SET NOCOUNT ON
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 = cast(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') as varchar(255))
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace,Label | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''&''+$_.label+''*''}"'
--creating a temporary table
DECLARE @output TABLE
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert @output
EXEC xp_cmdshell @sql

DECLARE @DISKS TABLE(
id int identity
,[DiskName] varchar(10)
,[Capacity(MB)] bigint
,[FreeSpace(MB)] bigint
,[Label] varchar(200)
)

INSERT INTO @DISKS
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)'
      ,rtrim(ltrim(SUBSTRING(line,CHARINDEX('&',line)+1,      (CHARINDEX('*',line) -1)-CHARINDEX('&',line)) )) as 'Label'
from @output
where line like '[A-Z][:]%'
order by drivename


select *
,[Capacity(MB)]/1024 as [Capacity(GB)],[FreeSpace(MB)]/1024 as [FreeSpace(GB)]

,round( [FreeSpace(MB)]*100/[Capacity(MB)],2) as [% Free]




16 comments:

  1. Hi Javier,

    I ran the above script on my lapton am getting only the columns no result set value. Please advise what to do? I need to enable any option in instance level.
    Thanks for your script.

    Redars,
    Raj

    ReplyDelete
  2. You need to check
    xp_cmdshell if it's enabled from sp_configure
    check if powershell it's installed. Try to execute the query directly in powershell.

    ReplyDelete
  3. Hi,
    The script above will only work on a default instance. If you are running a named instance @@servername returns the hostname \ instance name pair.

    If you use the value of
    SERVERPROPERTY ('ComputerNamePhysicalNetBIOS')

    or
    SERVERPROPERTY ('MachineName')

    will return the host machine name and allow the powershell part of the script to run correctly.

    You will have to Cast the output value of the function to varchar (255) as it is sql_variant data type.

    Have fun.
    Richard

    ReplyDelete
  4. xp_fixeddrives provides basically the same information in a easier way.

    ReplyDelete
    Replies
    1. ummm no it doesnt. "Basically" only shows the drive and free space, I need the assigned space and free percent, so basically no....

      Delete
    2. Then you should check this one
      http://sql-javier-villegas.blogspot.com.ar/2012/03/databases-info.html

      Delete
  5. Hi,

    Can you tell me how can I configure the Jobs in SQL Server (using Management Studio) to run some scripts, sql scripts, sql statements, etc, and how can I configure the mail sending in SQL Server in way to receive the results of SQL jobs in my email address.

    Can you teach me that please?!

    Thank you,
    jmgmad

    ReplyDelete
  6. Anonymous, since you have such far reaching questions I would suggest you read the sql server books online for the various topics you are interested in.

    What you are asking is much more than would be doable in an online forum and is more something that you would hire a contractor to do for you.

    ReplyDelete
  7. Keeping tabs on available disk space on your SQL Servers is something that every DBA should do, because once SQL Server runs out of disk on the underlying host, then everything obviously comes to a crashing halt.

    ReplyDelete
  8. Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure

    to all the commenters here! Its always nice when you can not only be informed, but also entertained! Im sure you had fun

    writing this article.
    Hadoop online training

    ReplyDelete
  9. This a great script. Do you know anything about how I can get a disk space for an Instance in SQL Server? I wanted to get a script that will fetch the SQL Server Instance Disk Space not the Drives. I am migrating and I need to know the specific Instance disk usage. Any tip can help

    ReplyDelete
    Replies
    1. You can try this
      http://sql-javier-villegas.blogspot.com.ar/2012/03/databases-info.html
      -- Javier

      Delete
  10. xp_cmdshell is enabled in sp_configure, but i couldn't get the results. Please help me..

    ReplyDelete
  11. To those getting no results in the table, The powershell command, after the QUOTENAME function needs to be all on one line.

    Thanks a lot for this script, should allow me to create some reports and projections on data growth.

    ReplyDelete