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 @[email protected]
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

 

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

 

 

 

 23,131 views last month,  3 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply