Recommended SQL Server transaction logs settings for ephemeral databases?

Question

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?

Answer 1

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.

Answer 2

Question: "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?"

IMHO:

ALTER DATABASE ... SET DELAYED_DURABILITY = FORCED

Control Transaction Durability

How to control transaction durability

Database level control

You, the DBA, can control whether users can use delayed transaction durability on a database with the following statement. You must set the delayed durability setting with ALTER DATABASE.

ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }  

DISABLED [default] With this setting, all transactions that commit on the database are fully durable, regardless of the commit level setting (DELAYED_DURABILITY=[ON | OFF]). There is no need for stored procedure change and recompilation. This allows you to ensure that no data is ever put at risk by delayed durability.

ALLOWED With this setting, each transaction's durability is determined at the transaction level - DELAYED_DURABILITY = { OFF | ON }. See Atomic block level control - Natively Compiled Stored Procedures and COMMIT level control -Transact-SQL for more information.

FORCED With this setting, every transaction that commits on the database is delayed durable. Whether the transaction specifies fully durable (DELAYED_DURABILITY = OFF) or makes no specification, the transaction is delayed durable. This setting is useful when delayed transaction durability is useful for a database and you do not want to change any application code.

Answer 3

Sounds like a good candidate for the Simple Recovery Model.

Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.

Here's a good article that discusses the uses of the different recovery models and then discusses doing shrink operations. Generally I don't recommend shrinking in a SQL Server instance, but in your scenario where disk space is crucial and database usage is temporary and perhaps various since it sounds like you do a lot of testing, then maybe this is a use case that is acceptable for running a shrink operation.

Traditionally, shrinking is recommended against because it's a heavy operation that's generally a waste to run since the Transaction Log is just going to re-grow to the size it needs anyway (and growth operations are also not so light). But in your case where it sounds like you scale up new databases regularly and do various testing, it may make sense to shrink during a maintenance window, to reclaim some of that disk space.

Additionally you can look into enabling auto-shrink on your databases, but again this highly recommended against because it can affect performance throughout the day while your developers are working. In my opinion, if you're going to shrink, it should be a controlled and planned operation that's done during a maintenance window that's minimally disruptive to your developers. Here's another article that goes through how auto-shrink works. And last but not least, here's a good Brent Ozar article on the drawbacks of auto-shrinking.