Leo (10/19/2008)
Hi Gila,Sorry, I get back to you a bit late. I am not fully understanding about Transaction Log. Let say I switched my DB Model to 'SIMPLE'. I don't need to worry about transaction log anymore as you said. But what do I do if transaction log is getting bigger, for example -
Transaction log initial size 4GB [that is okay and I am happy with this size], During the day or when I check the database at night, Transaction Log is about 40GB . What do I do?
I know, you will hate me but I really want to understand this which mean I want to learn from your experience. Last question.......
Insert or Reading the record from Database is very slow sometime, is that anything to do with transaction log? How to solve it?
Thanks again.
On your first question, if your database is in SIMPLE recovery model you don't have to worry about BACKING UP the transaction log. The transaction log is still required and will grow to the size needed to manage the largest transaction that will process on your system.
If your transaction log is growing to 40GB (in SIMPLE model), then your transaction log needs to be 40GB to handle the workload. If that is not acceptable, then you need to identify the process(es) that are using that much space in the transaction log and fix those processes.
One example would be an ETL process that is loading millions of rows of data in a single transaction. A process like this could easily cause the transaction log to grow that large.
Second question - I would not suspect the transaction log has anything to do with your performance issues. The only way the transaction log would really affect your performance is if the file is on the same disks as the data files and you are seeing I/O contention.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs