SQL Server 2016 Enterprise 3-Node WSFC Configuration Generating Excessive Logs and Filling Storage

Question

I am currently facing an issue with the configuration of a three-node Windows Server Failover Cluster (WSFC) for Microsoft SQL Server 2016 Enterprise Edition. I have a Data Center (DC) and a Disaster Recovery (DR) location, both with layer 3 network connectivity using different subnets and layer 2 connectivity with the same subnet. The infrastructure is hosted on VMware instances. During a previous attempt to set up this environment, I encountered a challenge where the configuration process automatically generated logs, leading to an unexpected filling of storage. To provide more details: Topology: DC: 2 nodes DR: 1 node

Layer 3 connectivity with different subnets Layer 2 connectivity with the same subnet

Issue: Excessive log generation during the configuration process. Resulted in unexpected storage consumption.

 **Environment:**
   VMware instances in both DC and DR locations.

I am seeking guidance and solutions to address this issue and optimize the configuration to prevent the automatic generation of excessive logs that impact storage. Any insights, best practices, or recommendations on how to configure a three-node WSFC for SQL Server 2016 Enterprise in a multi-subnet or same subnet environment with VMware instances would be greatly appreciated.

Answer 1

Hello If you are talking about user database transaction logs (the .ldf file for each your database) most probably you have the database recovery model configured as FULL so all user transaction a logged, for this reason the transaction log file grows. in order to maintain that grow under control you must have the proper backup strategy in place (example: 1 daily full backup + transaction log backups every 5 minutes) you can follow below link to get a detailed explanation as well as how to do the backups If above is the case, actually doesn't matter if is a 3-node cluster or a standalone server Regards Javier

Answer 2

Yes, understanding what storage you are speaking of would help. There are many logs in this case. VMWare logs SQL Server Logs Cluster Logs Event Viewer (Windows Logs) Having set up many clusters, I cannot imagine that SQL Server would generate enough log to fill a disk and the Cluster Logs are compressed'ish and should not fill up a drive. So if there is enough going on in VMWare or Windows that could make that happen, then yes there would be a problem. Knowing which logs or drives that are filling up would give us a little better clue.