Disk size

• Subhash-63067

  SSCarpal Tunnel

  Points: 4171

  More actions

  March 1, 2005 at 4:34 am

  #86924

  is there anyway to get the size and freespace available in local disk (C, D etc..) through t-sql

  
  subban

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 1, 2005 at 5:02 am

  #544570

  See if this helps:

  http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=153114

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Subhash-63067

  SSCarpal Tunnel

  Points: 4171

  More actions

  March 1, 2005 at 5:58 am

  #544579

  Ok this helped me

  
  subban

• Steve Morrow

  SSC-Addicted

  Points: 485

  More actions

  March 2, 2005 at 8:43 am

  #544907

  Here's another one.

   

  CREATE PROCEDURE sp_diskspace

  AS

  /*

     Displays the free space,free space percentage

     plus total drive size for a server

  */

  SET NOCOUNT ON

   

  DECLARE @hr int

  DECLARE @fso int

  DECLARE @drive char(1)

  DECLARE @odrive int

  DECLARE @TotalSize varchar(20)

  DECLARE @MB bigint ; SET @MB = 1048576

   

  CREATE TABLE #drives (drive char(1) PRIMARY KEY,

                        FreeSpace int NULL,

                        TotalSize int NULL)

   

  INSERT #drives(drive,FreeSpace)

  EXEC master.dbo.xp_fixeddrives

   

  EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

   

  DECLARE dcur CURSOR LOCAL FAST_FORWARD

  FOR SELECT drive from #drives

  ORDER by drive

   

  OPEN dcur

   

  FETCH NEXT FROM dcur INTO @drive

   

  WHILE @@FETCH_STATUS=0

  BEGIN

   

          EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

          IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

         

          EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

          IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

                         

          UPDATE #drives

          SET TotalSize=@TotalSize/@MB

          WHERE drive=@drive

         

          FETCH NEXT FROM dcur INTO @drive

   

  END

   

  CLOSE dcur

  DEALLOCATE dcur

   

  EXEC @hr=sp_OADestroy @fso

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

   

  SELECT drive,

         FreeSpace as 'Free(MB)',

         TotalSize as 'Total(MB)',

         CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'

  FROM #drives

  ORDER BY drive

   

  DROP TABLE #drives

   

  RETURN

  GO

  Good Luck,

  Steve

  
  Steven R. Morrow