avoid ldf grow
-
ldf file is 38gb
mdf file is 98gb
anyone know why the ldf file is so big?
how to prevent it?
how to avoid it?
-
It stores the transaction information records for recovery. You cannot keep it from growing but if you don't have a need to ever need to recover transactions since the last full backup set the recovery model to simple, this will allow the commited portions of the transaction log to be reused. However if you have a large transaction the file may still grow more than you expect. You can also shrink the log once to remove the committed transactions.
-
You can set the max size for the log in the log file properties, but the database will shutdown if that limit is reached. Choosing the proper recovery model as mentioned earlier and setting up shrink jobs that reallocate the space back to the OS is the key.
-
Actually - you can often enough help yourself by getting your log files to a certain size, and backup and truncating the committed transactions, but NOT shrinking the files. Especially if once you get there you can defragment said log file on the disk subsystem. A log file in one single "chunk" on the drive with some wiggle room inside will operate a LOT better than one that is filled to the brink, but split into a bunch of pieces.
Of course - if you happen to be starved for space (as we were for years), the truncate and shrink does help with THAT problem. As the old line from commercials goes "Your experience may vary".
----------------------------------------------------------------------------------
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? -
you can't truncate the log in simple recovery model.
how can i truncate it in simple recovery model?
-
I didn't notice the simply mode. That being said - simple recovery is essentially a "self-truncating" process. It will log transactions for the length of time they are open/un-committed, and will then automatically truncate them out once they are committed.
Keep in mind that truncate (i.e. free up the space in a file) is not the same as a shrink (actually making the file smaller by reducing the log's disk file). If you want to shrink the file, first you truncate (in the case of a full backup); the shrink then rewrites the log file so as to reclaim all of the space inside of the file. Kind of the same thing as putting something in the garage to be put out to the trash versus actually taking it to the curb for pickup.
If the file is too big, then by all means shrink it, but it will grow again from then.
----------------------------------------------------------------------------------
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? -
i think the problem is the developes are using DELETE statement instead of TRUNCATE. Thats why the log file is so big.
-
Careful now - that's a different TRUNCATE. the log files "truncate" <> TRUNCATE TABLE. That's a evil twin
TRUNCATE TABLE <table1> is a T-SQL equivalent to "delete from <table1>", e.g. delete EVERYTHING in table1. TRUNCATE TABLE doesn't allow for a WHERE clause, so it's functionally a "reset and start over" option on a table. You wouldn't want to go around replacing DELETE with TRUNCATE throughout s-procs - that would be disastrous (especially if you get the syntax right, and it allows you to actually run that...)
the logs truncating means the contents of a transaction are deleted.
(you probably knew all of that - just didn't want anything drastic happening due to a miscommunication).
That being said - assuming the situation calls for wiping out everything in a given table - then yes, I'd use TRUNCATE over DELETE from table (for readability). As to whether that is causing your issue - the delete operation might be - but I am not sure how switching between the two syntaxes would make a huge diff.
----------------------------------------------------------------------------------
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? -
I find that more frequent backups of the transaction log will keep the size managable. For example, if you back it up every 4 hours, try every 2 hours instead.
-
how could i avoid the file from grow again.?
-
If you are constantly shrinking your log file, you are not going to keep it from growing. Even in simple recovery mode, SQL Server writes all update, delete, and insert transactions to the log file first, then the database. The constant growing and shrinking of the transaction log is going to have an impact on the system and the application that it supports.
I would recommend you monitor the size of the log file to see if there is a specific size that you could set it to that allows it to grow when necessary, but is large enough to handle the majority of the work without having to grow.
See: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=396164
-
(continuing with Lynn's thought process)...in which case that size you just identified becomes the <target size> in the DBCC SHRINKFILE statement. That way - you're not making the file shrink too much, only to have to grow again....
----------------------------------------------------------------------------------
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? -
Thank you Matt. I must be suffering from distractionitis, as I did not fully complete my thoughts in my earlier post.
-
I do it all of the time
Too bad they don't track number of edits on individual posts - I'd be a "top poster by now....----------------------------------------------------------------------------------
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? -
i setup a job to shrink and truncate the log every 4 hours. I hope that works
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply