Transaction Log Space Utilization

A client asked me to investigate log space utilization on a specific database . It was  currently allocated at 53 gig, and the question was whether that was big enough or too big. I gave a pretty generic answer, which is that it needs to be big enough to contain all active transactions between dumps & during replication, plus a fudge factor. There’s a GREAT article here:

 

http://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

it also shows some loosely documented /undocumented commands to take a look.

At the time, they were using .22% (Yep, less than 1%) of the 53 gig transaction log. It’s impossible for me to tell how big it needs to be (without a deep understanding of the application, but a potential guideline is 25% bigger than the biggest transaction log backup you have; so, if those are in the 1-2 gig range, they had a lot of room to spare here.

Note that with SQL 2005 (their version), you can not shrink the log file below its initial size (this is corrected in SQL 2008 & later).

SQL Saturday

My most common response to Orlando's SQL Saturday "What could the speaker do differently to improve?" was "Give him more time!"

Thank you all who attended, it was a pleasure meeting you in person. I'll be doing the SAME seminar in Nashville in 2 weeks... check out www.sqslsaturday.com and register!

Along these lines, we do webinars about monthly, send me an email if you'd like to be added to the email list. In Orlando, a gentleman came up to me & claimed my webinar changed his life... your mileage WILL vary, but there's always lots of great information.

Thank you all again for coming out to see us!

Jeff