Disk Space - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Disk Space

Author

Topic

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2007-11-13 : 06:16:42

Hi guys,

I have been asked to find the total disk size (not free space) from the drives on my SQL boxes.

Company standards (set by me, DOH) says I cant use cursors or com objects, any ideas?

Dave

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 06:52:36

exec xp_fixeddrives

E 12°55'05.25"
N 56°04'39.16"

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2007-11-13 : 06:54:54

Hi Peso, Thanks but that only shows the free space.

I need to see the total disk size?

Haywood
Posting Yak Master

221 Posts

Posted - 2007-11-13 : 10:46:21

[CODE]
/******************************************************************************
** © Raylev Database Support & Consulting, 2005.
** This script is provided as is, and it's effects are not liable
** for any impacts/damage to your systems.
**
** http://members.cox.net/raylev.systems/
*******************************************************************************
**
** Name: usp_GetDrive_Info.sql
**
** Description: Returns a drive list with storage information.
**
**
** Return values:
**
** Author: G. Rayburn
**
** Date: 11/01/2005
**
** ToDo:
**
**
*******************************************************************************
** Modification History
*******************************************************************************
**
** Initial Creation: 11/01/2005 G. Rayburn
**
*******************************************************************************
**
******************************************************************************/

SET NOCOUNT ON

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')
DROP TABLE ##_DriveSpace

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')
DROP TABLE ##_DriveInfo

DECLARE @Result INT
, @objFSO INT
, @Drv INT
, @cDrive VARCHAR(13)
, @Size VARCHAR(50)
, @Free VARCHAR(50)
, @Label varchar(10)

CREATE TABLE ##_DriveSpace
(
DriveLetter CHAR(1) not null
, FreeSpace VARCHAR(10) not null

)

CREATE TABLE ##_DriveInfo
(
DriveLetter CHAR(1)
, TotalSpace int
, FreeSpace int
, Label varchar(10)
)

INSERT INTO ##_DriveSpace
EXEC master.dbo.xp_fixeddrives

-- Iterate through drive letters.
DECLARE curDriveLetters CURSOR
FOR SELECT driveletter FROM ##_DriveSpace

DECLARE @DriveLetter char(1)
OPEN curDriveLetters

FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

SET @cDrive = 'GetDrive("' + @DriveLetter + '")'

EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT

IF @Result = 0

EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT

IF @Result <> 0

EXEC sp_OADestroy @Drv
EXEC sp_OADestroy @objFSO

SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )

SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )

INSERT INTO ##_DriveInfo
VALUES (@DriveLetter, @Size, @Free, @Label)

END
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END

CLOSE curDriveLetters
DEALLOCATE curDriveLetters

PRINT 'Drive information for server ' + @@SERVERNAME + '.'
PRINT ''

-- Produce report.
SELECT DriveLetter
, Label
, FreeSpace AS [FreeSpace MB]
, (TotalSpace - FreeSpace) AS [UsedSpace MB]
, TotalSpace AS [TotalSpace MB]
, ((CONVERT(NUMERIC(6,0),FreeSpace) / CONVERT(NUMERIC(6,0),TotalSpace)) * 100) AS [Percentage Free]

FROM ##_DriveInfo
ORDER BY [DriveLetter] ASC
GO

DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo
[/CODE]

For 2005 this requires that DMO be 'turned on'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 10:49:09

And that's how you post an excellent suggestion, using both CURSORS and COM objects which for some reason where forbidden.
Which makes me think this is a homework question.

E 12°55'05.25"
N 56°04'39.16"

Haywood
Posting Yak Master

221 Posts

Posted - 2007-11-13 : 10:51:49

You don't like the cursor or COM object usage?

Personally, for administration I use them/it all over the place. I find a cursor much easer to write and manage than having to cruft up my own loop constructs...

Edit:

I skipped over the requirements...doh!

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2007-11-13 : 10:56:17

No its not a homework question. I dont use them at all, I find them to take far to much memory.

Its down to the bussines needs of each company, and we dont all do things the same way.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 11:32:42

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92571

E 12°55'05.25"
N 56°04'39.16"

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2007-11-13 : 11:35:11

