October 23, 2009 at 2:38 am
Hi,
i have a DB in a simple recovery model.
But we get an error messages like:
the transaction log is full for data xxxx. to find out why space in the lod cannot be reused, see the log error
what can we do?
October 23, 2009 at 2:57 am
Have a look at the "managing transaction logs" link at the bottom of this post. it should answer any questions that you have.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 23, 2009 at 3:26 am
The error says to query sys.databases to see why the log cannot be reused.Have you done so? If so, what is the value of log_reuse_wait_desc for the DB in question?
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
October 23, 2009 at 3:34 am
hi yes i requested it.
NOTHING
October 23, 2009 at 3:41 am
Did you query it at the time that the error occurred or sometime later? If it was a long running transaction, it could easily have resolved itself.
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
October 23, 2009 at 3:44 am
i requested later...
How to avoid this error?
October 23, 2009 at 4:00 am
Put some regular monitoring in place so that you can see what leads up to the error. Use sys.databases and DBCC SQLPERF(LOGSPACE)
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
October 23, 2009 at 4:05 am
ok.
thank you
October 23, 2009 at 5:35 am
Fred,
If this is a regular error then you need to figure out correct size of the log files. Did you mention automatic increment for log files? What about the free space in the drive where log file resides? Gila has rightly said to monitor the database with following commads:
DBCC SQLPERF(LOGSPACE)
DBCC LOGINFO (<database name>)
-:cool:lk
October 23, 2009 at 6:22 am
Usually this occurs when the log is trying to grow to accomodate a transaction if the log is in simple recovery. You probably need to monitor the log size much more closely and set it larger than it currently is.
You don't have it on auto-shrink do you?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 23, 2009 at 6:25 am
hello
we have restricted the log file autogrowth to control it.
Therefore we have resized the max log size
it should be ok now.
Thanks for all replies
October 23, 2009 at 7:09 am
fred2002 (10/23/2009)
we have restricted the log file autogrowth to control it.Therefore we have resized the max log size
So to prevent an error that's cause when the log file is too small you're preventing it from growing?
Sure that's going to fix it?
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
October 23, 2009 at 7:19 am
no
i explained it wrong.
first we restricted the size to 300MB
But now due to the errors we allw it to 1.2GB.
MS advise to fix a max size for transaction log
October 23, 2009 at 1:11 pm
fred2002 (10/23/2009)
noi explained it wrong.
first we restricted the size to 300MB
But now due to the errors we allw it to 1.2GB.
MS advise to fix a max size for transaction log
Do you have a document from MS that states that? Limiting the size of your transaction log will only cause processes to fail. Yes, there is the potential that you use up all space available on the drive - but then again, you should have a dedicated drive for your transaction log.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 23, 2009 at 6:17 pm
Jeffrey Williams-493691 (10/23/2009)
fred2002 (10/23/2009)
noi explained it wrong.
first we restricted the size to 300MB
But now due to the errors we allw it to 1.2GB.
MS advise to fix a max size for transaction log
Do you have a document from MS that states that? Limiting the size of your transaction log will only cause processes to fail. Yes, there is the potential that you use up all space available on the drive - but then again, you should have a dedicated drive for your transaction log.
I don't know if it's in the MS documentation or not, but I'll say it. Better that the app error out because the log is full than the log fill a drive, dedicated or not. That's not to say I don't use auto-grow on my logs, I do. But I will also put limits on them so that they don't grow in an out of control manner because of some bad transaction.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply