Getting all disks size
-
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.
-
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 -
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!)
-
try master..xp_fixeddrives
-
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
-
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
-
This gives the drive free space.
-
Thanks but I do not want to add any program on the servers I want to monitor.
- 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. -
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] -
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
-
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
Next
Set colDriveList = objWMIService.ExecQuery _
("Select * from Win32_LogicalDisk")
For Each objDrive in colDriveList
FileObject.WriteLine "Drive Letter = " & objDrive.Name & " Size = " & objDrive.Size
Next
Set FSO = Nothing
Set ColDriveList =Nothing
Regards,Yelena Varsha
-
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!
-
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.
-
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]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply