|
|
Posted by Teresa Masino on 10/02/33 11:44
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) 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_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('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
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(substring(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
Navigation:
[Reply to this message]
|