Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Unanswered: Data file space used calculations

    Hi,

    Is there any table I can query from to get the space used by a data or log file? I can get this info from TaskPad but I want to query from tables. The sp_spaceused procedure gets the info for a database or table but not the 'data or log' files.

    Thanks for any help.

    Vinnie

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    You can do this

    select cast(size * 8 as int) as Size from dbo.sysfiles

    The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134
    Originally posted by rhigdon
    You can do this

    select cast(size * 8 as int) as Size from dbo.sysfiles

    The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)

    HTH
    Thanks a lot but the sysfiles can give me only size whereas I need the space used and space free for a data file /log file. Any other suggestions are appreciated.

    Thanks in advance.

  4. #4
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    For log file you can do:

    dbcc sqlperf('logspace')

    I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  5. #5
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134
    Originally posted by rhigdon
    For log file you can do:

    dbcc sqlperf('logspace')

    I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.

    HTH
    Hi,

    That is exactly what I am looking for is 'data file'. I could get for log file in sysperfinfo table. I was unsuccessful to find any info for datafile to calculate space used or space free. How does the Taskpad display these things? They may be doing from table. What is it is the question? If any one can help It will be a great help.

    Thanks
    Vinnie

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    True, you can use SP_SPACEUSED to get the data, index space usage and DBCC SQLPERF(LOGSPACE) for the %age of Tlog used for any database.

    Run SP_HELPFILE to get the information for physical files associated to that database.

    Refer to Vyas's link for more information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •