log file
-
Log file grows up really quickly. Anyone know what are the reasons?
Do you think is because we do no use begin...end on the queries? or do we need a checkpoint?
The database is in simple recovery model and we do full backup every night.
I have to do manually truncate and then shrink to reduce the size...any beter ways?
-
Any chance you are replicating the database? In which case I have seen similar problems. Also if you have a process running that requires a large log (I've done several software migrations where this happend) you may be out of luck. The only thing you can do then is find the offending process (hopefuly it will only be 1) and try to break it into pieces. Generally if you can break it into multiple transactions the log won't grow quite so large.
If it makes you feel any better I had one update statement on a 20 gig database that grew my log file to 80 gig. All I could do was truncate and shrink (down to something like 10gig) in my script right after the update.
Ken
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
-
If this is occuring on a regular basis, you may want to consider keeping the log file at a larger size. Remember that everytime the log has to grow, your application is taking a performance hit. The transaction logs on our production servers for HR and Finance are actually quite large compared to the amount of data stored in the logs (we do transaction log backups every 2 hours), but I would rather have the space allocated and available than have the users experience slow downs during the day as the log file has to expand due to a high volume of transaction activity.
It is a trade off, but one you may want to look into.
-
do NOT shrink the log file EVER. Back it up instead
* Noel -
i backed up with truncate only and shrink the file ...setup a job to run every 4 hours
-
The DB is in Simple recovery model so the log can't be backed up.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
What kind of sizes are we talking about here ?
database data is ??? Gb
database log is ??? Gb
Big/Large are all relative terms ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
grows 1.5gb per day.
-
How big is the data portion ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
if you keep shrinking it it will keep growing. Be as aggressive as you wish on the truncate - but the transaction file needs some room to do the operations you ask of it. The fact that it keeps growing after you shrink it is an indication that it needs more room than you are giving it so track those operations.
Try this: stop the shrink part for a day or two. You should see the thing grow for a while, then hit a certain size, then STOP GROWING. That's the "magic size", and you're going to want to leave it at that size. That's the amount of space it needs to do all of the work it's being asked to do.
----------------------------------------------------------------------------------
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? -
95 GB
-
once i shrank it back to 15GB from 35GB. then stopped the shrinking for 1 week and then it gew up to 40GB....and the disk ran out of space.
Thats why im setting up this job to run it every 3 hours. Otherwise we are gonna have problems again. I dont wanna take any riisk
-
MAtt is right you can calculate the amount of space that the transaction log can consume for 1-2 days then stay at those sizes and keep truncating the transaction log (not shrink). I think that should do the trick
"-=Still Learning=-"
Lester Policarpio
-
Based on your monitoring how much GB does the transaction log consumed in a day?if the transaction log did not consume the total amount of transaction log in a day you can consider truncating once a day just to maintain in that total amount of transaction log. Just a thought
"-=Still Learning=-"
Lester Policarpio
-
If you are doing a backup log with truncate only, then that tells me you are in bulk-logged or full recovery mode. You should be running scheduled transaction log backup between you full backups. This would keep your transaction log from continually growing once you have determined what size it should be to handle your normal day to day transaction load.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply