SQL Server Performance

how to find space usage in filegroup

Discussion in 'T-SQL Performance Tuning for Developers' started by geeth_rajesh, Sep 21, 2004.

  1. geeth_rajesh New Member

    Hi Everybody,


    I have 2 databases. one is SRC_DB and the other is ARchive_DB.
    when my archiving script is trying to archive the tables from source to archive database
    few tables were not archived.WHen I tried to find out the reason.The filegroup of those tables did not have enough space in the Archive_DB.

    so now my question is

    1. when i am transfering my table from SRC_db to Archive_DB , how do i check if i have enough memoryin the filegroup of the archive(target) database..

    2. IS there any procedure or query which tells me the USED SPACE,FREE SPACE in a particular filegroup so that i can check before archiving.

    Thanx
    Rajesh.
  2. satya Moderator

    Run SP_HELPFILE to get information the database files and their sizes.

    1) Compare the SRC_db & ARchive_db sizes in order to assure the table transfer will complete.

    2)From Query analyzer use sp_spaceused.
    From EM, view, taskpad you will find space used and free for each filegroup

    quote: Luis Martin


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. FrankKalis Moderator

    quote:Originally posted by geeth_rajesh
    1. when i am transfering my table from SRC_db to Archive_DB , how do i check if i have enough memoryin the filegroup of the archive(target) database..
    Why is this a problem? Do you have only limited space?
    If not, the file will grow when needed

    quote:
    2. IS there any procedure or query which tells me the USED SPACE,FREE SPACE in a particular filegroup so that i can check before archiving.
    What about sp_spaceused?

    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
  4. sundeip New Member

    Try Using

    Sp_HelpDB 'DBNAME'
    &
    Sp_SpaceUsed

    Thanks,
    Sandip
  5. Luis Martin Moderator

    Also check database properties if filegroups have autogrow.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  6. geeth_rajesh New Member

    Hi friends...

    thanx a lot..the tips given were really useful.
    -rajesh

Share This Page