May 26, 2016 at 12:40 pm
Hello SQL Experts,
I'm not well versed on troubleshooting growing transaction logs. However, I have found DBCC SQLPERF(logspace) very useful in monitoring the logs.
My question is, I've been collecting the data using SQLPERF(logspace) each hour for a couple of days now, but I notice in some cases that the Log Size does not change hour-to-hour, but the Log Space Used changes dramatically. What am I missing here? I thought as the log size grew, I would see a change in the log space used. But when I don't see a change in the log size, I'm wondering why the log space is increasing.
My network admin is telling me the log space is growing out of control, but I'm not seeing the actual logs grow. Can someone point me in the right direction to start looking? I'm using SQL Server 2008 (not R2), I take full backups nightly and transaction log backups every 10 minutes.
Thanks for the help!
Shair
May 26, 2016 at 12:57 pm
The amount of space within the log file that is actively being used can fluctuate based on several things, including backups, active transactions, replication, Availability Groups, etc.
What you're seeing is that the log file itself is the same size, but how much of it is being actively used is changing (most likely because of the log backups, but there could be other factors).
I'm not going to go further into the details of it all here, but I definitely recommend that you check out this excellent book: http://www.sqlservercentral.com/articles/books/94938/
Cheers!
May 26, 2016 at 1:05 pm
Thanks for the info and book reference! I'll definitely check it out.
I don't need to be hand fed, but sometimes just pointed in the right direction when I'm totally lost 🙂
Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply