Cannot shrink ‘read only’ database | Shrink Transaction Log While Using AlwaysOn Availability Group

I got ” Cannot shrink ‘read only’ database ” error during Shrink Transaction Log While Using AlwaysOn Availability Group.

 

Shrink Transaction Log While Using AlwaysOn Availability Group

Transaction log of SQL Server size was very big, I need to shrink it to decrease its size. But My database is using AlwaysOn Availability Group, so I couldn’t shrink it because of following error.

 

I have used the following SQL Script, you can Shrink the SQL Server MDF, NDF or LDF using the following script or SQL Server Management Studio tool.

 

USE [MSDB]
DECLARE @i INT;
set @i=199960
print @i
while(@i>199900)
begin
DBCC SHRINKFILE (N'MSDB_log.trn' ,@i)
set @i=@i-100
print @i
end

 

TITLE: Microsoft SQL Server Management Studio
------------------------------

Shrink failed for LogFile 'KAS_CLIENT_log'. (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18206.0+((SSMS_Rel).191029-2112)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+LogFile&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot shrink 'read only' database 'MSDB'. (Microsoft SQL Server, Error: 7992)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5026&EvtSrc=MSSQLServer&EvtID=7992&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

 

 

Cannot shrink ‘read only’ database

This error is related with the Read only database, to solve this error, you can failover the Instance and you can make the read only database to Primary using the following post.

 

SQL Server AlwaysOn ( Availability Group ) Architecture and Step by Step Installation -3 Manual Fail Over Steps

See more

 

When you perform the Always On Failover, you can Shrink the Transaction logfile because now this database has been Primary database and read write mode, so you can shrink it easily.

 

 

Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.

SQL Server ( MSSQL DBA ) Database Tutorials for Beginners Database Administrators

See more