December 3, 2007 at 4:06 am
I'm having a database whose recovery is simple and the tran log is growing frequently and says "tran. log is full" error.
How is that possible for a database in simple recovery mode could enable log growth....any ideas
December 3, 2007 at 4:16 am
The transaction log is still written to in Simple recovery mode.
December 3, 2007 at 4:25 am
yes but it would be a very lesser amount, not that much to say an error like "Log is full. Backup the tran. log".
How to check this huge growth on a db having with simple mode.
December 3, 2007 at 4:46 am
the transactions are still written to the log in full, it's just truncated at the next checkpoint.
December 3, 2007 at 10:46 am
There could also be an error with the replication agents.
December 3, 2007 at 11:34 am
If you were doing a big transaction, it could still cause the transaction log to grow. It doesn't log any less, it just truncates once the transaction commits.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 3, 2007 at 12:47 pm
As mentioned above, the log is still written. Typically this is one transaction (or relatively few of them) and they are cleared out. However if you have an open transaction, that can prevent clearing, and large transactions can make things grow.
DBCC OPENTRAN, check for open trans.
Size the log large enough to handle your largest set of transactions within a short period of time (5-10 minutes) or as long as the longest transaction is open. Then you'll be fine.
December 3, 2007 at 2:18 pm
Queries use tempdb for sorting/grouping etc. Please correct me if I am wrong here. I was in a similar situation. I bounced the engine(off hours) and set the tempdb to 1 MB to start with. We are not allowed SQLmail, so I check the tempdb size during routine maintenance.
December 3, 2007 at 2:36 pm
chandrika5 (12/3/2007)
Queries use tempdb for sorting/grouping etc. Please correct me if I am wrong here. I was in a similar situation. I bounced the engine(off hours) and set the tempdb to 1 MB to start with. We are not allowed SQLmail, so I check the tempdb size during routine maintenance.
Why set it so low? If you know it's going to grow - why not give it some room to work with so it doesn't end up fragmented to an extreme across that volumn? Set tempDB to a few GB's with a goodly size LDB as well (enough that it shouldn't have to autogrow), and the leave it alone....And set its autogrow to have a decent growth factor (like 100MB -300MB), so that it will grab decent size chunk if it really needs it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 3, 2007 at 4:36 pm
You want to set tempdb to be at the size that it needs for the peak. Anything less can cause delays when tempdb grows. 1MB doesn't make sense.
December 4, 2007 at 5:31 am
Thank u all folks for your responses.
There were some active transaction that was running for a very long time could be due to that.
Murali.a
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply