Finding out disk space in from query analyser

  • How can we find out a disk space usage from a query analyser window?

  • 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]

  • if you are looking for hard disk capacity etc.

    its xp_fixeddrives

    cheers,

    Deepak

  • 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