Finding available drive space of databases stored on a LUN

We have a multitude of databases whose files are stored on their own individual mount points in a drive

(ex. Z:\dbname_db\dbname_db.mdf and Z:\dbname_log\dbname_log.ldf)

What I'm looking for is a way to find the available free space of the mount point.

    EXEC xp_cmdshell 'fsutil volume diskfree Z:\dbname_db'

But the service isn't running as administrator so fsutil will not work.

How would I go about doing this, is a stored procedure the most efficient way to do it?

From stackoverflow

• what happens when you run

  exec master..xp_fixeddrives
  

  Slipfish : xp_fixeddrives will give me the root drive free space. What I'm really looking for is the free space on the LUN, which is the mount point.

  SQLMenace : I see, not sure how you would do it without running cmdshell

  Slipfish : To Clarify, drive free ----- ---- C 6260 Z 971 Z is the host drive for multiple mount points. I want the space left on those mount points.

• xp_fixeddrives will not display information for mount points, only normal fixed drives.

  The following link has a CLR procedure that will use a Performance Counter to return the capacity and free space of a mount point.

  http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx

• HI, See the below script, whic will disply total size and free space of Fxied and mount volumes. To run this sxcript Enable WMIC(go to DOS type WMIC, which will instal WMIC first time) and XP_CMDShell.

  set arithignore on set nocount on go declare @dsize varchar(20) declare @SQL nvarchar(500) set @sql='xp_cmdshell ''fsutil volume diskfree ' --+'D:'''+'' create table #Dletter (Drive varchar(50), ) Create Table #Size (Sinfo varchar(250)) Create Table #DriveInfo (Drive varchar(30), TotalSize real, Freesize real)

  --set @x='xp_cmdshell''wmic volume get capacity,caption,freespace''' insert into #Dletter EXEC xp_cmdshell'wmic volume where drivetype="3" get caption' set rowcount 1 delete from #Dletter set rowcount 0 delete from #Dletter where drive is null or len(drive)<4 update #dletter set drive= replace(drive,' ','') --delete from #Dletter where Drive like'R:\%' --delete from #Capacity where Dcapacity is null or len(Dcapacity)<15 --delete from #dletter where -- convert(int,substring(drive,len(drive)-1,len(drive)))=5 --SELECT Row_Number() OVER (order by Drive asc) as RowNumber,drive from #Dletter declare @dv varchar(30) declare dx cursor for select * from #dletter open dx fetch next from dx into @dv while @@fetch_status=0 begin

   set @sql=@sql+@dv+''''
  

  -- print @sql insert into #Size EXEC sp_executesql @sql delete from #size where sinfo is null or sinfo like'Total # of avail free bytes :%' --select Drive from #dletter --insert into #DriveInfo(Drive,TotalSize,Freesize) select @dv as Drive ,convert(real,substring(sinfo,isnull(charindex(':',sinfo),0)+2,len(isnull(sinfo,0))))/1024/1024/1024 as Size into #rama from #size order by 2 asc declare @d varchar(30) declare @s real declare @cntr int set @cntr=1

   declare x cursor for
   select * from #rama order by 2 desc
   open x
   fetch next from x into @d,@s
   while @@fetch_status=0
   begin
  
    if @cntr=1
     begin
  
      insert into #Driveinfo(Drive,Totalsize)
      values(@d,@s)
      --print convert(char,@cntr)+' '+convert(varchar,@d)+'TotalSize:'+convert(varchar, @s)
     end
    if @cntr=2
     begin
  
      update #DriveInfo set Freesize=@s where drive=@d
      --print convert(char,@cntr)+' '+convert(varchar,@d)+'FreeSize:'+convert(varchar, @s)
     end
   fetch next from x into @d,@s
   set @cntr=@cntr+1
   end
   Close x
   deallocate x
   Drop table #rama
   delete from #size
   Set @Cntr=1
  

  fetch next from dx into @dv --print @sql set @sql='xp_cmdshell ''fsutil volume diskfree ' --+'D:'''+''

  end close dx deallocate dx
  select Drive,convert(decimal(10,2),TotalSize) as "TotalSize(GB)",convert(decimal(10,2),FreeSize) as "FreeSize(GB)" from #DriveInfo order by drive drop table #Dletter drop table #size Drop table #DriveInfo