Recommended SQL Server transaction logs settings for ephemeral databases?
6
votes
3
answers
549
views
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?
Asked by liori
(289 rep)
Feb 25, 2021, 12:10 PM
Last activity: Feb 25, 2021, 01:05 PM
Last activity: Feb 25, 2021, 01:05 PM