6

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?

6
  • 1
    "resource usage and raw speed are a bigger concern?" ALTER DATABASE ... SET DELAYED_DURABILITY = FORCED
    – Igor
    Commented Feb 25, 2021 at 12:49
  • 1
    @Igor, good idea! Can you add it as a proper answer? Comments are… ephemeral on this site ;-)
    – liori
    Commented Feb 25, 2021 at 12:57
  • 2
    "second trouble: size transaction log" This complex question. Example answer (Top reason 2/2: Long Running Transactions): dba.stackexchange.com/questions/29829/…
    – Igor
    Commented Feb 25, 2021 at 13:39
  • 1
    Another example decrease in growth TLOG (Break large delete operations into chunks): sqlperformance.com/2013/03/io-subsystem/chunk-deletes P.S. this complex question :-) (Takeaway: When performing long-running modifications, I’m sure many of you enjoy using batches to increase concurrency. But I want to talk about a pitfall to be aware of. If you’re not careful, the method you use to implement batching can actually worsen concurrency.) michaeljswart.com/2014/09/take-care-when-scripting-batches
    – Igor
    Commented Feb 25, 2021 at 14:04
  • 1
    BTW i can understand to reduce resource usage and speed up development databases, but keep in mind this encourages inefficient write patterns. So there should be performance characterizations being done on properly logged databases early in the development cycle.
    – eckes
    Commented Feb 28, 2021 at 10:30

3 Answers 3

6

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.

5

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.

3
  • 2
    SQL SERVER – Delayed Durability, or The Story of How to Speed Up Autotests From 11 to 2.5 Minutes blog.sqlauthority.com/2016/07/20/…
    – Igor
    Commented Feb 25, 2021 at 14:37
  • 1
    OP is using Delayed Durability for ephemeral data. For those using Delayed Durability for regeneratable data, be aware that the data may need to be regenerated after a clean shut-downs, not merely in response to crashes.
    – Brian
    Commented Feb 25, 2021 at 23:00
  • Docs reference for what @Brian points out, which is frankly amazing to me - you'd think they would make calling sp_flush_log part of a clean shutdown, right?!
    – AakashM
    Commented Mar 5, 2021 at 10:38
4

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.

12
  • Hmmm… I believe we're already using this setting, yet the transaction logs grow?
    – liori
    Commented Feb 25, 2021 at 12:14
  • @liori With any of the settings the Transaction Log will have to be used, even in a minimal capacity, but please see my updated answer with what else you can try.
    – J.D.
    Commented Feb 25, 2021 at 12:21
  • @liori, the log still needs to be sized to accommodate the largest transaction and not yet replicated data. It may grow as a result but will eventually be truncated,
    – Dan Guzman
    Commented Feb 25, 2021 at 12:22
  • Ok. So I still need to somehow decide on the transaction log size for each database? I hoped there is some setting that would shrink it automatically as soon as possible. I've had scenarios where the logs started taking half a terabyte after an hour, and the developer had to manually shrink it every hour or so.
    – liori
    Commented Feb 25, 2021 at 12:29
  • 1
    Thank you! It's clear now!
    – liori
    Commented Feb 25, 2021 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.