Tuesday, August 6, 2019

Old and forgotten xp_availablemedia procedure

If you plan to use "xp_availablemedia" please stop right now!

It is very old and outdated procedure and it might be deprecated by Microsoft at any moment.

I am writing this post because I've spent an hour trying to decode it's output and found the method only in an ancient book "SQL Server 2000 Stored Procedures Handbook" from 2003!
You can buy it on Amazon: https://www.amazon.com/Server-Stored-Procedures-Handbook-Experts/dp/1590592875 and it still very valuable and costs more than $30.

So, giving credits to the authors Robin Dewson, Tony Bain, Chuck Hawkins & Louis Davidson for the great job.

For those who hit my post trying to figure out what "xp_availablemedia" is I'll give you the formula I've got from the book.

Here is a compare of values from xp_availablemedia output and an output from newer and recommended procedure xp_fixeddrives:
DECLARE @availablemedia TABLE (name SYSNAME, [low free] INT, [high free] INT, mediatype INT)
DECLARE @fixeddrives TABLE (drive CHAR(1), mbfree INT)
INSERT INTO @availablemedia EXEC master..xp_availablemedia 2;
INSERT INTO @fixeddrives EXEC master..xp_fixeddrives;

SELECT m.*
       , [Free Gb] = CAST(4 * (IIF(m.[low free]<0,1,0)+m.[high free]) + m.[low free] / 1073741824. AS DECIMAL(19,1))
       , [Free Mb] = CAST(4096 * (IIF(m.[low free]<0,1,0)+m.[high free]) + m.[low free] / 1048576. AS DECIMAL(19,0))
       , [Drives Free Mb] = d.mbfree
FROM @availablemedia as m
INNER JOIN @fixeddrives as d
       ON LEFT(m.name,1) = d.drive;

As you can see, the difference in numbers is very minor, but xp_fixeddrives does not have any hassles with additional calculations:

Here is the Book!!!


No comments:

Post a Comment