Unable to Shrink Log file for Always On Availabilty Group Database

Question

We are trying to shrink the log file using the below SQL Command.

ALTER DATABASE [SharePoint_Config] SET RECOVERY SIMPLE WITH NO_WAIT

USE [SharePoint_Config]
GO
DBCC SHRINKFILE (N'SharePoint_Config_log' , 0, TRUNCATEONLY)
GO

ALTER DATABASE [SharePoint_Config] SET RECOVERY FULL WITH NO_WAIT

Gettng the below Error Message. Is there any other method ?

Msg 1468, Level 16, State 2, Line 1
The operation cannot be performed on database "SharePoint_Config" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Answer 1

Don't set recovery to simple, quite simply! Simple recovery is not a requirement for shrinking a database. You are not allowed to set a database to simple if it is in an availability group (as the error message states). Here's some more info on handling large log files: https://karaszi.com/large-transaction-log-file

Answer 2

The operation cannot be performed on database "SharePoint_Config" because it is involved in a database mirroring session or an availability group

Your approach is wrong, you are trying to make recovery model simple for database which is participating in AG, this is not allowed by Microsoft. You just cannot do that.

What you need to do is find out what is holding those logs from being truncated and below query will tell you.

select log_reuse_wait_desc from sys.database where name='SharePoint_Config'.

Let us say its availability groups. Which means logs on primary has not truncated because the same logs have not been hardened on secondary. You need to find out why. Mostly its because there is heavy transaction going on, the network is choked and the movement from primary to secondary is slow. This causes uncommitted logs piling up on primary thus causing log file to grow.

Answer 3

Hi there,

sometimes it just so simple that you don't get it... :-)

I have a small test environment. SQL 2016 Ent Ed with an AAG and one AdventureWorks DB in it.

Log size was initially 72MB. So I extended the file to 512 MB. Ran some stupid idx rebuilds, made a manual failover.
Took a log backup and then ran
USE [AdventureWorks]
GO
DBCC SHRINKFILE (N'AdventureWorks_log' , 256)
GO

Guess what?
It worked

Microsoft SQL Server 2016 (SP2-CU14) (KB4564903) - 13.0.5830.85 (X64) Jul 31 2020 18:47:07 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

So, here's probably the final solution for you @SGH or at least something you can try:

Make sure that you take some log backups. Either you have this scripted as an sql agent job or some kind of 3rd party software.
Just initiate a log backup.
Afterwards, instead of the shrinkfile with truncate only
you do something similar to this

USE [yourBD]
GO
DBCC SHRINKFILE (N'NameOdfYourLogFile' , 256) < -YMMV
GO

So you define the new size you want to have.

My output was

(1 row affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Completion time: 2020-11-20T20:35:59.4663095+01:00

Tibor already mentioned this solution in his blog post. https://karaszi.com/large-transaction-log-file

Same works for a database in an AAG.

You just don't need to switch the recovery model.

Best regards
Dirk

Answer 4

Hi @SGH ,

You want to shrink database log files? Please reference this firstly:
shrinking-sql-log-files-in-an-availability-group-cluster-or-database-mirror

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

Hi @SGH ,

I was lazy to post a link and then find the link is offline and then change it to another one, and this action was catched by MVPs ,I have to admit that the wrong thing lies in my attitude and I didn't reply seriously enough, and there is a saying that the owed will be returned after all, so I came to return it today.

There are two questiones need to be replied.

--Q1:about the error message

Msg 1468, Level 16, State 2, Line 1
The operation cannot be performed on database "SharePoint_Config" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

Operations that require transaction log backups are not supported by the simple recovery model. The following features cannot be used in simple recovery mode:

-Log shipping

-Always On or Database mirroring

-Media recovery without data loss

-Point-in-time restores

--Q2:how to shrink log in AG
--2.1:General situation:

--backup log
backup log dbname to disk = 'x:\xx\dbnamelog.bak'

--DBCC SHRINK
DBCC SHRINKFILE(dbname_log,0)

--2.2：other situations,such as you have shrinked many times, the log size is not be shrinked,you need to check the cause,and @Shashank Singh 's reply is very helpful.
-step1:check the log size and %

DBCC SQLPERF(LOGSPACE)
GO

--step2:check the log_reuse_wait_desc, and based its value and description to execute the command

select log_reuse_wait_desc,* from sys.databases

--step3:If the wait is LOG_BACKUP,please backup the log, if it is active_transaction, you need find the open tran and then commit the open tran or kill the open tran,and details you can reference :sys-databases-transact-sql

Test on my own side:

After I insert data, I check the log size as next:

Then shrink many times the log size is not shrinked;

I check the log_reuse_wait_desc, it is log_backup, so I backup the log and then shrink,the result changes as next:

--test code:

use test1026  
  
--step1  
SP_helpdb test1026  
GO  
DBCC SQLPERF(LOGSPACE)  
GO  
  
--step2 inser data  
use test1026  
drop table t1026  
create table t1026(a int, b nvarchar(3900))  
go  
  
declare @i int  
set @i =1  
while @i <=100000  
begin  
  
insert into t1026 values (1,REPLICATE(N'A',3900))  
insert into t1026 values (2,REPLICATE(N'B',3900))  
insert into t1026 values (3,REPLICATE(N'C',3900))  
insert into t1026 values (4,REPLICATE(N'D',3900))  
insert into t1026 values (5,REPLICATE(N'E',3900))  
insert into t1026 values (6,REPLICATE(N'F',3900))  
insert into t1026 values (7,REPLICATE(N'G',3900))  
insert into t1026 values (8,REPLICATE(N'H',3900))  
  
set @i=@i +1  
end  
  
--stop this at some time   
select count(*) from t1026  --(179465)  
  
--then check log   
SP_helpdb test1026  
GO  
  
DBCC SQLPERF(LOGSPACE)   
GO  
  
--step4: shink as next  
  
backup log test1026 to disk = 'C:\fiels\test1026log.bak'  
  
  
DBCC SHRINKFILE(test1026_log,0)  
  
--then check log,find it is not shrinked  
SP_helpdb test1026  
GO  
  
DBCC SQLPERF(LOGSPACE)   
GO  
  
--Steps:  
DBCC LOGINFO(test1026)  
  
select log_reuse_wait_desc,* from sys.databases    
  
backup log test1026 to disk = 'C:\fiels\test1026log.bak'  
  
DBCC SHRINKFILE(test1026_log,0)  
  
--check again, the log has been shrinked.  
DBCC SQLPERF(LOGSPACE)   
GO  

More information:what-can-i-do-if-my-transaction-log-is-full-hot-is.html

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.