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.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
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
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.
- Calculate a running Total
- Brad Paisley – This is country music
- George Strait – I can still make cheyenne
- Dawns – TailLights