SQL Server Performance Forum – Threads Archive

Transaction log size

Hi Guys, Our production database is about 46GB. With the data file of 27GB and transaction log file 19.5GB. The transaction log is backed up every hour and the full database is backed up once in 24hrs. But unfortunately the transaction log size never reduces below 19GB. I know you can run the shrinkfile and reduce the transaction log size but with in one day the transaction log size grows back up to 19GB. The file properties is set to Auto grow at 10%. Please do let me know if there is any way getting the size down to 2-3 gbs. Regards,
Ravi

Some large transaction must be coming along and making it grow. Do you run reindexing overnight? This will cause large t-logs to be genereated unless you put the db in BULK_LOGGED or SIMPLE recovery mode while it runs. Or maybe you have some bacth jobs causing large logs to be created. To check this, have a look at the size of the t-log backups, one or more of them must be very large. To monitor its usage you could run a scheduled task to run dbcc sqlperf(logspace) and capture its output on a regular basis. Tom Pullen
DBA, Oxfam GB

To add to Tom, I would set the TLOG at a reasonable size to avoid autogrowth operations between two backups. This autogrowth operations are somewhat expensive and lead to file fragementation, thus can degrade performance. So if this size might be 19 GB in your case, what’s wrong with it? ———————–
–Frank
http://www.insidesql.de
———————–

Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file. For information on shrinking the size of a physical log file, see Shrinking the Transaction Log Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>