HA HA, Thats the one great!!!!!

Thanks Peso!

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2007-11-13 : 11:40:45

hmm get this error:-

'fsutil' is not recognized as an internal or external command,

Am I being dumb, do I need an extra app?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 11:44:22

fsutil has been available since windows 98...
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/fsutil_volume.mspx?mfr=true

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 11:51:54

Try to put full path to fsutil.exe like this

SET @SQL = 'XP_CMDSHELL ''C:\WINDOWS\SYSTEM32\fsutil.exe volume diskfree ' + CHAR(@Drive) + ':'''

E 12°55'05.25"
N 56°04'39.16"

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2007-11-13 : 11:52:48

o yes that was on an old NT box, works fine on others, this might be what I need.

Thanks again!

Haywood
Posting Yak Master

221 Posts

Posted - 2007-11-13 : 12:31:44

I've seen someone drop to a cmdshell and use CHKDSK.exe to get drive geometry before...it's ugly and I really probably shouldn't post it (if I can even find it).

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-13 : 15:48:52

Use the PSINFO utility available free from sysinternals:
http://www.microsoft.com/technet/sysinternals/default.mspx


psinfo \\MYSERVER -d

PsInfo v1.73 - Local and remote system information viewer
Copyright (C) 2001-2005 Mark Russinovich
Sysinternals - www.sysinternals.com

System information for \\MYSERVER:
Uptime:                    112 days 4 hours 4 minutes 22 seconds
Kernel version:            Microsoft Windows Server 2003, Multiprocessor Free
Product type:              Standard Edition
Product version:           5.2
Service pack:              1
Kernel build number:       3790
Registered organization:   My Company
Registered owner:          My Company
Install date:              9/24/2006, 9:22:22 AM
Activation status:         Error reading status
IE version:                6.0000
System root:               C:\WINDOWS
Processors:                4
Processor speed:           3.4 GHz
Processor type:            Intel(R) Xeon(TM) CPU
Physical memory:           3584 MB
Video driver:              RAGE XL PCI Family (Microsoft Corporation)
Volume Type       Format     Label                      Size       Free   Free
    A: Removable                                                          0.0%
    C: Fixed      NTFS       MYSERVER_C             33.91 GB   22.95 GB  67.7%
    D: Fixed      NTFS       MYSERVER_D            102.11 GB   29.74 GB  29.1%
    E: Fixed      NTFS       MYSERVER_E            102.11 GB   38.02 GB  37.2%
    F: Fixed      NTFS       MYSERVER_F            102.11 GB   25.54 GB  25.0%
    G: Fixed      NTFS       MYSERVER_G            102.11 GB   27.59 GB  27.0%
    H: Fixed      NTFS       MYSERVER_H             34.04 GB   29.85 GB  87.7%
    N: CD-ROM                                                             0.0%
    R: Fixed      NTFS       MYSERVER_R            399.96 GB  102.65 GB  25.7%

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 04:12:00

Can't get PSINFO to work through XP_XMDSHELL. The first time you run the utility, you get to ACCEPT the license. I think the same thing happens when run under XP_CMDSHELL. Maybe that's because I run SQL SERVICE under another account?

I made a Scheduled task to run twice every day and output the result to a text file.

psinfo \\machine -d >c:\drvinfo.txt'

And then I import the textfile in my SP instead.

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-14 : 09:38:42

quote:
Originally posted by Peso

Can't get PSINFO to work through XP_XMDSHELL. The first time you run the utility, you get to ACCEPT the license. I think the same thing happens when run under XP_CMDSHELL. Maybe that's because I run SQL SERVICE under another account?

I made a Scheduled task to run twice every day and output the result to a text file.

psinfo \\machine -d >c:\drvinfo.txt'

And then I import the textfile in my SP instead.

E 12°55'05.25"
N 56°04'39.16"

I have never seen that problem, but I think the version I am using (v1.73) is not the latest. I guess they added the “accept the license” feature after the software was acquired by Microsoft.

Maybe you can get it to work by logging on with the service account and accepting the license one time.

I usually run PSINFO on an as needed basis from a command window, instead of from within xp_cmdshell.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources