I use SQL server database. To reduce the database log file I set the recovery model of the database to simple and then Shrink database but the log file does not get the effect.
What is problem?
4
-
2The better question is, why do you need to shrink the log? Are you not taking frequent log backups (As presumably it was in full recovery previously)?– Thom AJun 9, 2022 at 11:52
-
To add on to what Larnu said, if you switched to Simple Recovery Model, you potentially risk losing more data should an issue occur, since you'll only be able to take Full and Differential backups now, which typically aren't ran as frequently as Transaction Log backups. If you're ok with this, then carry on.– J.D.Jun 9, 2022 at 12:07
-
For one-time runaway log resizing, consider using MS Tiger Team's VLF solution.– vonPryzJun 9, 2022 at 12:35
-
2Related: dba.stackexchange.com/questions/29829/…– Paul White ♦Jun 9, 2022 at 14:26
Add a comment
|
2 Answers
Don't shrink the whole database. It is too imprecise. Always target the individual file. I.e., use DBCC SHRINKFILE.
The reason that the log file doesn't shrink is that the last Virtual Log File is in use. The ldf file is divided into Virtual Log Files (VLF), and the ldf file can only be shrink from the end of the file, removing un-used VLFs. Apparently you have used VLFs at the end of the file, else the file would have shrunk.
What you do is to empty the log (the CHECKPOINT command if in simple recovery or do a log backup if in full recovery) and then execute the DBCC SHRINKFILE command. You might have to repeat these steps several times until the last desired VLFs has gotten into the unused state and can be removed.
Here's an article I wrote, with some more details to it. And this is another about why it is a bad idea to shrink files, in general.
answered Jun 9, 2022 at 14:01
-
Thank you very much for this blog: karaszi.com/large-transaction-log-file It is actually the first piece I read that tries to answer the question without falling into the trap of discussing all-things-related like so many other do. Thumbs up! Sep 20 at 8:43
A transaction log is used to store delta values while the transaction is used to save the previous values before performing the data modifications (INSERT, UPDATE, DELETE...) in case of thses modifications are canceled. The different transactions generate many lines in this file, sequentially, and the different user transactions are interleaved.
Attempting to reduce the size of a transaction log is generally not a good idea and may fail especially if:
- there is a still alive transaction (not terminated by COMMIT or ROLLBACK) started a long time ago
- log size is already minimized
- the log is heavily loaded with many transactions ...
Generaly, reducing the size of a transaction log is an operation that should only be undertaken in exceptional circumstances, as an example in the case of its size becomes really gigantic. A transaction log having a size of the order of 10 to 20% of the overall size of the database is considered normal.
Indeed, the fact of reducing considerably and too low, the size of the transaction file will inevitably force it to grow, this growth being an operation detrimental to performance...
