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
http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver

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

select
    t.id ,
    t.somedate ,
    t.somevalue ,
    rt.runningTotal
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
http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver

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

select
    id,
    somedate,
    somevalue,
    sum(somevalue) 
       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:

select 
      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 
       availInMB
  , tblDBFileAvailRT.avail as availRunningTotalInMB

from    sys.database_files tblDBFile	

cross apply 
  (

  select 
    sum(((FILEPROPERTY(tblDBFileTotal.name, '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

where
	(

 	   (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

http://www.youtube.com/watch?v=n_KxM4rU38Q

 

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.

References:

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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