Several points needing attention in adding data files under Alwasyon environment

In the middle of the night to receive the alarm text message, the server disk space, crawling up to check, found that because of the index reconstruction, and the disk still has an autogrow data file, due to the other disk characters on the server has the remaining space, it is intended to limit the data file under the disk growth, and add several data files in other free space under the disk, so the following steps:

First step: Check that the same drive letter for AlwaysOn secondary nodes has disk space (you can use exec master.) Xp_fixeddrives to view)

The second step: check whether there is a corresponding folder under each auxiliary node, no manual creation

Step three: Add data files to the database on the master node.

Three steps to finish the manual, by the way to check the other server, ready to go back to dream of the public, SMS and alarm, Alwasyon data delay, but also in the synchronization mode of the auxiliary node delay, suddenly small hands a shiver, almost the computer to fall, immediately troubleshoot the cause, the auxiliary node disk space is sufficient, The corresponding directory has also been created successfully, and a new file of one of the databases has been created on the secondary node, but there is a new database file is not created, but also suspect that the current time index maintenance caused a large number of log backlog resulting in alwasyon delay, so decisively shut down the primary node index maintenance, Continue to use the Alwasyon Control Panel observation, found that the secondary node redo queue size is gradually increasing, it seems to close the index maintenance and no egg, continue to troubleshoot the problem ...

The problem is that the log redo queue is increasing, since the main node "production" of a large number of logs, then the secondary node "consumption" of these log problems, and then troubleshoot the IO pressure, auxiliary node Io is normal, the only cause IO pressure on a full backup job in the execution, the full backup job, This does not seem to conflict with the log redo, but do not forget that the operation is a new data file, the culprit timely full backup. Because the database is large, full backups take up to dozens of minutes to complete, and to ensure that the full backup can back up all data files, SQL Server blocks the addition of files to the database during a full backup, while the redo log on the secondary node is the new file for the database, and the full backup "blocks" Log redo, and give us a log of the "redo" rate to catch up with the log "generate" the illusion of the rate.

Summary: In the Alwasyon scenario, add data files, not only to consider the secondary node disk space and the corresponding file directory problems, you should also check that the secondary node is running a full backup, and after the operation is finished, be sure to check whether the corresponding file is created on the secondary node, and AlwaysOn sync status is normal.

PS: If the secondary node is in asynchronous mode, and there is no corresponding drive letter or file directory on the secondary node, the secondary node redo the "New Files" section of the log will be error, resulting in the corresponding database database "hang", in an unsynchronized state, when the secondary node no longer send "request" to the primary node, There is no point in observing the log send queue size and the log redo queue size.

--================================================================

Work in the middle of the night is not easy, not a sister refreshing how to do

Several points needing attention in adding data files under Alwasyon environment