May 21, 2008 at 10:09 pm
I know this question is already answered here (somewhere :P) but can't find that subject (maybe too lazy to search or im just too hungry to find it :w00t:)
xp_fixeddrives gives us the free hard disk space right?
how about the total hard disk space? any commands?
Thanks very much
"-=Still Learning=-"
Lester Policarpio
May 22, 2008 at 5:33 am
This is not mine, but here ya go anyway.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE sp_diskspace
/*** Borrowed from SQL Stripes Disk Size Stored Procedure ***/
AS
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; 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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Got no time for the jibba jabba!"
-B.A. Baracus
May 22, 2008 at 7:47 am
Here's a more thourough example of using the FileSystemObject (IMO)...
/******************************************************************************
** © 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 freespace < @Percent
**
**
**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(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]
FROM ##_DriveInfo
ORDER BY [DriveLetter] ASC
GO
DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo
Your friendly High-Tech Janitor... 🙂
May 22, 2008 at 10:35 pm
Thanks guys for your replies and suggestions the script works perfectly but now my problem is that the servers (34 servers) are with different sql versions of sp's (some with no sp and some are sp4). The script only works for servers with sp4 on it (all are sql server version 7). Is there a way to activate the sp_OA etc. procedure wherein i do not need to upgrade the sp of the other servers? if no is the answer well im in for an sp upgrade just like to know your opinions thanks in advance :):P
"-=Still Learning=-"
Lester Policarpio
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply