August 22, 2011 at 11:37 am
Have you looked through this? http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
August 22, 2011 at 11:41 am
EjSQLme (8/22/2011)
Grant Fritchey (8/18/2011)
EjSQLme (8/18/2011)
Grant Fritchey (8/17/2011)
The size of the transaction log is usually reflective of the size of the transactions. Have you tried monitoring the freespace in the tran log during the index rebuild. I suspect that's when you're running into issues.In monitoring the t-log size, the file seems to be growing even during the week when the maintenance is not running.
So it's constantly growing, but, you're running transaction log backups? And they're not failing? You're getting successful completion and you can see the files and they have a size and everything?
I've seen something similar before, when there were transactions that were stuck open. This is a bare-bones query, but it ought to show you if you have open transactions and which query they are and if they're blocked. There's more that can be done with this, but this will at least alert you.
SELECT dtat.transaction_begin_time,dtat.transaction_status,
dtat.transaction_state,
der.blocking_session_id,
dest.text
FROM sys.dm_tran_active_transactions AS dtat
JOIN sys.dm_exec_requests AS der
ON dtat.transaction_id = der.transaction_id
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
Yes, it keeps growing and in looking at the transaction log backup history, i don't see any failure and I do see the transaction log backup file growing each time a back up is done. We Sunday night, we had a maintenance done and the T-log file grow for 10GB and the t-log backups has not reduced the size even though the transaction log backup file is about 10GB also.
In running the query you provided a few time a day, I don't see a query that is stuck. Each time I run it, I see different queries.
But wait a sec, the transaction log backup will not reduce the size of the transaction log, ever. It will mark transactions inside the transaction log and those records will be disposed of creating space within the transaction log, but not reducing the size of the file.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2011 at 12:00 pm
So, is it normal for a t-log to be over 10GB even though you are doing a t-log backup? If so, is there a way to manage the t-log size under 2GB? If there is way how beside doing a regular t-log backup?
August 22, 2011 at 12:06 pm
EjSQLme (8/22/2011)
So, is it normal for a t-log to be over 10GB even though you are doing a t-log backup? If so, is there a way to manage the t-log size under 2GB? If there is way how beside doing a regular t-log backup?
From what you're saying, your tran log needs to be 10gb to support the maintenance routine that you run on Sunday. Until and unless you change that routine, no I don't think you can shrink the log.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2011 at 12:13 pm
EjSQLme (8/22/2011)
So, is it normal for a t-log to be over 10GB even though you are doing a t-log backup?
I used to work with a DB that had a 250GB transaction log file. Log backups every 15 minutes.
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
August 22, 2011 at 12:19 pm
Grant Fritchey (8/22/2011)
EjSQLme (8/22/2011)
So, is it normal for a t-log to be over 10GB even though you are doing a t-log backup? If so, is there a way to manage the t-log size under 2GB? If there is way how beside doing a regular t-log backup?From what you're saying, your tran log needs to be 10gb to support the maintenance routine that you run on Sunday. Until and unless you change that routine, no I don't think you can shrink the log.
What you are telling me is that as the DB data grows, the t-log will keep growing since it is going to take more time/space to rebuild indexes? Therefore, more than likely, the t-log file will keep growing?
August 22, 2011 at 12:22 pm
GilaMonster (8/22/2011)
EjSQLme (8/22/2011)
So, is it normal for a t-log to be over 10GB even though you are doing a t-log backup?I used to work with a DB that had a 250GB transaction log file. Log backups every 15 minutes.
Do you think increasing the t-log backup to 30 mins would help more?
August 22, 2011 at 12:51 pm
Typically the more frequent the log backup the smaller the log file can be. But that requires no long-running transactions, no replication. If you're doing index rebuilds in full recovery, your log will need to be at least as big as the largest table in the DB, probably bigger.
Why are you trying to get it smaller? Log files should be left at the size they need to be for regular DB activity.
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
August 22, 2011 at 1:18 pm
GilaMonster (8/22/2011)
Typically the more frequent the log backup the smaller the log file can be. But that requires no long-running transactions, no replication. If you're doing index rebuilds in full recovery, your log will need to be at least as big as the largest table in the DB, probably bigger.Why are you trying to get it smaller? Log files should be left at the size they need to be for regular DB activity.
We want it to be smaller b/c of disk space issue. The biggest table is about 2.7GB and yes, we do run index rebuild once a week, but the t-log size is currently over 10GB.
August 22, 2011 at 1:23 pm
If the log is 10GB from regular database activity then it needs to be 10GB and you need to ensure you have that much space.
Maybe reducing the interval between the log backups will reduce the size it needs to be, but it's not going to shrink it to nothing. If the largest table is just under 3GB then there is no way you can keep that log under 2GB if you're rebuilding in full recovery.
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
August 23, 2011 at 9:51 am
I got you. Thank you to everyone who took the time and helped answer my question. Have a good one.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply