SQL Noob (11/9/2009)
interestingi have two databases that are replicated and generate tens of millions of commands a few times a month. Most of the db is deleted and new data inserted continuously. One seems to replicate fine and the other is always backed up during the big batch processes. I just checked and the one that's OK is Full Recovery Model and the problem DB is Simple. We set it up like this 18 months ago.
SQL Noob,
I'm curious to know if on the DB that's using SIMPLE model has a transaction log the same size of the DB that uses the FULL model?
Also, what's the longest running transaction time on the SIMPLE model DB?
If you can get the SIMPLE model DB to stay under 70% capacity for the transaction log (maybe make the log file double the size of what it needs to be to fit the "total" workload between backups?) and run the workload within the recovery time interval (you can manually increase this if you experience "long running queries"), you could see some improvement in performance. It's just theory, as I don't know all the variables of your setup for these systems and SQL instances.
Hope this helps,
James
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/