Post reply

To find freespace of Database using a query

  • December 1, 2007 at 4:20 am

    #184570

    Hi,

    my requirement is to find the freespace of the Database using a query instead of a stored procedure.

    Is there any qurey available to fine the freespace of database?

    I can find a lot of stored procedures that are available but i want query only.

    It would be great if anybody help on this.

    Thanks in advance,

    MRK.

  • December 1, 2007 at 9:30 am

    #756238

    I can find a lot of stored procedures that are available

    "Steal" code from those...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 1, 2007 at 11:41 am

    #756240

    Take the code out of the stored procedure and you should be able to use one of those. There aren't many ways to do this, so I'd use that code.

    Is there a reason you can't use a stored procedure?

  • December 2, 2007 at 2:54 am

    #756272

    Hi,

    I can use the stored procedure. but Im trying to implement That stored procedure in DTS package. The Execution of the stored procedure is not returning a resultset... Im not sure what is wrong. so im searching for a query which finds the free space of the database.

    Is there any query available to find the Free space of the Database?

  • December 2, 2007 at 4:54 am

    #756275

    I did have some code for this but cannot put my finger on it at the moment.

    As others have suggested, you should steal code from the system stored procs - just get the relevant bits of code.

    If you need something that ends up resembling

    select * from X

    then you could make a user-defined function that returns a table. In the UDF you can put the stored proc code. Just remember to use table variables rather than temp tables (ie @myTable rather than #myTable) in your UDF.

  • December 2, 2007 at 6:25 am

    #756279

    Already had the SQL statements for 2005 where transaction log space is excluded.

    selectFilePageCnt, ReservedPageCnt, ( FilePageCnt - ReservedPageCnt)as FreePageCnt

    ,FileKb, ReservedKb, ( FileKb- ReservedKb)as FreeKb

    ,FileMb, ReservedMb, ( FileMb- ReservedMb)as FreeMb

    ,FileGb, ReservedGb, ( FileGb- ReservedGb)as FreeGb

    FROM(

    selectFilePageCnt

    ,ReservedPageCnt

    ,(FilePageCnt * BytesPerPage / BytesPerKb ) as FileKb

    ,(ReservedPageCnt * BytesPerPage / BytesPerKb ) as ReservedKb

    ,(FilePageCnt * BytesPerPage / BytesPerMb ) as FileMb

    ,(ReservedPageCnt * BytesPerPage / BytesPerMb ) as ReservedMb

    ,(FilePageCnt * BytesPerPage / BytesPerGb ) as FileGb

    ,(ReservedPageCnt * BytesPerPage / BytesPerGb ) as ReservedGb

    from(select cast( 8192 as numeric(38,2) ) as BytesPerPage

    ,cast( 1024 as numeric(38,2) ) as BytesPerKb

    ,cast( ( 1024 * 1024) as numeric(38,2) ) as BytesPerMb

    ,cast( ( 1024 * 1024 * 1024 ) as numeric(38,2) ) as BytesPerGb

    ) as Environment

    ,(

    selectsum( cast ( sys.database_files.size as bigint ) ) as FilePageCnt

    fromsys.database_files

    wheresys.database_files.type_desc <> 'LOG'

    ) as DatabaseSize

    ,(

    selectsum( cast ( total_pages as bigint ) ) as ReservedPageCnt

    fromsys.allocation_units

    ) as ReservedSize

    ) as SpaceUsage

    SQL = Scarcely Qualifies as a Language

  • December 2, 2007 at 8:01 am

    #756282

    That's even better than the one I couldn't find - thanks for the nugget! 🙂

  • December 3, 2007 at 3:02 am

    #756391

    Hi,

    This query is not working. because i have to use SYSFILES instead of sys.databasefiles it seems.

    Pls give me another MS SQL query or a Stored Procedure to find the freespace of Database as soon as possible.

    Thanks in advance,

    MRK.

  • December 3, 2007 at 4:59 am

    #756407

    Hi,

    I found this code on website some time ago. I tried to find it again but am unable to do so (apologies to the original author as I am now unable to credit him/her).

    SELECT name AS NameOfFile,

    size/128.0 as TotalSizeInMB,

    CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS SpacesUsedInMB,

    size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB

    FROM dbo.SYSFILES

    go

    There are a number of scripts/sp's on this site that may work as well.

    N

  • December 3, 2007 at 9:39 am

    #756542

    Hi,

    Thank you very much. Its working fine.

    Do you have any SQL for finding the freespace of Disk drive?

    Thanks in Advance,

    MRK

  • December 3, 2007 at 7:49 pm

    #756763

    xp_fixeddrives

  • December 4, 2007 at 12:37 am

    #756799

    HI,

    How to use the XP_diskdrives effectively?

    my requirement is, i need to find the freespace of a diskdrive.

    Im looking for a query to achieve this functionality.

    Can any one help me?

    Thanks in Advance,

    MRK.

  • December 4, 2007 at 12:39 am

    #756800

    HI,

    How to use the XP_fixeddrives effectively?

    my requirement is, i need to find the freespace of a diskdrive.

    Im looking for a query to achieve this functionality.

    Can any one help me?

    Thanks in Advance,

    MRK.

  • December 4, 2007 at 4:31 am

    #756845

    exec this stored procedure XP_fixeddrives and store the output into a table or a temp table. Then query your table.

    CREATE TABLE x (drive char(1) PRIMARY KEY, FreeSpace int NULL)

    INSERT x EXEC master.dbo.xp_fixeddrives

    select * from x

  • December 4, 2007 at 6:08 am

    #756893

    And Vivian's good script lists the FreeSpace in Mega Bytes.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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