February 10, 2009 at 9:19 am
Hi expert,
Here are the details about the DB
- SQL 2005 STD
- The DB is backup in FULL every night
- Autogrowth is set
-Auto Shrink Auto
- Simple Recovery mode
The MDF size is 40 and the log 26GB.
I got often error message that the transaction logs are full, to fix that I close the SQL and then manually shrink the both files.
1- Is there any settings or something that I can do to avoid the transaction log to growth...
I don’t think just add more space will fix my problem long term
2- Does the logs are growing that much because the query are too long or not optimize
3- Is there any command that I can add in a job to force transaction log to be shrink at the end of my job?
February 10, 2009 at 9:34 am
Turn AutoShrink OFF!
Shrinking a database regularly is a terrible thing to do. It just has to grow again and that growth is going to slow things down. Repeated shrinks and grows will cause major fragmentation.
Size the log based on the max size it needs to be for the work that's done and leave it alone. Size the data file according to the data growth you expect, and manually grow it as necessary. If you need more drive space, get it.
Are you sure the recovery model is simple?
Is a once a dy backup sufficient? If the DB fails just before the backup, is the loss of a full day's data acceptable?
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
February 10, 2009 at 9:37 am
It sounds like you're maxing out the transactions. You're in simple recovery mode, so completed transactions should be removed from the log at the checkpoints. To max out the log means that you've got open, uncomitted transactions, that are filling the log. That's where you should focus your efforts.
Turn off auto-shrink. That is a huge performance drain in two directions. It's going to cause slow-downs while it shrinks the log and then, it'll cause slow downs while it regrows the log to the size the log needs to be to support your transactions. Set it at a given size and then don't let it auto-grow, or let it auto-grow, but only so far. then you won't fill the drive.
"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
February 10, 2009 at 9:54 am
Thx for the advice,
If i recap
- By turning the Autoshink Off i will increase the performance, however the transaction will still get empty after the Full backup ?
- Yes DB is set to Recovery model : Simple
- Im affraid to set the log at the fix size, because if it need to grow more the job or query will fail because the size maximum will be reatched ?
- If it normal that my transaction log i getthing so big ?
- No way to force it to shrink or to empy the transaction logs when a job is completed ?
February 10, 2009 at 10:39 am
b.larochelle (2/10/2009)
Thx for the advice,If i recap
- By turning the Autoshink Off i will increase the performance, however the transaction will still get empty after the Full backup ?
The transaction log, since it's in simple recovery, should be having completed transactions removed at the checkpoint, which runs, automatically, approximately every two minutes (or so, depending). There's no corellation between a full backup and a log backup. If you need log backups, you need to change recovery from Simple to something else.
- Yes DB is set to Recovery model : Simple
- Im affraid to set the log at the fix size, because if it need to grow more the job or query will fail because the size maximum will be reatched ?
But if you fill the drive letting the log grow forever you DON'T get a crash? You're going to have to get the transactions under control and you're going to have to deal with an error, either when the log is full, or when the drive is.
- If it normal that my transaction log i getthing so big ?
Not being there and seeing what you're doing, I can't say specifically, but in general, because you have so many transactions that it's filling up, or the transactions you have are so big and so long running that it fills up, or some combination of the two.
- No way to force it to shrink or to empy the transaction logs when a job is completed ?
Again, you don't want to shrink the log over & over. That's a bad thing. You shouldn't have to do anything to clean out a transaction log that is in simple recovery. It will happen automatically.
"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
February 10, 2009 at 1:08 pm
Grant Fritchey (2/10/2009)
You shouldn't have to do anything to clean out a transaction log that is in simple recovery. It will happen automatically.
Indeed. If the log space is not reused in Simple, then there's something wrong. Since you're on 2005, use the sys.databases view and check the log_reuse_wait_descr column for the reason the log's not been reused. In simple, the only reasons should be 'open transaction' and 'transactional replication'
Please read through this: http://www.sqlservercentral.com/articles/64582/
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply