August 17, 2011 at 8:11 am
Hi,
We have a database that is currently about 60GB size (without t-log size) and the Recovery model option set to Full. We do a nightly backup (around 12am) and Transaction-log backup every hour. We also have a maintenance (rebuild index) that runs once a week and when the maintenance job runs we have T-Log backup running every 7 mins. The initial file size of the t-log was set to 87MB with 10 percent growth and Unrestricted file growth. We have Auto Shrink turned false and auto create stat and auto update set to True. We use the below script to backup the t-log.
After a few months (maybe 5-6 months) the t-log size will be about 10GB. I thought the reason for backing up t-log file is to reduce the size of t-log from growing? We do not want to change the recovery model to simple or run shrink on the t-log. What can I do to keep the t-log size from growing and growing and growing? Am I missing something here?
Thanks in advance,
USE DBName
BACKUP LOG [DBName] TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DBName_LOG.trn'
WITH
NOFORMAT,
NOINIT,
NAME = N'DBName_Transaction_Log_Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
August 17, 2011 at 8:28 am
You may find the reason by reading this article by Gail Shaw:
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
August 17, 2011 at 8:56 am
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.
"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 18, 2011 at 3:50 pm
bitbucket-25253 (8/17/2011)
You may find the reason by reading this article by Gail Shaw:http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Thanks for the article; it is a nice article. I went through, but as the article indicated, it doesn't help you solve the issue. I went through the suggestion, but that didn't help. The reason is that we don't have Replication, Database Mirroring, Snapshot creation running.
August 18, 2011 at 3:52 pm
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.
August 18, 2011 at 4:12 pm
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
"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 18, 2011 at 7:57 pm
Just in case you don't know... even though you're doing constant backups on the T-Log file during maintenance, IIRC, the TLog file will still grow to about 1.5 times that of the largest table that has a clustered index or the largest non-clustered index, whichever is the largest.
For the growth that's occuring during non-maintenance periods, it may simply be a fact of life or you have some nasty ol' queries with "many-to-many " joins that some folks are using either DISTINCT or GROUP BY to get rid of the duplicates being produced.
With a relatively small DB of 60GB, I'm betting on the latter of those two.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2011 at 5:03 am
Jeff Moden (8/18/2011)
Just in case you don't know... even though you're doing constant backups on the T-Log file during maintenance, IIRC, the TLog file will still grow to about 1.5 times that of the largest table that has a clustered index or the largest non-clustered index, whichever is the largest.For the growth that's occuring during non-maintenance periods, it may simply be a fact of life or you have some nasty ol' queries with "many-to-many " joins that some folks are using either DISTINCT or GROUP BY to get rid of the duplicates being produced.
With a relatively small DB of 60GB, I'm betting on the latter of those two.
Absolutely true, but the fact that the log is growing all the time concerns me (assuming I'm understanding what the OP is saying). You usually reach a state of equilibrium on most systems. Constant growth of the log seems off.
"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 19, 2011 at 6:07 am
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
Hey Grant,
Does this query return pending replicated transactions like DBCC OPENTRAN?
The OP states no replication, but I wonder if there might have been replication at one time?
I had a what seems to me like a very similar issue about a year ago:
http://www.sqlservercentral.com/Forums/Topic967700-291-1.aspx
Turned out to be remnants of the first attempts to configure replication - long since abandoned.
Sincerely,
Dan B
August 19, 2011 at 6:12 am
skrilla99 (8/19/2011)
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
Hey Grant,
Does this query return pending replicated transactions like DBCC OPENTRAN?
The OP states no replication, but I wonder if there might have been replication at one time?
I had a what seems to me like a very similar issue about a year ago:
http://www.sqlservercentral.com/Forums/Topic967700-291-1.aspx
Turned out to be remnants of the first attempts to configure replication - long since abandoned.
Sincerely,
Dan B
Yeah, it probably would. It's completely unrefined. It's going to show system connections & stuff that you don't care about. But it'll get you the info you do need.
"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 19, 2011 at 6:20 am
Grant Fritchey (8/19/2011)
skrilla99 (8/19/2011)
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
Hey Grant,
Does this query return pending replicated transactions like DBCC OPENTRAN?
The OP states no replication, but I wonder if there might have been replication at one time?
I had a what seems to me like a very similar issue about a year ago:
http://www.sqlservercentral.com/Forums/Topic967700-291-1.aspx
Turned out to be remnants of the first attempts to configure replication - long since abandoned.
Sincerely,
Dan B
Yeah, it probably would. It's completely unrefined. It's going to show system connections & stuff that you don't care about. But it'll get you the info you do need.
OK, cool.
Is DBCC OPENTRAN deprecated?
Thanks,
Dan B
August 19, 2011 at 6:27 am
skrilla99 (8/19/2011)
Grant Fritchey (8/19/2011)
skrilla99 (8/19/2011)
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
Hey Grant,
Does this query return pending replicated transactions like DBCC OPENTRAN?
The OP states no replication, but I wonder if there might have been replication at one time?
I had a what seems to me like a very similar issue about a year ago:
http://www.sqlservercentral.com/Forums/Topic967700-291-1.aspx
Turned out to be remnants of the first attempts to configure replication - long since abandoned.
Sincerely,
Dan B
Yeah, it probably would. It's completely unrefined. It's going to show system connections & stuff that you don't care about. But it'll get you the info you do need.
OK, cool.
Is DBCC OPENTRAN deprecated?
Thanks,
Dan B
Not yet... DMOs just give you so much more power. I go straight to them for everything I can now. It would be trivial to add a bit of code to get the individual statement instead of just the SQL text, and the execution plan... You can really go to town with these things.
"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 11:13 am
Jeff Moden (8/18/2011)
Just in case you don't know... even though you're doing constant backups on the T-Log file during maintenance, IIRC, the TLog file will still grow to about 1.5 times that of the largest table that has a clustered index or the largest non-clustered index, whichever is the largest.For the growth that's occuring during non-maintenance periods, it may simply be a fact of life or you have some nasty ol' queries with "many-to-many " joins that some folks are using either DISTINCT or GROUP BY to get rid of the duplicates being produced.
With a relatively small DB of 60GB, I'm betting on the latter of those two.
The largest table we have has 17,478,146 row count, 1,685.844 MB data space and 2,691.922 MB index space. No user is allowed to run a query manually, and I'm assuming that the application connected to the DB has thousands of views and stored procedures, so the scripts may have distinct or group by in it.
August 22, 2011 at 11:17 am
4 TB in 1 table?
Missed the period! 🙂
August 22, 2011 at 11:32 am
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.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply