check diskspace UNC via T-SQL

Hello,
I have an EM Job script that backups up my databases using SQLsafe to a
target UNC.
Sometimes, the target server that will store the backups is off line or
it's disk is full.
I am wondering how I can check to see if the machine is up via the job
and secondly, check the disk space.
If either check fails, I would then check another machine machine.
Any ideas appreciated,
Thanks
Rob
SQL 2000 Server and Enterprise, Windows 2003
SQL 2005 Server and Etnerprise, Windows 2003
Target storage is a Windows 2003 and I connect via UNC

You can use a couple of extended procs to get what you want.  I have a
stored proc that jumps through some hoops to give me that information.

First I call:  EXEC master.dbo.xp_availablemedia

That returns a list of devices on the database server.  I loop over the
results from that and do:

EXEC master..xp_cmdshell 'DIR /-C <drive>'

and I look for the line that has "bytes free" and parse that for the
number.

It's not terribly elegant or fancy, but it does the job.  The SQL for
the stored proc is below if you're curious.  I also reference a table
that I created in msdb to help me track growth over time.  You can just
eliminate that part.

Hope it helps,
Teresa Masino


CREATE procedure sp_checkdbspace
AS
SET nocount ON

CREATE TABLE #DriveList (
 name	varchar(20)	nul
l,
lowfree	int		null,
highfree int		null,
mediatype int		null
)

CREATE TABLE #DirList (
Drive	   varchar(20)  null,
DirResults varchar(255) null
)

INSERT INTO #DriveList EXEC master.dbo.xp_availablemedia

DECLARE @Drive varchar(20),
@CMD varchar(255)

DECLARE  mycursor CURSOR
FOR
SELECT	name
FROM	#DriveList
ORDER BY name

OPEN mycursor

FETCH mycursor INTO @Drive

IF  CURSOR_STATUS('varia
ble', '@mycursor') = 0
BEGIN
PRINT 'No such device'
CLOSE mycursor
DEALLOCATE mycursor
return
END

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @CMD = 'insert into #DirList (DirResults) EXEC
master..xp_cmdshell ''DIR /-C ' + @Drive + ''''
EXEC (@CMD)
UPDATE #DirList SET Drive = @Drive WHERE Drive IS NULL
FETCH mycursor INTO @Drive
END

CLOSE mycursor
DEALLOCATE mycursor

SELECT	DBName, LogicalName, PhysicalName, MinSize = min(SizeMB),
MaxSize = max(SizeMB), MinDate = min(StatusDate), MaxDate =
Max(StatusDate), MaxSizeMB = max(MaxSizeMB)
INTO	#SpaceList
FROM	msdb..DBSpaceHistory
GROUP BY DBName, LogicalName, PhysicalName
ORDER BY DBName, LogicalName, PhysicalName

SELECT	*, BytesFree =  convert(numeric(18,0
),
 rtrim(ltrim(substrin
 g(replace(DirResults
, ' bytes free', ''), 26,
50))))
INTO	#SpaceOnDisk
FROM	#DirList
WHERE	DirResults LIKE '%bytes free%'

SELECT	DBName =  convert(varchar(20),
 DBName),
PhysicalName =  convert(varchar(60),
 PhysicalName),
MaxSize,
Growth = MaxSize - MinSize,
DiskMBFree =  convert(numeric(10,3
), BytesFree / 1048576),
GrowthPeriod = datediff(day, MinDate, MaxDate),
DaysLeft =  convert(numeric(10,3
), (BytesFree / 1048576) /  CASE WHEN
(MaxSize - MinSize) <= 0 THEN 1 ELSE ((MaxSize - MinSize) /
datediff(day, MinDate, MaxDate)) END)
FROM	#SpaceList, #SpaceOnDisk
 WHERE	UPPER(substrin
g(PhysicalName, 1, 3)) = Drive

GO