Getting all disks size

Question

Getting all disks size

francois keen

Valued Member

Points: 58
More actions
December 31, 2004 at 3:47 am

#87612

Hi,
I am writing a perl program which aim is to get all the datafiles & logs size. (It uses DBI and creates a monitoring DB with some tables, stocked procedures and a dedicated connexion)
Once known, the data files & logs sizes are stocked into a central DB.
Since I want to trace some graphs with the retrieved data, I have to know the disks size (not the free space) to be able to calculate the right scale.
Since I do not want to add any program on the SQL servers which will be monitored and since I do not want to enter the disks size manually, I wonder if there is a DOS command which can return me the disks capacity. Then, I'll be able to involve this command trough an EXEC master..xp_cmdshell with a drive letter parameter (returned thanks to a cursor which contains the drive letters returned by the xp_fixeddrives proc)
Many thanks for your help.
François.

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

IF you know how large all of your databases are (and what drives they are on) along with the logs + drive freespace would that not be close enough??

Good Hunting!

AJ Ahrens

webmaster@kritter.net

francois keen Valued Member Points: 58 More actions · Answer 2

Hi,

I have dirty old DB to deal with and there are some programs which writes files on the DB involved drives. As a consequence, knowing the DB datafiles & logs size and the free space on this drive prevents me from knowing its capacity...

I really need to know the drive size since it will be shown as the Y axis.

François (Still hunting!)

Chlijack SSC Rookie Points: 46 More actions · Answer 3

Chlijack

SSC Rookie

Points: 46

January 3, 2005 at 2:04 pm

#535668

try master..xp_fixeddrives

hoo-t SSChampion Points: 12713 More actions · Answer 4

The DOS DIR command will give you the drive capacity, but you'll have to parse the information out of the results. I've done this in a stored procedure, but you said that you want the information from DOS itself.

Steve

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 5

if you install windows resource kit there is a program call srvinfo and it will give you all kinds of info including drives info. The result will be in dos

mom

francois keen Valued Member Points: 58 More actions · Answer 6

francois keen

Valued Member

Points: 58

January 4, 2005 at 2:38 am

#535729

This gives the drive free space.

francois keen Valued Member Points: 58 More actions · Answer 7

Thanks but I do not want to add any program on the servers I want to monitor.

francois keen Valued Member Points: 58 More actions · Answer 8

Hello,

Parsing the results is not a problem since the DB is queried by perl DBI.

But the DIR command does not give me the drive capacity, it only gives the drive free space (same thing as xp_fixed_drives) :

C:\Documents and Settings\fkeen>dir c:

Le volume dans le lecteur C n'a pas de nom.

Le numéro de série du volume est 8C21-025F

Répertoire de c:\

23/12/2004 16:04 <DIR> cygwin

25/01/2000 08:53 88 DATA.TAG

10/12/2004 10:39 <DIR> Documents and Settings

31/12/2004 09:50 <DIR> Program Files

23/12/2004 11:58 <DIR> Stonesoft

20/12/2004 11:50 <DIR> WINNT

12/08/2003 13:13 6 421 _files.lst

2 fichier(s) 6 558 octets

5 Rép(s) 857 116 672 octets libres

I'll be really interrested in knowing how you manage to get the drive capacity in your stored procedure.

Thanks for your assistance.

François.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

I doubt you'll find a built-in solution for this. I am not aware of any sp that will give what you need. You can however utilize the FileSystemObject via the sp_OA* procedures. Here's an example I picked up some time ago on the net:

CREATE PROC sp_getTotalSpaceOnAllFixedDrives

AS

SET NOCOUNT ON

DECLARE @hr int

DECLARE @fso int

DECLARE @size float

DECLARE @mbtotal int

DECLARE @drive char(1)

DECLARE @fso_Method varchar(255)

SET @mbTotal = 0

EXEC @hr = master.dbo.sp_OACreate 'Scripting.FilesystemObject', @fso OUTPUT

-- Insert Error-handling for sp_OACreate here

CREATE TABLE #space (drive char(1), mbfree int)

INSERT INTO #space

EXEC master.dbo.xp_fixeddrives

DECLARE cDrives CURSOR FAST_FORWARD FOR SELECT drive FROM #space

OPEN cDrives

FETCH NEXT FROM cDrives INTO @drive

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fso_Method = 'Drives("' + @drive + ':").TotalSize'

EXEC @hr = sp_OAMethod @fso, @fso_method, @size OUTPUT

