SQL Server Performance Forum – Threads Archive
Database log is growing too fast too bigHello,
yesturdaty I truncated Log of our production database, because it growed to 12 GB, and from yesturday till now it growed to 6 GB, can anyone tell me why, is there any new virus regarding databases?
I checked log with a trail version of LogNavigator and a saw that nothing strange hapend on the database.
Have you checked FILEGROWTH option for your log file?
Check whether any reindex jobs were executed last night and check what kind of activity was on the database as compared to the previous truncate. Satya SKJ
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
I checked the filegrowth and it is set to UnrestrictedFilegrowth, but I don’t see any reason to rectrict it.
No, I wanted to know what is increment size. It could be set to 5000 MB or 100% or something like that. If you sucessfully shrinked your tx log, new transaction could cause log file grouth by specified amount in megabytes or by specified percent. If this setting is not extremelly high then you have to find out which activity after log truncation produced so much log (as Satya suggested).
The filegrowh increment is 10%, I don’t have maintenance plan which would recreate indexes by itself,I recreate indexes manualy. Lately I did not do any recreation of indexes or any fragmentation.
Try to find out was there any exceptional activity from the moment you truncated the log (scheduled or unscheduled dts load, bulk insert or something of that kind). How often do you backup your database? What is db recovery model?
I checked, no dts load, no bulk inserts, only inserts of users that work every day.
I do Fridays Full Database backups, and every day differential backups after working ours
Your problems is similar to:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6338 If normal activity produces so much log you can try either: – to use simple recovery model if you can have full backups daily and loosing one day data is acceptable; or
– You can schedule transaction log backups to be frequent enough to keep your log small enough. Transaction log backup (and full backup too) marks completed transactions inactive, so this space can be reused.
Run DBCC SHRINKFILE immediately after the log truncation? Truncating the log only shrinks the logical log size not the physical size. Refer to BOL DBCC SHRINKFILE Raulie