Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pearsont74
Starting Member
27 Posts |
 Posted - 2007-02-15 : 09:44:13
|
| ok....We have serveral db in sql 2000 (all teh patches and sp.A few of the db are fairly large (over 5 gigs) The problem i am having is the transaction log. If I set a limit on tis then we have problems with imports and backups. For example if i set the max size on the log to 1ggi then it wil hit that and will not allow me to do anything like backup and imports....ect.I have read some on simple recovery mode which i assume lessen the writing to the log right??Is the fact that the transaction log will always be the size of the db is something we just have to live with?what is in the log??any other suggestions?? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-15 : 12:18:28
|
| "I have read some on simple recovery mode which i assume lessen the writing to the log right??"Not really. The Log will still need to store your biggest transaction (which may be the Data Import), but it will then "clear", so [in simple terms] it will only be the size of your one biggest transaction."Is the fact that the transaction log will always be the size of the db is something we just have to live with?"Yes. I reckon on LDF file begin 120% of the MDF file is "reasonable".You can increase the frequency of your Tlog backups. I think every 10 minutes is a good compromise. So then your TLog file will be the size of the "Largest group of transactions that can occur in 10 minutes".You could change your Data Import procedure to force a Tlog backup after each large bulk import step.Note that if you change to Simple recovery model you will only be able to recover to your last Full backup (or your last Full + Differential). So you will lose the ability to recover to point-in-time.Kristen |
 |
|
|
|
|
|
Transactions log woahs