MSSQL_NOOB (12/31/2012)
If the percentage used is averaging around, say 5% BEFORE transaction log backup; does that mean there's just a spike at one point in time of use?
Maybe. You'd need to investigate and see if it was a one-time spike or if it's a regular spike. Spikes in log usage are often regular.
I used to have a DB that had a 250GB log file. During the week it used 10% of that at most. On a sunday night, when we did imports, it reached 85%.
If so, does that also means I should be issuing SHRINKFILE commands from time to time, after the full backup?
Nope. Do not schedule shrink operations. Once off, or after uncommon operations like large once-off data imports or archiving processes.
I'm not trying to 2nd guess you ... but I just do not understand why the following recommendations: "Do this shrink only once though."
Because growing a log is an expensive operation and causes internal fragmentation if the growth increments are inappropriate. Shrink is also an expensive operation.
So shrink often and you get into this nice shrink-grow cycle that takes huge amounts of resources and time, fragments the log and achieves nothing.
I understand that everytime a log file needs to auto expand, it takes up resources. But, if it's only using say 75MB (5% of 1.5GB); wouldn't it be good measures to shrink it from time to time - to save space not used 99% the time and the space allocated is only for 1 time use?
Not really. There's no penalty or problem from lots of free space. If the log really needs that space, it'll just regrow (and if the space isn't there you'll get log full errors and failed transactions)
And considering my anticipated users usage is from 7am - 9pm; wouldn't a "cleanup" be recommended after 9pm? All data should be committed (and written to data file), full backup will make sure I have a copy of data file of all committed transactions, wouldn't it?
What's the full backup got to do with anything? It doesn't truncate the log, it doesn't shrink the log.
SQL is not MS Access that requires regular compact and repair to function properly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability