December 4, 2007 at 8:35 am
Hi,
Thanks for youre response. I have already looked into that script. But im looking for a single query which returns the freespace of a disk drive.
I need a single MS SQL query to find the freespace of Disk drive.
Can anybody help me on this?
Thanks in advance,
MRK.
December 4, 2007 at 5:14 pm
Gosh... what's wrong with just...
EXEC master.dbo.xp_fixeddrives
--Jeff Moden
December 4, 2007 at 10:18 pm
HI,
I know that the above procedure is correct. But we are facing some techincal problem in this while implementing it into the DTS Package.
CREATE procedure DiskSpace_Size
AS
SET NOCOUNT ON
DECLARE @disksize Decimal(15,2)
SET NOCOUNT ON
BEGIN
CREATE TABLE #T1(DRVLETTER CHAR(1),DRVSPACE INT)
INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives;
select @DiskSize =(SELECT DRVSPACE FROM #T1 WHERE DRVLETTER='D');
Drop TABLE #T1
return @DiskSize
end
Execution:
------------
DECLARE @ReturnValue int
EXEC @ReturnValue = DiskSpace_Size
SELECT ReturnValue=@ReturnValue
While executing like this im getting the freespace. But when using DTS Package.
The Parameter "ReturnValue" is not getting the value.
So It would be better if we find the freespace of DiskDrive in a single query.
So that it will work fine.
MRK.
December 4, 2007 at 10:34 pm
You can use th script named database detais available in the site http://www.sql-articles.com
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
December 4, 2007 at 10:49 pm
Hi I have a script which i downloaded way back. Sorry i forgot author's name. But i pass on my credits to him if he finds this script.
i modified the script little bit to suit the results i want.
this script displays drive, total size and available size.
DECLARE @Drive TINYINT,
@SQL VARCHAR(100)
SET @Drive = 97
-- Setup Staging Area
DECLARE @Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET @SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT @Drives
(
Info
)
EXEC (@SQL)
UPDATE @Drives
SET Drive = CHAR(@Drive)
WHERE Drive IS NULL
SET @Drive = @Drive + 1
END
-- Show the expected output
SELECT Drive,
((SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END)/1024)/1024) AS TotalMB,
((SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END)/1024)/1024) AS AvailFreeMB
FROM (
SELECT Drive,
Info
FROM @Drives
WHERE Info LIKE 'Total # of %'
) AS d
GROUP BY Drive
ORDER BY Drive
Hope this solves your purpose.
December 5, 2007 at 2:13 am
Hi,
When i Try to execute
DECLARE @Drive TINYINT,
@SQL VARCHAR(100)
SET @Drive = 97
-- Setup Staging Area
DECLARE @Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET @SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT @Drives
(
Info
)
EXEC (@SQL)
UPDATE @Drives
SET Drive = CHAR(@Drive)
WHERE Drive IS NULL
SET @Drive = @Drive + 1
END
Im getting an error:
EXECUTE cannot be used as a source when inserting into a table variable.
pls help to resolve this issue.
December 5, 2007 at 2:29 am
wrap all the statments into the string and after that execute it with exec()
December 5, 2007 at 2:41 am
You can't insert rows into table variables in an INSERT..EXEC statement.
Create a temporary table to hold the data instead of table variable.
--Ramesh
December 5, 2007 at 3:37 am
Hi,
Thank you very much.
I have my procedure DiskSpace_Size in DTS Package.
The problem is with my output parameter.
I have resolved it.
Thanks for all your support and information.
MRK.
December 5, 2007 at 5:58 am
Two way street here... HOW did you resolve it?
--Jeff Moden
December 5, 2007 at 7:54 am
Hi,
I have implemented that Procedure using DTS Package.
I returned the freespace from the Procedure.
In DTS Package i got the returned value as Global Parameter.
But im facing a problem here that Global variable is not getting updated.
Can anybody help me on this?
MRK.
December 7, 2007 at 9:04 am
----The following will return each local drive and a free space in MBEXEC master.dbo.xp_fixeddrives
----The following function with obtain a supplied drive's capacity
ALTER FUNCTION [dbo].[fnDBDriveStats_GetDriveSize]
(@cDrive char(1))
RETURNS int
AS
--Makes OLE Automation call to obtain supplied drive's total size
BEGIN
DECLARE @x int
DECLARE @iFSO int
DECLARE @iDrive int
DECLARE @vTotalSize varchar(20)
DECLARE @biMB bigint
DECLARE @iSizeMB int
SET @biMB = 1048576
EXEC @x = sp_OACreate 'Scripting.FileSystemObject', @iFSO OUT
IF @x <> 0
RETURN -1
EXEC @x = sp_OAMethod @iFSO, 'GetDrive', @iDrive OUT, @cDrive
IF @x <> 0
RETURN -1
EXEC @x = sp_OAGetProperty @iDrive, 'TotalSize', @vTotalSize OUT
IF @x <> 0
RETURN -1
SET @iSizeMB = @vTotalSize/@biMB
EXEC @x = sp_OADestroy @iFSO
RETURN @iSizeMB
END
March 22, 2011 at 8:05 pm
this query is excellent, to find the space in database files size.
November 26, 2011 at 10:30 am
good query..
November 27, 2011 at 2:41 pm
bfranklin-557019 (12/7/2007)
----The following will return each local drive and a free space in MBEXEC master.dbo.xp_fixeddrives----The following function with obtain a supplied drive's capacity
ALTER FUNCTION [dbo].[fnDBDriveStats_GetDriveSize]
(@cDrive char(1))
RETURNS int
AS
--Makes OLE Automation call to obtain supplied drive's total size
BEGIN
DECLARE @x int
DECLARE @iFSO int
DECLARE @iDrive int
DECLARE @vTotalSize varchar(20)
DECLARE @biMB bigint
DECLARE @iSizeMB int
SET @biMB = 1048576
EXEC @x = sp_OACreate 'Scripting.FileSystemObject', @iFSO OUT
IF @x <> 0
RETURN -1
EXEC @x = sp_OAMethod @iFSO, 'GetDrive', @iDrive OUT, @cDrive
IF @x <> 0
RETURN -1
EXEC @x = sp_OAGetProperty @iDrive, 'TotalSize', @vTotalSize OUT
IF @x <> 0
RETURN -1
SET @iSizeMB = @vTotalSize/@biMB
EXEC @x = sp_OADestroy @iFSO
RETURN @iSizeMB
END
Just a quick question on that script... why don't you destroy the @iFSO object when an error occurs just before the second and third "RETURN -1"?
--Jeff Moden
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply