HOTSPOT
You have a data warehouse that is hosted in a SQL Server instance. The data in the data warehouse is
loaded by running bcp.exe You discover that the transaction logs regularly fill up the local hard disk on
the server and that a WRITELOG wait is present. You need to reduce the amount of disk space used to
store the transaction logs and to remove the WRI7TLOG wait.
Which two database property settings should you modify? To answer, select the appropriate settings in
the answer area.
Answer: 
I think the answer is:
RECOVERY
ALLOW_SNAPSHOT_ISOLATION
https://technet.microsoft.com/en-us/library/ms190249(v=sql.105).aspx
0
0
Delayed transaction durability look good as well
Delayed transaction durability is accomplished using asynchronous log writes to disk. Transaction log records are kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place. Delayed transaction durability reduces both latency and contention within the system.
0
0
Recovery (although it isn’t shown). Probably the recovery model is full.
Delayed Durability: https://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014
0
0
Yes, I remember: Recovery was on full.
0
0
https://www.sqlskills.com/help/waits/writelog/
There are many things you can do to reduce WRITELOG waits and wait times, including:
*Reduce the amount of transaction log being generated*
Reduce how often log flushes are occurring
Reduce/remove log flushing overheard from synchronous HA technologies
With availability groups, the remote log copy incurs a HADR_SYNC_COMMIT wait
Upgrade to 2012 or higher to raise the max-outstanding-log-writes from 32 to 112
Put the transaction log on the fastest portion of the I/O subsystem
*Consider implementing delayed durability (in 2014+) or in-memory OLTP/Hekaton*
Looks like Recovery model && Delayed durability must be correct options on that Options page.
0
0
The only truly non-logged operation, which supports features like SNAPSHOT ISOLATION and online index operations. These can be non-logged because there’s never a need to roll back a version store operation or run crash-recovery on the tempdb database.
There are many things you can do to reduce WRITELOG waits and wait times, including:Consider implementing delayed durability (in 2014+) or in-memory OLTP/Hekaton
SO ANSWER IS – ALLOW SNAPSHOT ISOLATION – TRUE
DELAYED DURABILITY – ALLOWED
0
0