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 UNCYou 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 (
namevarchar(20)null,
lowfreeintnull,
highfree intnull,
mediatype intnull
)
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
SELECTname
FROM#DriveList
ORDER BY name
OPEN mycursor
FETCH mycursor INTO @.Drive
IF CURSOR_STATUS('variable', '@.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
SELECTDBName, LogicalName, PhysicalName, MinSize = min(SizeMB),
MaxSize = max(SizeMB), MinDate = min(StatusDate), MaxDate =
Max(StatusDate), MaxSizeMB = max(MaxSizeMB)
INTO#SpaceList
FROMmsdb..DBSpaceHistory
GROUP BY DBName, LogicalName, PhysicalName
ORDER BY DBName, LogicalName, PhysicalName
SELECT*, BytesFree = convert(numeric(18,0),
rtrim(ltrim(substring(replace(DirResults, ' bytes free', ''), 26,
50))))
INTO#SpaceOnDisk
FROM#DirList
WHEREDirResults LIKE '%bytes free%'
SELECTDBName = 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
WHEREUPPER(substring(PhysicalName, 1, 3)) = Drive
GO
