Post reply

How do I get log file space allocation and space used?

  • January 3, 2005 at 12:21 pm

    #65869

    I am using DBCC SHOWFILESTATS to get the Total Extents and Used Extents of the data file for a database. From there I calculate the data file's total allocation (Total Extents * 64 / 1024) and the data file's total usage of that allocation (Used Extents * 64 / 1024). This gives me a T-SQL method of viewing a database's data file's allocation and usage that is similar to what Task Pad represents in the Enterprise Manager.

    Does anyone know of a way, similar to the above, to get a Transation Log's file allocation and usage? Once again, I am looking for a T-SQL representation of what the Enterprise Manager's Task Pad shows.

  • January 3, 2005 at 12:58 pm

    #535655

    I don't know the actual answer, but turn on the profiler and then get the info from enterprise manager.  You should then be able to get the SQL from the profiler.

     

    If the phone doesn't ring...It's me.

  • January 3, 2005 at 1:08 pm

    #535656

    Take a look at 'dbcc sqlperf( logspace)'.  One caveat though.  If there are multiple

    log files, it will aggregate the data rather than providing stats for each file.  There doesn't seem to be a clean way of getting information on separate log files.  SQL sees it simply as one Log.

    Steve

  • January 3, 2005 at 1:20 pm

    #535661

    By the way, our host, Steve Jones, wrote 'dbspCalcdbaSpaceDist' stored procedure, which pulls this information.  I think the procedure is available on this website.  There are a couple of minor issues that you may want to address if you decide to use it though.  I have implemented it in my shop and its working great!  I don't recall exactly what changes I made, but here's the comment I placed at the top of the procedure -

    modified table structure, corrected logical names,

      corrected percent calculations (also changed to percent used instead of

      percent free). changed to allow input parm to be positive value.

    If you would like to have my version, let me know.

    Steve (not Jones)

  • January 3, 2005 at 9:39 pm

    #535696

    Getting the usage is the hardpart... the names used below are the "logical" names for each file...

    SELECT FILEPROPERTY('northwind', 'spaceused')*1024*8
SELECT FILEPROPERTY('northwind_log', 'spaceused')*1024*8

    To get the allocated file size for each file, use sp_HelpFile.

    --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)
    Intro to Tally Tables and Functions

  • January 4, 2005 at 6:52 am

    #535758

    Look at this script:

    http://sqlserverstandard.com/downloads/200411/200411_deng.zip

    It's part of the November issue of SQLserver standard:

    http://www.sqlserverstandard.com/issue/

    Robbert

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

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