Looking for the inverse of xp_fixeddrives

Question

Looking for the inverse of xp_fixeddrives

Jeff W

SSCertifiable

Points: 5207
More actions
April 27, 2004 at 9:04 am

#85486

master..xp_fixeddrives returns the amount of free space on the fixed drives. I am looking for a command that may return the amount of total space on the fixed drives. Or maybe even a command that will return the total amount of space if the specific drive is specified...
Any ideas?

Jeff

"Keep Your Stick On the Ice" ..Red Green

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

RSP SSCrazy Points: 2614 More actions · Answer 1

Well, I've always wondered why MS couldn't just add a darn column with total space, but it was not to be.

The only way, I know of, is to use other methods outside SQL Server, such as a VB or ASP script, that uses the FSO (file system object).

Also, you can use Srvinfo.exe which is in the W2K Resource kit. It does output a lot of info about the server,services, uptime, etc., but also all the disk volumes with total, free, and used space.

You can execute this via xp_cmdshell, and perhaps insert it into a table, and then get just the disk info. Just an idea that hopefully you can work with.

HTH

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

You can also get this from VBScript using the FileSystemObject.

cchitanu SSC-Addicted Points: 450 More actions · Answer 3

cchitanu

SSC-Addicted

Points: 450

April 28, 2004 at 5:45 am

#504473

Send me an email at cchitanu@csc.com

Imrancs SSC Enthusiast Points: 127 More actions · Answer 4

you can use SP_OACreate, SP_OAMethod and other SP_OA sps to use ole objects in SPs just like in VB or VBS

Imran

SpiceDBA SSC Veteran Points: 221 More actions · Answer 5

I thought I found this script on this website. Below is a script that can be run against the server to get the fixed drive total space:

CREATE PROCEDURE sp_diskspace

AS

SET NOCOUNT ON

DECLARE @hr int

DECLARE @fso int

DECLARE @drive char(1)

DECLARE @odrive int

DECLARE @TotalSize varchar(20)

DECLARE @MB bigint ; SET @MB = 1048576

CREATE TABLE #drives (ServerName varchar(15),

drive char(1) PRIMARY KEY,

FreeSpace int NULL,

TotalSize int NULL,

FreespaceTimestamp DATETIME NULL)

INSERT #drives(drive,FreeSpace)

EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD

FOR SELECT drive from #drives

ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0

BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives

SET TotalSize=@TotalSize/@MB, ServerName = host_name(), FreespaceTimestamp = (GETDATE())

WHERE drive=@drive

FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur

DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT ServerName,

drive,

TotalSize as 'Total(MB)',

FreeSpace as 'Free(MB)',

CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',

FreespaceTimestamp

FROM #drives

ORDER BY drive

DROP TABLE #drives

RETURN

GO

I hope this helps,

McDBA

Jeff W SSCertifiable Points: 5207 More actions · Answer 6

The script is awesome!!!

"Keep Your Stick On the Ice" ..Red Green

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 7

I agree it is a great script.

I did minor mods to to handle checking remote servers. Also, if you fire it from the desktop, it will return the server's name not the workstations name.

This way you can conslidate your data to one location.

------------------------------

CREATE PROCEDURE cp_diskspace

@ServerName sysname

AS

SET NOCOUNT ON

DECLARE @hr int

DECLARE @fso int

DECLARE @drive char(1)

DECLARE @odrive int

DECLARE @TotalSize varchar(20)

DECLARE @MB bigint ; SET @MB = 1048576

if @ServerName is null or @ServerName =''

begin

select @ServerName = @@servername + '.master.dbo.xp_fixeddrives'

end

else select @ServerName = @ServerName + '.master.dbo.xp_fixeddrives'

CREATE TABLE #drives (ServerName varchar(15),

drive char(1) PRIMARY KEY,

FreeSpace int NULL,

TotalSize int NULL,

FreespaceTimestamp DATETIME NULL)

INSERT #drives(drive,FreeSpace)

EXEC @ServerName

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

IF @hr 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD

FOR SELECT drive from #drives

ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0

BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

IF @hr 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

IF @hr 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives

SET TotalSize=@TotalSize/@MB, ServerName = replace( @ServerName , '.master.dbo.xp_fixeddrives',''), FreespaceTimestamp = (GETDATE())

WHERE drive=@drive

FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur

DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso

IF @hr 0 EXEC sp_OAGetErrorInfo @fso

SELECT ServerName,

drive,

TotalSize as 'Total(MB)',

FreeSpace as 'Free(MB)',

CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',

FreespaceTimestamp

FROM #drives

ORDER BY drive

DROP TABLE #drives

RETURN

GO

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

andyobl Right there with Babe Points: 767 More actions · Answer 8

