Sql-server – How to force SQL Serve *not* to maintain a log file

sql serversql-server-2012

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!

Catt11.

Best Answer

There is no way to prevent SQL Server from writing to the transaction log.

In the simple recovery mode, as soon as a transaction finishes its log records are marked so that they can be overwritten but the transaction is still written to the log.

This means that a large transaction can still expand the log, if the current size of the log is too small.

You need to make sure that the drive your log is on has enough adequate free space for the log to expand, or (and this is what I recommend) expand your log file manually to a size that will avoid auto-growth. Do not regularly shrink your transaction log.

The best way to get the correct size for your transaction log is to simulate production traffic on a development server. Run queries (that would be executed against your production database) against your development database for a set period of time, and monitor the log.