Post reply

Free space in Data file

  • December 30, 2003 at 4:05 am

    #59107

    Dear friends,

    Sp_spaceused reports the free space in the whole db including data and log space. is there a way to identify the space used in the data files alone(data,index,text,image etc)

    thanks to all in advance

    anil

  • December 30, 2003 at 5:19 am

    #488162

    To see the log space usage you can use dbcc sqlperf (logspace)

    sp_spaceused reports only the data and index space usage (in the reserved, data, index and unused columns) and this is what you want I guess.

    Only database_size column combines the file size of the data AND the log files.

    Bye

    Gabor



    Bye
    Gabor

  • December 30, 2003 at 9:39 am

    #488210

    sp_helpfile will tell you the database file size which have been allocated to your database.

    SELECT FILEPROPERTY('yourdatabaselogicalfilename', 'spaceused') will give you space used for your database files.

  • December 30, 2003 at 9:45 pm

    #488328

    Dear friends,

    I am clear now. Thanks to all of U

    Anil

  • December 31, 2003 at 5:56 am

    #488336

    quote:

    sp_helpfile will tell you the database file size which have been allocated to your database.

    SELECT FILEPROPERTY('yourdatabaselogicalfilename', 'spaceused') will give you space used for your database files.

    keep in mind this returns # pages ! (8k)

    I run DBCC UPDATEUSAGE ('mydb') with count_rows before handeling space issues. This may take a while !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • December 31, 2003 at 10:00 am

    #488377

    DBCC showfilestats (-1, -1) reports usage for all files in the current database (in extents).

  • January 4, 2004 at 7:59 pm

    #488611

    I use sp_helpdb because this gives you detail on data and log files as well as the overall database and you don't have to already be in the database you want to query like you do with sp_helpfile

Viewing 7 posts - 1 through 6 (of 6 total)

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