The Mystery of Transaction Log Size Growth???

  • 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

  • You may find the reason by reading this article by Gail Shaw:

    http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • 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

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

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

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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

  • 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

  • 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

  • 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

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

  • 4 TB in 1 table?

    Missed the period! 🙂

  • 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