Resolving Disconnected Secondary Replica or Database in Always On Availability Groups

It’s not uncommon to encounter situations where either the secondary database or the secondary replica enters a disconnected state in an Always On Availability Group (AG). This typically occurs during initial AG configurations or after events like OS/SQL patching, network issues, or failovers.

Recently, I faced a scenario where not only was the secondary database in a disconnected state, but the entire secondary replica was also marked as disconnected in the AG dashboard. As a result, the transaction log (T-log) started growing due to unsynchronized data movement.

Below are the steps I followed to troubleshoot and resolve the issue:

Steps to Resolve:

1. Connected to both Primary and Secondary replicas via SSMS.

2. On the Secondary node, navigated to:
   Always On High Availability > Availability Groups (AVG) > [Your AG Name]

3. Under Availability Databases, right-clicked the affected database, then:
   Suspend Data Movement > Resume Data Movement

   Note: This did not resolve the issue.

4. Restarted the HADR Endpoint on both replicas:

   
   SELECT * FROM sys.endpoints;
   
   ALTER ENDPOINT [Hadr_endpoint] STATE = STOPPED;
   ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
   

   This also did not resolve the issue initially.

5. Granted CONNECT permissions to the SQL Server service account (used by the secondary replica) on both nodes:

   
   GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\SQLServiceAccount];
   

6. After applying the endpoint permission, I re-executed Step 4 (restarting endpoints).
   This time, the secondary replica successfully reconnected, and data synchronization resumed.

SQL server DB status runnable, sleeping, suspended, running, pending and background meanings

Some of the most seen status of SPID's in SQL Server and what do they mean:

RUNNING:

This status means session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. What this actually means is, the client connected to SQL Server using this session has already submitted a query for SQL Server to process and SQL Server is currently processing the query. The query could be anywhere between generating a parser tree to performing a join to sorting the data... and it is consuming the CPU (Processor) cycles currently.

SUSPENDED:

It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAIT it can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed. For example, if the query the has posted a I/O request to read data of a complete table tblStudents then this task will be suspended till the I/O is complete. Once I/O is completed (Data for table tblStudents is available in the memory), query will move into RUNNABLE queue.

RUNNABLE:

The SPID is in the runnable queue of a scheduler and waiting for a quantum to run on the scheduler. This means that requests got a worker thread assigned but they are not getting CPU time.

The RUNNABLE queue can be likened to a grocery analogy where there are multiple check out lines. The register clerk is the CPU. There is just one customer checking out e.g. “RUNNING” at any given register. The time spent in the checkout line represents CPU pressure. So this SPID is waiting for that customer who is running (with register clerk) to get out so that it can start RUNNING. You can use the query SELECT wait_type,waiting_tasks_count,signal_wait_time_ms FROM sys.dm_os_wait_stats ORDER BY signal_wait_time_ms DESC to find out the difference between the time the waiting thread was signaled and when it started running. This difference is the time spent in RUNNABLE queue. Some of the waits on the top of the list can be safely ignored.

PENDING:

The request is waiting for a worker to pick it up. This means the request is ready to run but there are no worker threads available to execute the requests in CPU. This doesn't mean that you have to increase 'Max. Worker threads", you have to check what the currently executing threads are doing and why they are not yielding back. I personally have seen more SPID's with status PENDING on issues which ended up in "Non-yielding Scheduler" and "Scheduler deadlock".

BACKGROUND:

The request is a background thread such as Resource Monitor or Deadlock Monitor.

SLEEPING:

There is no work to be done.

Database in Suspect Mode

Yesterday I had mail to check the database health after the recovery of servers from Storage crash issue. I found one particular database was in 'Suspect' mode. Performed steps to fix this. 

Database will go to Suspect mode usually if there is any issue with Server Reboot, Storage issue, Database Corruption due to Network issue etc. In this case Storage SAN drive crashed, restored the storage from the Snapshot. 

1. Check the database state

select state_desc, * from sys.databases where state_desc<>'online'

2. Found database was in Suspect Mode. 

First step is to Restore the database from the latest Backup file. and Run the DBCC Integrity command to check it. Made sure everything fine on that.          

As a part of troubleshoot on the issue database.

               

            Brought the database to Emergency Mode, and do the DBCC Integrity check.

Use Master

go

ALTER DATABASE DBNAME SET EMERGENCY

DBCC CHECKDB (DBNAME )

3. Based on the DBSS Result\suggestion performed the DBCC REPAIR_ALLOW_DATA_LOSS.  Depending on the DBCC Results and suggestions 

