July 24, 2008 at 12:40 pm
How can we find out a disk space usage from a query analyser window?
July 24, 2008 at 2:00 pm
Of what? Databases, Tables or Indexes?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 24, 2008 at 3:09 pm
if you are looking for hard disk capacity etc.
its xp_fixeddrives
cheers,
Deepak
July 24, 2008 at 3:11 pm
Got this from this site way back, need to enable ole automation through surface area config tool so it will work on 2005:
set nocount on
/*****************************************
* Create temp tables for disk space info
*****************************************/
CREATE TABLE #space (dletter varchar(2), fspace int, tspace BIGINT)
/*****************************************
* populate temp tables
*****************************************/
INSERT INTO #space (dletter, fspace) EXEC master.dbo.xp_fixeddrives
/*****************************************
* Update temp table info with total disk sizes
*****************************************/
-- Create cursor for disk space table
DECLARE c_disks CURSOR FOR
SELECT dletter, fspace, tspace FROM #space
FOR UPDATE
DECLARE @dletter VARCHAR(2), @fspace INT, @tspace BIGINT
-- Create FileSystemObject
DECLARE @oFSO INT, @oDrive INT, @drsize VARCHAR(255), @ret INT
EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT
-- Open cursor and fetch first row
OPEN c_disks
FETCH NEXT FROM c_disks
INTO @dletter, @fspace, @tspace
-- Loop through all records in the cursor
WHILE @@fetch_status = 0
BEGIN
-- Get disk size
SET @dletter = @dletter + ':\'
EXEC @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
EXEC @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
-- Update table
UPDATE #space
SET tspace = CAST(@drsize AS BIGINT)
WHERE CURRENT OF c_disks
-- Destory oDrive
EXEC master.dbo.sp_OADestroy @oDrive
-- Fetch next row
FETCH NEXT FROM c_disks
INTO @dletter, @fspace, @tspace
END
-- Close cursor
CLOSE c_disks
DEALLOCATE c_disks
-- Destroy FSO
EXEC master.dbo.sp_OADestroy @oFSO
/*****************************************
* Return disk space info
*****************************************/
select @@servername as ServerName, dletter as DriveLetter,
(convert(float, tspace) /1024/1024) / convert(float, 1024) as Disk_Total_GIG,
convert(float, fspace) / convert(float, 1024) as Disk_FreeSpace_GIG,
CAST(ROUND((CAST(tspace AS FLOAT) / 1024) /1024,0) AS INT) Disk_Total_MB,
fspace as Disk_FreeSpace_MB,
convert(float, fspace) / convert(float, CAST(ROUND((CAST(tspace AS FLOAT) / 1024) /1024,0) AS INT))
* 100 as Percentage_FreeSpace,
getdate() as Date_Sampled
from #space
DROP TABLE #space
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply