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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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:
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...