check diskspace UNC via T-SQL

**Teresa Masino** · Apr 7 '06, 05:55 PM

Re: check diskspace UNC via T-SQL

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_a vailablemedia

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

EXEC master..xp_cmds hell '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) null,
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_a vailablemedia

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(' 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_cmds hell ''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..DBSpaceHi story
GROUP BY DBName, LogicalName, PhysicalName
ORDER BY DBName, LogicalName, PhysicalName

SELECT *, BytesFree = convert(numeric (18,0),
rtrim(ltrim(sub string(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(substring (PhysicalName, 1, 3)) = Drive

GO

check diskspace UNC via T-SQL

check diskspace UNC via T-SQL

Comment