Finding available drive space of databases stored on a LUN

Question

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?

Answer 1

what happens when you run

exec master..xp_fixeddrives

Answer 2

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

Answer 3

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