Microsoft – SQL Server – Log File Utilization and Running Total

Here are I am taking a good look at Database Log File Sizes. And, needing a good
way to determine the overall running total.

I think I can use “dbcc sqlperf(logspace)”.

But, as I had a working code already, I chose to use that one.

The working code basically reads through sys.database_files and uses the MetaData function fileProperty.

The only thing missing is a good path to arriving at running total.

So Googled for one and ended up a finding a good one in very quick succession.

Give Credit to Mike Forman

The APPLY operator in SQL 2005 and higher works for this:

select ,
    t.somedate ,
    t.somevalue ,
from TestTable t
 cross apply (
               select sum(somevalue) as runningTotal
    	       from TestTable
    	       where somedate <= t.somedate
    	     ) as rt
order by t.somedate

Giving credit to Mikael Erikson

In SQL Server 2012 you can use SUM() with the OVER() clause.

       over(order by somedate rows
             unbounded preceding
           ) as runningtotal
from TestTable

It reminds me of the song that talks about:

  • You ‘re not supposed to say the Word Cancer in a song
  • And, telling Folks that Jesus is the answer, can rub them wrong
  • Well, you like to drink a cold one on the weekend
  • Do you wanna say I am sorry, but don’t know how
  • (King) George Strait – Amarillo by the Morning
  • Tammy Wynette’s – Stand by your Man (re-birth by Hillary Clinton)

During the 44th Country Music (heard on Nov 2010) Award the Artist who sang it had this to say:

I wanna talk to the fans.  It sounds like a cliche when you say thanks to the fans.

The great thing about country fans is, when you say fans, I don’t even mean mine. You guys are loyal to everyone in this room. It’s the most amazing loyal fan base in the world.

Here is a sample code to get SQL Server Transaction Log File Sizes:

      file_id as fileID  
   --, name 
   --, physical_name
   , (size / 128) as sizeInMB
   , (((FILEPROPERTY(name, 'spaceUsed'))) / 128) as spaceUsedInMB    , tblDBFileInUsedRT.spaceUsed as spaceusedRunningTotalInMB
   , (( size - (FILEPROPERTY(name, 'spaceUsed'))) / 128) as 
  , tblDBFileAvailRT.avail as availRunningTotalInMB

from    sys.database_files tblDBFile	

cross apply 

    sum(((FILEPROPERTY(, 'spaceUsed'))) / 128)        as spaceUsed	
  from   sys.database_files tblDBFileTotal
  where  (tblDBFileTotal.type = 1)
  and    tblDBFileTotal.file_id <= tblDBFile.file_id

  )	tblDBFileInUsedRT

cross apply 

    select sum(( size - (FILEPROPERTY(name, 'spaceUsed'))) / 128) 
             as avail	
    from   sys.database_files tblDBFileTotal
    where  (tblDBFileTotal.type = 1)
    and    tblDBFileTotal.file_id <= tblDBFile.file_id

  ) tblDBFileAvailRT


 	   (tblDBFile.type = 1)


order by tblDBFile.file_id asc


The Artist I was referring to is “Brad Paisley”.  And, the video is available @

Brad Paisley – This is Country Music


As my Church’s founding pastor likes to say “Give it a little time”.

Hopefully, sooner or later you will have enough respect for what you do and realize that you only have a small window to do it. And, once that dawns on you, you will truly enjoy LIFE more and appreciate and stand behind’s other work.

The silliness of “Java vs .Net”, “Oracle vs MySQL vs SQL Server” starts to give way to what you ‘re trying to do for your paying customers.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s