SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Looking for the inverse of xp_fixeddrives


Looking for the inverse of xp_fixeddrives

Author
Message
Jeff W
Jeff W
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1015 Visits: 51

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
RSP
RSP
Mr or Mrs. 500
Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)

Group: General Forum Members
Points: 536 Visits: 623

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
Steve Jones
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: Administrators
Points: 146665 Visits: 19432

You can also get this from VBScript using the FileSystemObject.



Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
cchitanu
cchitanu
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 261
Send me an email at cchitanu@csc.com



Imrancs
Imrancs
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 7

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
SpiceDBA
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 200

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
Jeff W
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1015 Visits: 51

The script is awesome!!!




"Keep Your Stick On the Ice" ..Red Green
Jim P.
Jim P.
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1853 Visits: 2215
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
andyobl
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 105
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 < @threshold AND drive > @drive) > 0
BEGIN --loop through drive letters and repeat above
SET @drive = (SELECT TOP 1 drive FROM ServerAdmin..TBLdrive
WHERE freespace < @threshold
AND drive > @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.
Jim P.
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1853 Visits: 2215
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search