REDO_THREAD_PENDING_WORK Wait Type In SQL Server

REDO_THREAD_PENDING_WORK is an AlwaysOn High Availability Wait Type which is without a HADR_ prefix. In most cases, this wait type can be ignored as they are not very important to be noticed. But it is good for all the DBAs to immediately identify the wait types being generated in the SQL Server Instance.

What is REDO_THREAD_PENDING_WORK?

Inside an AlwaysOn Availability Group, the REDO_THREAD_PENDING_WORK wait type occurs on the secondary replica. Further, a wait type accumulates over a course of time in the server when there is no work to be carried out. As we all are much aware that an AlwaysOn Availability Group processes log records, hardens them on transaction logs, and then confirms the same to the primary replica. While using the synchronous mode, the primary replica waits to commit on the client until a transaction commits in the secondary replica. Whereas, in the asynchronous mode, the commit message is sent to the client without waiting for hardening on the secondary replica.

Until now, we have seen the internals of an AlwaysOn High Availability for a synchronous and asynchronous mode. The REDO_THREAD_PENDING_WORK wait type comes to the picture when there are any modifications inside the secondary replica log records. The REDO thread does not impact the commit confirmation from the secondary replica. Furthermore, this states that the secondary replica transactions may not the exact same as the primary replica even after the transactions are committed on both sides.

At this point, you might think that the information could be lost during a failure. However, you must note a point that all the information will be recorded in the secondary replica logs. The Secondary replica will have all the information it requires to perform in the secondary replica log files. In case of a failure, the SQL Server will either redo or undo the internal processes from the transaction log.

How to check the REDO_THREAD_PENDING_WORK wait type in Secondary Replica?

The wait type occurs in secondary replica because of accumulated wait time naturally. This can be monitored using a DMV called sys.dm_os_wait_stats. This DMV can be used to fetch the waiting_tasks_count, wait_time, max_wait_time, and signal_wait_time. All the durations mentioned from the DMV will be in milliseconds.

select * from sys.dm_os_wait_stats where wait_type='REDO_THREAD_PENDING_WORK'

Note: In most cases, this wait type can be ignored. 

Restore The Master Database In SQL Server

There are multiple reasons you may want the master database to be restored. While a DBA understands clearly the backup strategies, the backup of the master database is very critical. In most cases, restoring the master database will be a rare scenario. However, including all the system databases in our regular backup strategy is a must.

Restoring a master database is not a straight method like any other normal backup restore we perform. As the name suggests, the master database bags all the information about the server including databases, database files, and locations, configuration settings, login information, and more.

Being said that the master database in most cases is rare, it is always recommended for the DBAs to keep the restore process handy. Any of our databases at any moment can fall and is the best option to make a practice of disaster recovery.

Let’s look at the steps to restore the master database.

1. Start SQL Server Instance In Single Mode

Follow the path for starting SQL Server Instance in Single User Mode.

SQL Server Configuration Manager --> Click on SQL Server Services --> Click on Startup Parameter Option --> Type “-m” in the specified field --> Click on Add --> OK

By doing this, the SQL Server Instance will be set to Single User Mode. Now, restart the SQL Server Services to get the effects in place.

2. Open Command Prompt To Restore Master Database

Open the command prompt and type >sqlcmd. This will navigate to enter the SQL commands. 

 RESTORE DATABASE master FROM DISK = 'C:\BackupRestore\master.bak' WITH REPLACE;

3. Remove “-m” Startup Parameter From SQL Server Configuration Manager

Sometimes we may be in a hurry to fix it and may forget to revert the startup parameter. Once the master database is restored, the SQL Server shuts down and terminates the sqlcmd process. Once the restart has been finished, before you open the SQL Server Instance, revert to multi-user mode by removing the startup parameter. 

Please refer to the MSDN HERE for more details.