I just checked log_reuse_wait_desc and it shows 'AVAILABILITY_REPLICA'. I know now what's causing this.

Thanks for the help.

Hi,

if you got this error next time, just take a look at this query:

SELECT
    name AS DatabaseName,
    log_reuse_wait_desc AS LogBlocking,
...

Sorry, found a solution. We have a DAG and someone was working on DR server so DBs is out of sync and that's what causing the issue.

Sue_H, screenshots which I shared last week were from the Extended events session for the Always on health.

I believe we have 4 servers (Node1, Node2, node3 and node4) in one subnet.  All physical servers. Environment details.

Node1 act as a primary for 1st AG. Node 2, Node 3...

• This reply was modified 4 years, 10 months ago by  LearningDBA.
• This reply was modified 4 years, 10 months ago by  LearningDBA.
• This reply was modified 4 years, 10 months ago by  LearningDBA.

This is the patch level we are on.

Microsoft SQL Server 2016 (SP2-CU10) (KB4524334) - 13.0.5492.2 (X64) 

TDE.

I tried this method and it worked. I turned off the encryption, dropped the encryption on the database, took the backup, restore the database on the secondary with norecovery, join...

Replacing the file worked, however; I am not able to join the DBs into AG.

I took the full backup and then the log backup of the database on a primary...

Replacing a file worked. Thanks!

• This reply was modified 4 years, 10 months ago by  LearningDBA.
• This reply was modified 4 years, 10 months ago by  LearningDBA.
• This reply was modified 4 years, 10 months ago by  LearningDBA.

Just restarted the SQL Server service and it helped.

I mean I just didn't run just DBCC shrinkfile. This is the complete command I ran for all 8 files but still no luck.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev'...

I have tries using couple of different methods.

1. DBCC SHRINKFILE
2. DBCC FREEPROCCACHE
3. There are 8 files in total.

Try this

 IF (OBJECT_ID('tempdb..#DatabaseSize')) IS NOT NULL DROP TABLE #DatabaseSize

 CREATE TABLE #DatabaseSize ( 
DatabaseName VARCHAR(255),
FileLogicalName VARCHAR(255),
FilePhysicalName VARCHAR(1000),
AllocatedGB NUMERIC(12,2),
UsedGB NUMERIC(12,2))

   
  DECLARE @ServerName VARCHAR(255)
DECLARE...

• This reply was modified 4 years, 11 months ago by  LearningDBA.

I see what you are saying but the problem is not about removing the database. Environment details to help you better understand.

We have AG1 (DAG) which acts as a Primary

We...

• This reply was modified 4 years, 11 months ago by  LearningDBA.