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

Question

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

DarylAndDaryl

Default port

Points: 1491
More actions
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.

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

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

Charles Otten Default port Points: 1475 More actions · Answer 1

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.

hoo-t SSChampion Points: 12713 More actions · Answer 2

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

hoo-t SSChampion Points: 12713 More actions · Answer 3

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)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

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)

Robbert Hof SSCrazy Points: 2778 More actions · Answer 5

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