Disk Size

I want to find the size of the physical disks on a SQL
server. I have tried using xp_cmdshell but i cannot find a
command that will return the disk size. The closest that i
have come is forating the results of chkdsk but this takes
time to run. Can anyone offer any other suggestions. Ist
there a command to show this or perhaps there is a XP or
SP that will do the job
Cheersmat
CREATE table DriveTable (Drive varchar(10),[MB Free] int)
INSERT into Drivetable Exec xp_fixeddrives
SELECT * FROM DriveTable
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx
.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers|||Hi,
Execute the command "XP_FIXEDDRIVES" from query analyzer to the Free size in
MB for each of the fixed drives.
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx
.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers|||Hi ,
use following
exec master..xp_fixeddrives
it will return free space on the drives
Thanks
Ajay
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx
.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers|||I need to determine the actual size of the disks, not the
free space available
>--Original Message--
>mat
>CREATE table DriveTable (Drive varchar(10),[MB Free] int)
>INSERT into Drivetable Exec xp_fixeddrives
>SELECT * FROM DriveTable
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
> news:22f4901c45e7c$417e04f0$a601280a@.phx
.gbl...
find a[vbcol=seagreen]
that i[vbcol=seagreen]
takes[vbcol=seagreen]
>
>.
>|||Jasper Smith (SQL Server MVP) has written very useful stored procedure
use master
go
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 (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int 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
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 drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
go
<anonymous@.discussions.microsoft.com> wrote in message
news:237d901c45e7d$924f25c0$a501280a@.phx
.gbl...[vbcol=seagreen]
> I need to determine the actual size of the disks, not the
> free space available
> message
> find a
> that i
> takes|||The code can be found here http://www.sqldbatips.com/showcode.asp?ID=4
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:237d901c45e7d$924f25c0$a501280a@.phx
.gbl...[vbcol=seagreen]
> I need to determine the actual size of the disks, not the
> free space available
> message
> find a
> that i
> takessql