Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Looking for the inverse of xp_fixeddrives Expand / Collapse
Author
Message
Posted Tuesday, April 27, 2004 9:04 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 8, 2013 3:39 PM
Points: 349, Visits: 49

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
Post #113227
Posted Tuesday, April 27, 2004 9:59 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 11:13 AM
Points: 172, Visits: 539

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

 

 

 




Post #113243
Posted Tuesday, April 27, 2004 11:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:30 PM
Points: 33,055, Visits: 15,167

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
Post #113255
Posted Wednesday, April 28, 2004 5:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 11:25 AM
Points: 136, Visits: 140
Send me an email at cchitanu@csc.com


Post #113399
Posted Wednesday, April 28, 2004 5:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2008 12:35 AM
Points: 43, 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




Post #113403
Posted Wednesday, April 28, 2004 9:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 6, 2013 11:04 AM
Points: 9, 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

 




Post #113475
Posted Thursday, April 29, 2004 8:37 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 8, 2013 3:39 PM
Points: 349, Visits: 49

The script is awesome!!!




"Keep Your Stick On the Ice" ..Red Green
Post #113685
Posted Tuesday, June 22, 2004 3:34 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:35 PM
Points: 615, Visits: 2,047
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.
Post #122447
Posted Wednesday, June 23, 2004 9:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 8:52 AM
Points: 21, Visits: 98
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
Post #122582
Posted Wednesday, June 23, 2004 10:36 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:35 PM
Points: 615, Visits: 2,047
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.
Post #122603
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse