June 12, 2010 at 6:11 pm
Hello all. I watched a transaction log grow 20 GB in 2 hours for an app that usually only has 1GB a day in total tran log logging. I was running dbcc opentran during this time and only saw a transaction in reference to the service broker that was long running. The app uses sql dependencies to reduce hits against the db. When using sys.dm_exec_query_stats i only saw this same transaction as possibly being an issue. It had a most recent execution time of 630 seconds. I will get the full details out as to what this query was and what it was doing. I do not have that at this time. However, easy question? Are there any known bugs with service broker or sql dependencies that could cause crazy tran log growth?
During the time of the issue i changed the recovery model to simple. I used the UI. It took about 3 minutes for the hour glass to go away. So something was blocking the recovery change. When it finally finished changing to simple, i did shrink file on the tlog and then changed it right back to full. Oddly enough there was no further growth. So either whatever was causing log growth coincidentally stopped right after the change. Or the change itself fixed he problem.
If any could give some pointers for troubleshooting further i am listening. Again using the dmv it showed nothing longer than a second or 2 that had run as far back as those stats go excluding the 630 seconds on the service broker query. Also again dbcc open tran did not show anything. Lastly when running sp_who2 there was no blocking. i ran it numerous times and nothing. I did not have blocked process report on, but if there was blocking and open trans they would have shown on the dbcc opentran.
CONFUSED... HELP!!!
Jimmy
"I'm still learning the things i thought i knew!"June 13, 2010 at 1:20 am
You didn’t specify if you had any log backups during the time that you log was growing. If you had no log backups during that time, then it isn’t important if you had any open transactions.
When you modified the database’s model to simple, you broke the log chains. Modifying the database’s model to full will take effect only after the first full backup that you’ll do after it was modified to full model again. If you didn’t run a full backup on the database, you have to do it.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 13, 2010 at 9:46 am
Thanks for the reply. During the issue i ran 3 tlog backps. The log still continued to grow. So it was acting as if a transaction was keeping the logical log open (not able to truncate) even though only the service broker trans showed in dbcc opentran.
I did not run a full backup after switching back to full. I would think the tlog would have still grown, even if simple, but i didnt check to see if the checkpoints were firing keeping the log from growing. I know when in simple the log will get truncated automatically after a checkpoint. So maybe check points were happening quickly. The server is barely utilized so SQL might be running checkpoints rapidly. I did not know that you had to run a full backup before the db would be truly in full recover mode again after a change from simple.
But to the point during the log growth, between 9-11 am i ran 3 tlog backups and the log file was continuing to grow without open transactions. Any input would be appreciated.
Jimmy
"I'm still learning the things i thought i knew!"June 14, 2010 at 1:39 am
imSQrLy (6/13/2010)
Thanks for the reply. During the issue i ran 3 tlog backps. The log still continued to grow. So it was acting as if a transaction was keeping the logical log open (not able to truncate) even though only the service broker trans showed in dbcc opentran.
Maybe you had one statement that caused a massive data modification. For example one SQL Statements that updates a big table with no where clause. In such case you won’t see it as an open transaction, but as long as this statement is running, it keeps writing to the log.
I did not run a full backup after switching back to full. I would think the tlog would have still grown, even if simple, but i didnt check to see if the checkpoints were firing keeping the log from growing. I know when in simple the log will get truncated automatically after a checkpoint. So maybe check points were happening quickly. The server is barely utilized so SQL might be running checkpoints rapidly. I did not know that you had to run a full backup before the db would be truly in full recover mode again after a change from simple.
There are few events that cause checkpoint. If I’m not mistaken one event that causes check point is when 70% of the log’s space is being used. Most chances are that when you use simple recovery model and you don’t have a single statement that causes massive data modification and you don’t have a long running transaction, you won’t see the log growing.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 23, 2011 at 12:59 pm
did you ever find out what the problem was. I am seeing the same behaviour in one of my databases... :w00t: ... and regardless of what other people had posted,,, this is related to service broker. If I kill the offending process and block the id from logging in I am able to backup the tlog and it truncates. Otherwise I can't.
March 23, 2011 at 1:40 pm
Please post new questions in a new thread. Thank you.
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
September 13, 2011 at 5:45 am
We have the similar issue. Log is growing because of Service Broker. Did anyone have the solution?
Hemant Patel
September 13, 2011 at 5:46 am
Please post new questions in a new thread. Thank you.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply