I have a situation that I need some advice to overcome.
I have a SQL Server 2012 database that has been set to
Simple recovery model. At times, my SQL Server transaction log file grows out of control and tries to consume all available disk space. It shows as empty in SQL Server, but continues to consume disk space (I understand why). I would like to know if there is a way to prevent SQL Server from maintaining the log file at all.
The application that lives in front of this database doesn't play well with the idea of recovering using a trans log backup. Our backup strategy is one of Full and Diff backups executed on a regular enough schedule that we are adequate covered. We don't need or want point in time recovery (hence simple recovery).
To test, I had the log file capped at 10mb, and 10% auto-growth turned on. My SQL Server load was working away at about 3.8mb trans file, when I initiated a full DB re-index (to try to approximate the load that our customers may put on the database). The log file went straight to 10mb+, and all transactions halted with a '9002' error.
I believe I understand the goal of the trans log in normal situations, but in my case, it is just taking up space and getting in the way. Is there a way to prevent SQL Server from using it?
Thank you in advance for your insight!