Tuesday, October 16, 2012

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).

No comments:

Post a Comment