quote:

---

Originally posted by Wanderer
...My gut says that it'll be less intrusive, and it certain will be easier to store that data into a table and track trends, usage etc., using the sp_diskspace stored procedure...

---

drop table #t
go
create table #t (
output varchar(2000) null,
seq int not null identity(1,1) primary key clustered )
go
set nocount on
insert into #t ( output )
exec master.dbo.xp_cmdshell 'psinfo \\MYSERVER -d'

select
	drive_info = substring(output,1,80) 
from #t
where
	left(output,11) = 'Volume Type' or
	substring(output,6,7) = ': Fixed'

drive_info                                                                       
-------------------------------------------------------------------------------- 
Volume Type       Format     Label                      Size       Free   Free
    C: Fixed      NTFS       LOCAL_C                33.91 GB   26.29 GB  77.5%
    D: Fixed      NTFS       LOCAL_D               102.11 GB   64.83 GB  63.5%
    E: Fixed      NTFS       LOCAL_E               102.11 GB   74.73 GB  73.2%
    F: Fixed      NTFS       LOCAL_F               102.11 GB   65.72 GB  64.4%
    G: Fixed      NTFS       LOCAL_G               102.11 GB   65.37 GB  64.0%
    H: Fixed      NTFS       LOCAL_H                34.04 GB   27.54 GB  80.9%
    R: Fixed      NTFS       LOCAL_R               399.96 GB  374.34 GB  93.6%

set nocount on

declare @ServerToCheck table
	(LineId					Int		identity (1,1),
	 ServerName			varchar(250),
	 enabled				bit)
Declare 
	@ServerCheck varchar(250),
	@DynamicSQL		varchar(500)

Create table #TemporaryDiskTable  
	(LineOutput varchar(2000) null, seq int not null identity(1,1) primary key clustered )

Insert into @ServerToCheck (ServerName, Enabled) values ('\\Rgalbraith',1)
Insert into @ServerToCheck (ServerName, Enabled) values ('\\Rgalbraith',1)

declare ServerSpaceDetailsCursor cursor for 
	select ServerName from @ServerToCheck where Enabled = 1

open ServerSpaceDetailsCursor
fetch next from ServerSpaceDetailsCursor into @ServerCheck

While @@fetch_status  = 0
begin 
	set @DynamicSQL = 'master.dbo.xp_cmdshell ''psinfo ' + @ServerCheck + ' -d'''
--	set @DynamicSQL = @DynamicSQL 
--	select @DynamicSQL
	insert into #TemporaryDiskTable ( LineOutput )
	exec (@DynamicSQL)

	fetch next from ServerSpaceDetailsCursor into @ServerCheck
end

select
	drive_info = substring(LineOutput,1,80) 
from #TemporaryDiskTable
where
	left(LineOutput,11) = 'Volume Type' or
	substring(LineOutput,6,7) = ': Fixed'

drop table #TemporaryDiskTable

drive_info                                                                       
-------------------------------------------------------------------------------- 
Volume Type       Format     Label                      Size       Free   Free
    C: Fixed      NTFS                              28.31 GB   15.84 GB  55.9%
    D: Fixed      NTFS       New                    13.67 GB   10.39 GB  76.0%
    E: Fixed      NTFS       New                    13.80 GB   11.84 GB  85.8%
Volume Type       Format     Label                      Size       Free   Free
    C: Fixed      NTFS                              28.31 GB   15.84 GB  55.9%
    D: Fixed      NTFS       New                    13.67 GB   10.39 GB  76.0%
    E: Fixed      NTFS       New                    13.80 GB   11.84 GB  85.8%

quote:

---

Originally posted by TRACEYSQL

Hi would that work if my databases are held on a SAN and not the local server Drives..

i.e get the size left on the I drive

Thanks

---

quote:

---

Originally posted by schuhtl

I did not write the sp below and I can't remember where I found it but I think it will work for you...you might have to tweak it a bit to get exactly what you are looking for!

use master
go

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

---