Post reply

Database Free Space

  • August 28, 2008 at 3:06 am

    #93801

    Hi All,

    How to find the amount of free space in a database.

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • August 28, 2008 at 3:16 am

    #864198

    Hi,

    In the Enterprise Manager, usind Taskpad view, the fress space can be verified.

    [font="Verdana"]Renuka__[/font]

  • August 28, 2008 at 3:24 am

    #864201

    Thanks I do know that....but how can I get it thru qry analyzer i.e PL/sql

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • August 28, 2008 at 3:37 am

    #864216

    I am not sure if there is direct query or a command, but if you use DBCC SHOWFILESTATS it returns Total extents and the Used extents of the Database; if required, using this the Free space can be calculated.

    [font="Verdana"]Renuka__[/font]

  • August 28, 2008 at 3:41 am

    #864218

    sp_spaceused

  • August 28, 2008 at 4:24 am

    #864237

    Rajan John (8/28/2008)

    sp_spaceused

    Thanks...I used it to group all the database in a single table.

    - I created a temp table to store the result set exec sp_msforeachtable 'sp_spaceused ''?'',true'

    - Then aggregated the results...but these results do not match with this sp_spaceused @updateusage='true'

    guys, any idea ???

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • August 28, 2008 at 5:30 am

    #864261

    Renuka (8/28/2008)

    I am not sure if there is direct query or a command, but if you use DBCC SHOWFILESTATS it returns Total extents and the Used extents of the Database; if required, using this the Free space can be calculated.

    thanks....yes there isn't any direct function to get it but as per ur suggestion this may be the solution...

    - Unused Extent = X

    - Page/Extent = 8

    - Page Size = 8 KB

    - Extent Size = 64KB

    - Unused Extents - 64 * X KB

    am i right

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • August 28, 2008 at 1:16 pm

    #864652

    Ahmad,

    Try this:

    CREATE TABLE #db_file_information(

    fileid integer

    , theFileGroup integer

    , Total_Extents integer

    , Used_Extents integer

    , db varchar(30)

    , file_Path_name varchar(300) )

    -- Get the size of the datafiles

    insert into #db_file_information

    ( fileid

    , theFileGroup

    , Total_Extents

    , Used_Extents

    , db

    , file_Path_name )

    exec('DBCC showfilestats')

    -- add two columns to the temp table

    alter table #db_file_information add PercentFree as

    ((Total_Extents-Used_Extents)*100/(Total_extents))

    select * from #db_file_information

    drop table #db_file_information

    MJ

  • August 28, 2008 at 10:10 pm

    #864822

    MANU (8/28/2008)

    Ahmad,

    Try this:

    CREATE TABLE #db_file_information(

    fileid integer

    , theFileGroup integer

    , Total_Extents integer

    , Used_Extents integer

    , db varchar(30)

    , file_Path_name varchar(300) )

    -- Get the size of the datafiles

    insert into #db_file_information

    ( fileid

    , theFileGroup

    , Total_Extents

    , Used_Extents

    , db

    , file_Path_name )

    exec('DBCC showfilestats')

    -- add two columns to the temp table

    alter table #db_file_information add PercentFree as

    ((Total_Extents-Used_Extents)*100/(Total_extents))

    select * from #db_file_information

    drop table #db_file_information

    MJ

    Thanks MJ ...but the results are different from that of Taskpad in Enterprise Mgr

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • August 29, 2008 at 10:09 am

    #865136

    Hi,

    In Taskpad view the size is shown in MBs. Ideally it should match with the results of DBCC SHOWFILESTATS.

    @used_space=(CONVERT(dec(10,2),usedextents*64/1024))

    @total_space=(CONVERT(dec(10,2),totalextents*64/1024))

    The results are in MBs and should match with Taskpad view results.

    [font="Verdana"]Renuka__[/font]

  • August 29, 2008 at 10:25 am

    #865143

    Try this:

    Create TABLE #db_file_information(

    fileid integer

    , theFileGroup integer

    , Total_Extents integer

    , Used_Extents integer

    , db varchar(30)

    , file_Path_name varchar(300))

    -- Get the size of the datafiles

    insert into #db_file_information

    ( fileid

    , theFileGroup

    , Total_Extents

    , Used_Extents

    , db

    , file_Path_name )

    exec('DBCC showfilestats')

    -- add two columns to the temp table

    alter table #db_file_information add PercentFree as

    ((Total_Extents-Used_Extents)*100/(Total_extents))

    alter table #db_file_information add TotalSpace_MB as

    ((Total_Extents*64)/1024)

    alter table #db_file_information add UsedSpace_MB as

    ((Used_Extents*64)/1024)

    alter table #db_file_information add FreeSpace_MB as

    ((Total_Extents*64)/1024-(Used_Extents*64)/1024)

    select * from #db_file_information

    drop table #db_file_information

    HTH

    Manu

  • August 29, 2008 at 11:27 am

    #865171

    Renuka (8/29/2008)

    Hi,

    In Taskpad view the size is shown in MBs. Ideally it should match with the results of DBCC SHOWFILESTATS.

    @used_space=(CONVERT(dec(10,2),usedextents*64/1024))

    @total_space=(CONVERT(dec(10,2),totalextents*64/1024))

    The results are in MBs and should match with Taskpad view results.

    Thats what I said in one of previous post...

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • August 31, 2008 at 12:14 pm

    #865510

    how can i get all databases aggregated in a single table ,....as sp_spaceused and dbcc showfilestats are database specific....

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • August 31, 2008 at 1:19 pm

    #865513

    Something like this should work. It's rough, but should give you the idea.

    CREATE TABLE #AllDbSpaceInfo (

    ...

    )

    exec sp_MSForEachDB ('Use ?; Insert into #AllDbSpaceInfo EXEC ....)

    SELECT * from #AllDbSpaceInfo

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 31, 2008 at 5:04 pm

    #865526

    Try this:

    -- Declare local variables

    CREATE TABLE #DB_Space (

    DBname varchar(25),Name varchar(100), FileName varchar(100),

    Size_in_MB varchar(20),

    Space_Used varchar(20),Available_Space varchar(20),RunDate Datetime

    )

    EXEC master..sp_MSForeachdb '

    USE [?]IF DB_ID(''?'')>4

    BEGIN

    INSERT INTO #DB_Space

    SELECT db_name(dbid),sf.name AS [File], sf.filename as File_Name

    , CAST(sf.size/128.0 as DECIMAL(10,2)) AS Size_in_MB

    , CAST(FILEPROPERTY(sf.name, ''SpaceUsed'')/128.0 as DECIMAL(10,2)) as Space_Used

    , CAST(sf.size/128.0-(FILEPROPERTY(sf.name, ''SpaceUsed'')/128.0) AS DECIMAL(10,2)) AS Available_Space

    , getdate() as RunDate

    FROM SYSFILES sf INNER JOIN master..sysaltfiles sa

    on sf.name=sa.name

    and sf.fileid=sa.fileid

    -- Where (maxsize = 0) or (maxsize <> 0 and (CAST(size/128.0-(FILEPROPERTY(name, ''SpaceUsed'')/128.0) AS DECIMAL(10,2))) = 0)

    --BEGIN

    --PRINT ''?''

    --END

    END

    '

    Select * from #DB_Space

    GO

    MJ

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply