How to avoid fast growth of Transaction Logs
-
I’m currently experiencing fast growth of my database transaction logs which I don’t think it’s normal. Technically, all of my queries in my stored procedures are normalized and optimized but for sure the application running the database is I guessed the big factor causing the growth of the transaction logs.
Now, what I had in mind is to use the following commands: (after having the backup database)
1. DBCC SHIRNKDATABASE, which cause me an error say “…Transaction Log is in use” and the file size still the same.
2. DBCC SHRINKFILE, same file size nothing change
3.
Detach and Attach the database, delete the transaction log (.LDF) file and create new one. This process gives me better resolution but again, it does the same way.So my question is how will I completely avoid the fast growth of the transaction logs or how will I eliminate or automatically shrink the file size of the transaction log.
Additionally, to get rid of the TEMPD is FULL I’m always restarting the production server. Is this normal just to restore the original size of the TEMPDB?
BTW, Im using SQL Server 2000/SP4 Ent Ed. on Windows 2003 Ent Ed in IBM xSeries 346.
Looking for possible permanent solution. Many thanks!
-
Several questions first. I am assuming that your database(s) are in Full Recovery mode. What is your current backup strategy? How often, and what type of backups are you completing? Where does your tempdb database reside, and how much diskspace is availabel on the drive(s) used?
I am sure others on this site may have other questions as well.
-
In addition to the questions that Lynn has asked, can you give us some clarification on what 'fast growth' means to you and why you are concerned. You mentioned having your DB code optimized, but this really does not have an impact on your t-log growth. Your t-log growth is determined by the recovery model and transaction throughput.
-
thanks for the reply but allow me to say these screnarios in my way.
1. backup, i've created a backup maintenance plan every 2am then the backup file goes to my drp server which dump to a tape drive and before i do some altering im taking the database offline then copying a clean separate MDF file to a separate location.
2. tempdb resides of course in the same server/machine where my production databsae is also located in but in different directory.
3. disk space? for now i've allocated 200GB but the database as of now is only 5GB +/- however the transaction log file of the database grows upto 100GB in just 1 week or less.
my data is coming from SMS reason why i have 5GB of data (we have just stated this year). the orginal plan is having the database in clustered environment but since im getting this problem i've switched back to single server.
-
If your database is in Full Recovery Mode, as I assume, just completing a full backup nightly is the reason your transaction log is getting so big. Full backups and Differential backups do not truncate the transaction log. To truncate the transaction log, you need to be running periodic transaction log backups between full or differential backups. If you do not need point in time recovery of your data, and a single nightly backup is sufficient, then I would recommend changing your recovery mode to simple. This will keep the size of your transaction log small.
Once you have managed the size of your transaction log, the need to restart SQL to recreate tempdb should hopefully go away.
-
You are currently only backing up the data file once per day? If you have no need to recover to a point-in-time, then consider setting your recovery model to simple. If you need the abilitiy to recover to a point-in-time, you'll need to set up transaction log backups throughout the day. Read BOL topic 'Truncating the Transaction Log' for more info.
For your TempDB, it appears that you have it on a drive with limited space. Consider moving it to a drive with more space to grow. Beware that moving this to the same drive as your DB data or log file could cause I/O contention issues.
-
"fast growth" well this means that the transaction log file is getting bigger and bigger in a minute say i've started at 1mb then after a while in 5 minutes its now 10mb something like that (actual figure may vary).
Yes, I said the code is optimize becuase the stored procedures does the transaction of insert and update only of course there is a process where you have to validate the values to be inserted or updated.
theoretically, i understand how the transaction log is important but having my code in placed, i know that all successful transactions are already kept and can not be deleted nor altered.
to be more specific, i have this table that i dump all results in my procedures for logging purposes (just for a "debug" mode) to see the result of evert execution but after fetching the process the record will be deleted. now, going back to my question, how will i prevent or automatically trancate the unused space of the transaction log file. please dont tell me to remove the debug mode but i can easily do this but later on it will end up with the same problem.
the database is already configured to "auto shrink".
appreciate your replies !!!
-
Hi, I have some questions again in mind regarding your suggestions:
1. "running periodic transaction log backups between full or differential backups" - do you mean that i should run every hour a differential backup to trancate the unused space from the transaction logs?
2. if i will revert back to "simple" mode, is it seamless? does it applies even my database is operational? or i need to isolate it from the users? (which i think)
3. "need to restart SQL to recreate tempdb" - how by restarting/rebooting the server again? is there an other way to recreate the tempdb without rebooting the server?
Lynn, Thank you so much! i will wait for your reply. Thanks.
-
Do you need point-in-time recovery of your database or is a single nightly backup good enough for your purposes. If you don't need point-in-time recovery, change your recovery mode to simple. Doing this will allow SQL Server to automatically truncate the transaction log as transactions are checkpointed to your database. This will keep the transaction log from constantly growing.
If you need point-in-time recovery, you need to establish period transaction log backups between your full nightly backups, perhaps every two hours, depending on the number of transactions processed over time.
-
"You are currently only backing up the data file once per day?" - Yes, complete backup every 2am.
"then consider setting your recovery model to simple" - Yes, will adopt this process asap.
"Consider moving it to a drive with more space to grow" - Yes, I've already considered this allocation of drive space. my tempdb is not stored in the same directory and/or hard drive. Even my other databases is stored separately from hard drive.
-
By the way using auto shrink is not recommneded you can find many articles on why e.g
http://www.sqlservercentral.com/columnists/mpearson/autocloseandautoshrinkjustdont.asp
To recreate tempdb you will need to restart sql server as it recreated each time the sql server is restarted.
To truncate the transaction log you need to use the backup log command not a differential or full backup.
But as Lynn and John have said you need to first decide on how much data loss is acceptable i.e. last nights backup was good enough or it has to be to within 15 minutes or whatever and depending on this choose the appropriate recovery model i.e SIMPLE or FULL and if it is full take regular enough transaction log backups through the day so that you can meet your recovery requirements
David
-
Thanks!
btw, what if i add new transaction log file from the database? does it help the performance of my database specifically allocation of the transaction logs? or it will give me another maintenance problem?
-
Lynn is giving very good advice about transaction log backups, recovery mode, etc.
You also said you had the databases set to 'auto shrink'. This is bad. You should never shrink a database if you expect it to grow in any way within the next 3 months. Repeated shrinking and growing of databases causes bad Windows file fragmentation which harms performance. This can only be fixed by a Windows disk defrag, a SQL index defrag does not affect this problem. You also get a performance hit when you shrink and grow.
You need to set your database sizes so they it are stable. If tempdb grows to 10 GB when in use, set the initial file size to 10 GB. The tempdb for our Data Warehouse system has an initial size of 100 GB, because once a week we run a proces sthat needs that space.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Hi Francis,
My suggestion is:
1. set your recovery model to simple.
2. take a full backup of your database
3. (optional) Look at why this will solve your problem.
First, the simple recovery model only keeps the minimum amount of logged transactions at any given time, allowing the log to be reused in a "circular" fashion.
Second, the switch to simple recovery model doesn't actually happen until you back up the database.
Third, you should probably look at the BOL and the other answers in this thread so that you understand why this is either a magic bullet for you or not the best solution long term. The transaction log and backups are not so obvious as to be simple to understand.
What I can tell you is that since you are not backing up the log now, changing to simple recovery can only improve you situation.
Then maybe you can take a breather and read up on this. There's plenty of help here at SSC.
Good luck! jg
-
Hi Guys!
I do sincerely appreaciate your replies.
Well after having the changes from my databases, from FULL to SIMPLE, as of now the Transaction Log file size was shrunk to the 1/4 size from the original. Good that I have now more space to play with. However, I understand the trade offs from this configuration but I will still observe the behaviour of the server in terms of performance and reliability of data.
For now, I've eliminated the "Auto Shrink" and I'm running a diffirencial bakup every 12hours and retained my 2am complete backup since the high traffic is between 6am-11pm. So far so good and I'm happy with the results. I hope everything will work fine with less headache.
No regrets! I'm satisfied with SSC and this is definitely a great help for me. I'm getting so much information and idea how will I improve my current database servers. Honestly, my knowledge is limitted from training and 4 years experience from simple/non complex structure of database. Now, my new databases are very complex and the data is very high in volume so what i've learned is i should not be alone in the dark fixing what is need to be fix and of course, i should seek for "veteran" well experienced experts like you.
I still have questions came out upon getting the reactions of concerned experts. I need to know the answers based on the actual experience aside from theoretical explanation.
Until next time.
Francis
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply