I'm looking for recommendations on how should I set up transaction logs for databases that are used by a development team. These databases are ephemeral in the sense that we never care about data recovery in case of hardware/software failure. Instead, every time a developer starts a task, they will create a new database and fill it with data from scratch anyway, so they'd do so as well in case of hardware failure. Another use case is automated testing, where a new database is created for each test run.
Right now, due to the usage patterns of developers (testing different types of queries, frequent data bulk-loading), the logs grow a lot and are more of a hindrance than help. We've seen cases where the logs started taking half a terabyte after only an hour of the developer's work, forcing them to manually truncate the logs. As we don't want to manually truncate the logs during automated tests, we need to allocate larger machines for them. I suspect that more I/O is required, slowing down operation.
Any recommendations I could find in both the SQL Server documentation and other materials are for production server and focus on data recovery, which is exactly the opposite of what I'm looking for.
What are some good practices regarding configuration of transaction logs of SQL Server when data recovery is irrelevant, instead ease of operation, resource usage and raw speed are a bigger concern?
Best Answer
Simple recovery model doesn't mean that the transaction log isn't used.
It just means that SQL Server can empty the log (aka "truncate" the log), instead of having to do it yourself scheduling regular transaction log backups. I.e., the log need space for the largest/earliest open transaction, since the log cannot be truncated past the oldest transaction - regardless recovery model.
Simple does mean, however, that some operations can be performed in minimally logged form. Such include create/alter/drop index, SELECT INTO, bulk loading of data and if the stars are properly aligned also INSERT ... SELECT.
So, if simple doesn't cut it for you regarding space usage, then you'd have to look into how the operations are done - i.e., educate the developers about minimal logging as I mention above.
As for speed, minimally logging will help there as well, since you have less I/O. Larger transactions (within some reason) means less synchronous I/O to the LDF file. From a pure config aspect, I guess some RAM disk option for the ldf file could help - but that might not be doable if you need large ldf files.