-- Insert Error-handling for sp_OAMethod here

SET @mbtotal = @mbtotal + @size / (1024 * 1024)

FETCH NEXT FROM cDrives INTO @drive

END

CLOSE cDrives

DEALLOCATE cDrives

EXEC @hr = sp_OADestroy @fso

SELECT @mbtotal

return 0

GO

exec sp_getTotalSpaceOnAllFixedDrives

DROP PROCEDURE sp_getTotalSpaceOnAllFixedDrives

It will return the total size in kb.

HTH

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

hoo-t SSChampion Points: 12713 More actions · Answer 10

You're absolutely right. I was looking at the wrong procedure. The procedure that I have that gets the total space is basically the same as the one Frank posted.

Sorry for the confusion,

Steve

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 11

Here could be a solution. Use WMI script inside vbs file. WMI is Windows Management Instrumentation and is designed for this kind of tasks. Create a new text document, copy my script there, name the document MyScript.vbs You have to put this script only on your monitoring server assuming everything is running on the domain account that has access to other servers. Replace strServer value from MyServerName to a dot for a local machine or for any server name you want to have a drive size for. Either use the script as is or you may call it from SQL Server. For that create a batch file MyBatch.bat with a single line calling this script like C:\MyScript.vbs. Add a new job witha job step of the type OS Command and enter MyBatch.bat there. Run a job. For some reason I was not able to call vbs file directly from SQL Server that is why I am calling it from the bat file. I also was not able to run it as ActiveX type of the step. But calling it from a bat file works fine. You may also want just schedule a vbs script in Scheduled Tasks without SQL Server and just dump results in the file like I did. Notice, that you will have all drives including mapped. You may also append WHERE to the SELECT in the script, it is like SQL.

Also note that I ran 2 queries for the example, for the DiskDrive Object and for the LogicalDisc object. Use whatever you like better.

The result will be in C:\z_MyLog1.txt in this example. It is very easy to use this script as a sub passing the names of all your servers in the loop. The names could be taken from a file or a table too.

One thing: not all parts of the WMI scripts may work from Windows 2000. It works FROM XP and 2003 Server and you MAY access 2000 servers and get info from them, but I had problems running some WMI scripts FROM Windows 2000 server.

Yelena

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Constants for opening files

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Const OpenFileForReading = 1

Const OpenFileForWriting = 2

Const OpenFileForAppending = 8

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Some handy global variables

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim TabStop

Dim NewLine

NewLine = vbCrLf 'Chr(13)&Chr(10)

' Creating FileSystemObject

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")

Set FileObject = fso.CreateTextFile("C:\z_MyLog1.txt", True)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

strComputer = "MyServerName"

Set objWMIService = GetObject("winmgmts:" _

& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colDriveList = objWMIService.ExecQuery _

("Select * from Win32_DiskDrive")

For Each objDrive in colDriveList

FileObject.WriteLine "Drive Letter = " & objDrive.Name & " Size = " & objDrive.Size

("Select * from Win32_LogicalDisk")

For Each objDrive in colDriveList

FileObject.WriteLine "Drive Letter = " & objDrive.Name & " Size = " & objDrive.Size

Set ColDriveList =Nothing

Regards,Yelena Varsha

francois keen Valued Member Points: 58 More actions · Answer 12

Thanks for your help!

Invoking such VB objects through master.dbo.sp_OACreate is great.

There are some other properties such as VolumeName SerialNumber etc.

So now I've got everything to go on with my project!

francois keen Valued Member Points: 58 More actions · Answer 13

Thanks, but I do not wanted to add any file on the DB servers I wanted to monitor.

I finally managed to get the disks capacity thanks to :

EXEC @hr = master.dbo.sp_OACreate 'Scripting.FilesystemObject', @fso OUTPUT

select @sql =

'use Master

insert REPORTS.dbo.tblDriveLogs (Drive,MBFree) exec xp_fixeddrives'

exec (@SQL)

DECLARE le_curseur CURSOR FOR

SELECT Drive

FROM tblDriveLogs

OPEN le_curseur

FETCH NEXT FROM le_curseur INTO @drive

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fso_Method = 'Drives("' + @drive + ':").TotalSize'

EXEC @hr = sp_OAMethod @fso, @fso_method, @taille_lecteur OUTPUT

set @size_mb=@taille_lecteur/(1024*1024)

etc.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 14

Glad I could help!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]