Great script. I used to use srvinfo.exe but this is a much more elegant solution. I made some minor adjustments to email me and additional checking when disk space reaches a critical point.

I found the bottom bit in a forum somewhere. I think it's this one. Sorry I lost the author's name.

Anyway, here you go:-

/*

we use this table to store all DBA task

*/

use ServerAdmin

go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ssp_DiskSpace]')

and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[ssp_DiskSpace]

GO

CREATE PROCEDURE ssp_DiskSpace

@ServerName sysname

AS

SET NOCOUNT ON

DECLARE @emailaddress VARCHAR (100)

DECLARE @SubjectText VARCHAR (200)

DECLARE @SubjectText1 VARCHAR (200)

DECLARE @MSG VARCHAR(400)

DECLARE@DSPACE INT

DECLARE@threshold INT

DECLARE @hr INT

DECLARE @fso INT

DECLARE @drive CHAR(1)

DECLARE @odrive INT

DECLARE @TotalSize VARCHAR(20)

DECLARE @MB BIGINT ; SET @MB = 1048576

IF @ServerName is null or @ServerName =''

BEGIN

SELECT @ServerName = @@servername + '.master.dbo.xp_fixeddrives'

END

ELSE

SELECT @ServerName = @ServerName + '.master.dbo.xp_fixeddrives'

CREATE TABLE TBLdrive (

ServerName varchar(15),

drive char(1) PRIMARY KEY,

FreeSpace int NULL,

TotalSize int NULL,

FreespaceTimestamp DATETIME NULL

)

INSERT TBLdrive(drive,FreeSpace)

EXEC @ServerName

-- This is a VB method

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

IF @hr 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD

FOR SELECT drive from TBLdrive

ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0

BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

IF @hr 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

IF @hr 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE TBLdrive

SET TotalSize=@TotalSize/@MB, ServerName = replace( @ServerName , '.master.dbo.xp_fixeddrives',''), FreespaceTimestamp = (GETDATE())

WHERE drive=@drive

FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur

DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso

IF @hr 0 EXEC sp_OAGetErrorInfo @fso

-- Email DBA with disk usage information

SELECT @EmailAddress=Email_Address FROM msdb..sysoperators WHERE Name='YOUR NAME'

SET @SubjectText = 'Disk space usage on Server ' + @@ServerName

EXEC master..xp_sendmail

@recipients = @emailaddress,

@subject = @SubjectText,

@query ='SELECT

ServerName,

drive,

TotalSize as [Total(MB)],

FreeSpace as [Free(MB)],

(TotalSize - FreeSpace) as [Used(MB)],

CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as [Free(%)],

(CONVERT(char(12), FreespaceTimestamp, 105)) AS [Date]

FROM ServerAdmin..TBLdrive ORDER BY drive',

@width = 133

-- where 105 is dd-mm-yyyy date format

-- Email DBA if disk space falls below the specified threshold

--SET @threshold = 20480-- For testing only. 20GB set as limit

SET @threshold = 1024-- Set the limit of disk usage threshold for all drives

SET @drive = (SELECT TOP 1 drive FROM ServerAdmin..TBLdrive --get first drive letter

WHERE freespace < @threshold

ORDER BY drive ASC)

SET @DSPACE = (SELECT freespace FROM ServerAdmin..TBLdrive --get the disk space for the letter

WHERE drive = @drive)

SET @MSG = @drive + ' is at ' + CONVERT(VARCHAR,@DSPACE) --put the vars into a msg

+ 'MB' + CHAR(13) + CHAR(10)

WHILE (SELECT COUNT(*) FROM ServerAdmin..TBLdrive WHERE freespace @drive) > 0

BEGIN--loop through drive letters and repeat above

SET @drive = (SELECT TOP 1 drive FROM ServerAdmin..TBLdrive

WHERE freespace @drive

ORDER BY drive ASC)

SET @DSPACE = (SELECT freespace FROM ServerAdmin..TBLdrive

WHERE drive = @drive)

SET @MSG = @MSG + @drive + ' is at ' + CONVERT(VARCHAR,@DSPACE) + 'MB'

+ CHAR(13) + CHAR(10) + CHAR (10) +'Please investigate immediately.'

SET @SubjectText1 = 'WARNING: Disk space in one or more drives is below 1 GB'

EXEC master..xp_sendmail

@recipients = @emailaddress,

@subject = @SubjectText1,

@message = @msg,

@width = 133

END

DROP TABLE ServerAdmin..TBLdrive

RETURN

GO

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 9

Thanks for the update.

I'm not using the e-mail code yet. I'm trying to build up a stats database that get DB sizes, disk size, last backup (log and transaction), recovery models and so on that will allow me to pull out the critical info and send it in one e-mail.

A lot of the time one problem can mask the real problem. I had one case where my logs (and disk) kept filling up. But what the real problem was that the backups were failing integrity checks in the nightly backups and terminating the maint plan. That was leaving many days worth of backups on the drive. I had to go break up the maint plan to get them all to work. (I have a database that is logged into all the time by a web app and wouldn't let them go single user for DBCC.)

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

THANG HOANG Say Hey Kid Points: 662 More actions · Answer 10

Can someone please explain the posted sp above (for the case of targeting a remote server), how could the the Total disk size coming from the remote server disks and not from the local server disks ???

I can understand the disk "free" spaces are from the remote server disks (Server.master.dbo.xp_fixeddrives), but the total disk space ? What tells the FSO to return the total disk space of a remote server disk ???

DBA-640728 SSChampion Points: 12898 More actions · Answer 11

hi run your script but i get this error message

Msg 7202, Level 11, State 2, Procedure cp_diskspace, Line 22

Could not find server 'WIN2K09' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

🙁

goaler SSC Enthusiast Points: 185 More actions · Answer 12

This script is excellent, but what about if you are using mount points? I have not been able to gather that information from the windows counters, anyone had luck on that point?:w00t:

goaler....

Goaler...:hehe:
goaler@comcast.net

madmecks SSC Journeyman Points: 91 More actions · Answer 13

/* script informs about the USED and FREE discspace on the partitions

the DB file are allocated. Only this partitions are scanned.

The result is a table (driveusage) with the informations, how many megabytes are used and free and the timestamp of the

information are taken.

I----------------- Partition

I I----------- Megabyte Used

I I I------- Megabyte Free

timestamp

D641178512009-05-28 16:15:04.190

E 39 18833 2009-05-28 16:15:04.190

F 1277 148 2009-05-28 16:15:04.190

NO xp_cmdshell or FSO-objects are used.

*/

use master -- < choose upon your decision

go

--/* one time definition

drop table [DriveUsage]

go

CREATE TABLE [DriveUsage] (

[drive] [varchar](10) NULL,

[mbUsed] [int] NULL,

[mbFree] [int] NULL,

ValuesFrom DATETIME NULL)

--table will be reloaded by every run, so truncate them first */

drop table #temp

drop table #temp2

drop table #dbspace

drop table #drivespace

CREATE TABLE [#dbspace](

[dbname] [varchar](100) NULL,

[dbsize] [int] NULL)

CREATE TABLE [#drivespace](

[drive] [varchar](100) NULL,

[mbfree] [int] NULL)

create table #temp

( dbn varchar(200), dbs varchar(200), t1 varchar(20))

insert into #temp

exec sp_databases

insert into #dbspace

select dbn, convert(int,dbs)/1024 from #temp

drop table #temp

create table #temp2

( drive varchar(20), mbfree varchar(200))

insert into #temp2

exec xp_fixeddrives

insert into #drivespace

select * from #temp2

drop table #temp2

truncate table DriveUsage

insert into DriveUsage

select distinct upper(substring(d.filename,1,1)),

sum(s.dbsize) as mbUsed,

max (c.mbfree) as mbFree,

GETDATE()

from #dbspace s, sys.sysdatabases d, #drivespace c

where s.dbname = d.name

and upper(substring(d.filename,1,1)) = upper(c.drive)

group by upper(substring(d.filename,1,1))

drop table #dbspace

drop table #drivespace

select * from DriveUsage

yamillahuatzin SSC Enthusiast Points: 157 More actions · Answer 14

All scripts works fine, I make my contribution with the server name instead the host name... best regards

CREATE PROCEDURE sp_diskspace

AS

SET NOCOUNT ON

DECLARE @hr int

DECLARE @fso int

DECLARE @drive char(1)

DECLARE @odrive int

DECLARE @TotalSize varchar(20)

DECLARE @MB bigint ; SET @MB = 1048576

CREATE TABLE #drives (ServerName varchar(15),

drive char(1) PRIMARY KEY,

FreeSpace int NULL,

TotalSize int NULL,

FreespaceTimestamp DATETIME NULL)

INSERT #drives(drive,FreeSpace)

EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

IF @hr 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD

FOR SELECT drive from #drives

ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0

BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

IF @hr 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

IF @hr 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives

SET TotalSize=@TotalSize/@MB, ServerName = CONVERT(char(20), SERVERPROPERTY('servername')), FreespaceTimestamp = (GETDATE())

WHERE drive=@drive

FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur

DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso

IF @hr 0 EXEC sp_OAGetErrorInfo @fso

SELECT ServerName,

drive,

TotalSize as 'Total(MB)',

FreeSpace as 'Free(MB)',

CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',

FreespaceTimestamp

FROM #drives

ORDER BY drive

DROP TABLE #drives

RETURN

GO