We have a SQL 2008 database which is in full recovery mode. Regularly (at least once a week) the log grows to several times the database size. We have nightly full backups scheduled set to truncate the log file.
Out of need we have had to change the DB to simple recovery mode and then shrink the log file. I believe this makes us vulnerable to only being able to perform a restore to the last full backup. Is this correct?
I understand the log file must be expanding in response to one or more queries causing it. How can I determine what queries are causing this in an effort to have them optimized and/or removed?