Use Master

go

ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB ('DBNAME', REPAIR_ALLOW_DATA_LOSS)

            go

4. I was not able to access the database into single user mode, transaction was ending in the Deadlock. I changed the Deadlock priority and later was able to take the database into Single user mode.

SET DEADLOCK_PRIORITY HIGH 

GO

ALTER DATABASE DBNAME SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

or 

SET DEADLOCK_PRIORITY 10

GO

ALTER DATABASE DBNAME SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKDB ('DBNAME', REPAIR_ALLOW_DATA_LOSS)

            go

5. Then performed the the DBCC REPAIR_ALLOW_DATA_LOSS. Database went to Recovery mode. DBCC Repair Allow data Loss should have fixed the issue by allowing some data loss. But it didn't fix the issue. Hence continued using the Restore database. Later I just dropped the database and renamed the Restored database to original name. 

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'DBNAME'

GO

USE [master]

GO

/****** Object:  Database [DBNAME]    Script Date: 3/27/2019 12:29:38 PM ******/

DROP DATABASE [DBNAME]

GO

Unable to Kill SPID

                  

                       Recently one user reported he is facing some issue while deleting the table records. When I checked it there was a blocking. Immediately I tried to kill the session which was causing the issue. I used  KILL spid to kill the session. Though it was executed successfully, session was still running with 'suspended' status by holding a blocking on sessions. 

In order to kill the session, I alter the database to Single User mode. 

USE MASTER 

GO

ALTER DATABASE (DBNAME) SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

ALTER DATABASE (DBNAME) SET MULTI_USER WITH ROLLBACK IMMEDIATE 

GO

This resolved the issue by killing all the sessions. But it was on a NonProduction database. When doing it on Production database please be aware of affected users and the work to be redone. 

Shrink TempDB database

Its very common issue on day to day DBA activities, where TempDB databases will go out of space. Sometime it is cumbersome to shrink the database files to release the space. 

Below are some on the Methods to release the space on TempDB database. 

1. Shrink Database files 

use tempdb

GO

CHECKPOINT

GO

dbcc shrinkfile ('tempdev') -- shrink db file

dbcc shrinkfile ('templog') -- shrink log file

2. Clean up the Cache to and shrink the database files. 

DBCC FREEPROCCACHE -- clean cache

DBCC DROPCLEANBUFFERS -- clean buffers

DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache
DBCC SHRINKDATABASE(tempdb, 10); -- shrink tempdb

After this again Run the shrink commands

dbcc shrinkfile ('tempdev') -- shrink db file
dbcc shrinkfile ('templog') -- shrink log file
GO

3. Restart the SQL service
This is should be a last option. 

SQL SERVER CLR Error

Error:

Msg 6263, Level 16, State 1, Line 721

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Solution ; We need to enable the CLR in the SQL Server configuration.

sp_configure 'show advanced options',1

go

sp_configure 'clr enabled',1

go

reconfigure

go

Error:

Msg 10314, Level 16, State 11, Line 2
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

Solution 1 : When the databases is restored\attached from the different instance this error might occurred as the CLR objects will be looking for the same owner SID which was created at the source. Changing the databases owner to the same as source and giving the required permission would fix the issue.

Alternatively we can use db owner as ‘sa’ also.

USE <DatabaseName>

GO

EXEC sp_changedbowner 'sa'

Solution 2 : The quickfix would be to set the database to trustworthy: 

ALTER DATABASE <DatabaseName> SET TRUSTWORTHY ON

Solution 3 : You can also navigate to Assemblies of a respective databases and change the Assembly property.  Set the permission to ‘External Access or unrestricted’

Error 945 : Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details

This is one of the common issue which I have seen. Today I received a ticker from QA team saying one of the database is not accessible. I checked with the team on what exactly they are doing on the database. I came to know the team was processing the data which increased the many databases sizes which lead to Disk space issue. 

Due to there was no free space on the disk for the database to grow further, one of the database went to shutdown mode. You can check the database status from below query. 

use master
select databaseproperty('DBName', 'isShutdown')

Fix/Solution/WorkAround:

1. Took the database offline and brought back online. Query for the same 

use master
alter database DBName set offline


use master
alter database DBName set online

2.  If possible add more hard drive space either by removing of unnecessary files from hard drive or add new hard drive with larger size.

3.  Check if the database is set to Autogrow on.

4. Check if the account which is trying to access the database has enough permission to perform operation.

5. Make sure that .mdf and .ldf file are not marked as read only on operating system file system level.