question

sajithgh avatar image
0 Votes"
sajithgh asked ·

Unable to Shrink Log file for Always On Availabilty Group Database

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.

sql-server-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TiborKaraszi avatar image
2 Votes"
TiborKaraszi answered ·

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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered ·

Hi @sajithgh,

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.


· 8 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hm,

I have a problem with this advice.
A log backup to nul device? That would disturb desaster recovery steps.

"Execute the backup command to backup the transaction log to a null location:
BACKUP LOG databaseName TO DISK='NUL:' WITH NO_CHECKSUM, CONTINUE_AFTER_ERROR"

This would break my ability to perform a point in time restore.

So, I have a full backup at night. During the day I have a few log backups and again some transactions. Then I perform backup to NUL
In case of a recovery I'm screwed.
As a DBA that would be a career limiting move.


1 Vote 1 ·

I have a problem with this advice.

A log backup to nul device? That would disturb desaster recovery steps.

You only have one problem= I have two. The idea as such is poor, but also is very disturbing that it is suggested by someone-MSFT.
1 Vote 1 ·

To quote Tibor:
I wanted to put it mildly.

0 Votes 0 ·

I fully agree!

"As a DBA that would be a career limiting move."

To put it mildly...

0 Votes 0 ·

Citrix has taken down the article after my hint. It's currently under review.

1 Vote 1 ·

Good work, Dirk!

1 Vote 1 ·

Ok, now you changed the URL to a different article and not the Citrix advice anymore

Still a hint with backup to NUL device though.
At least there it is stated to use it at own risk...

0 Votes 0 ·

Ok, now you changed the URL to a different article and not the Citrix advice anymore

Still a hint with backup to NUL device though.
At least there it is stated to use it at own risk...


A bit obnoxious, isn't it? I must say that that Report button is starting to itch on me...




0 Votes 0 ·
Shashank-Singh avatar image
1 Vote"
Shashank-Singh answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DirkHondong avatar image
1 Vote"
DirkHondong answered ·

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 @sajithgh 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


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered ·

Hi @sajithgh,

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

42834-20201126logreusewait.png

Test on my own side:

After I insert data, I check the log size as next:
42845-20201126aglogtest1.png
Then shrink many times the log size is not shrinked;
42806-20201126logtest2.png
I check the log_reuse_wait_desc, it is log_backup, so I backup the log and then shrink,the result changes as next:
42774-20201126logtest3.png

--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.



· 13 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the explanation. However I am totally confused now. Can anyone please suggest and provide the final solution for shrinking the log file of SharePoint_Config ?

Is it possible to configure Maintenance Plan in SQL Server ?

0 Votes 0 ·

Did you ever answer the question why you want to shrink the log file in the first place?

Shrinking a database file is a very exceptional operation. And absolutely nothing you put in a maintenance plan.

0 Votes 0 ·
sajithgh avatar image sajithgh ErlandSommarskog ·

Because the log database is increasing enourmously. Now it is more than 20 GB.

0 Votes 0 ·
Show more comments

Yes - you can create a maintenance plan...and you absolutely must setup a process to backup the database (daily if possible - if not, then weekly with daily differentials) and backup the transaction log (frequently - every 15 minutes is generally a good place to start). Based on this question alone, I assume the reason for your log growing is because you don't backup the transaction log - which leads you to try the very bad advice of switching to simple recovery, shrinking the log and switching back to full.

0 Votes 0 ·
sajithgh avatar image sajithgh JeffreyWilliams-3310 ·

Backup is performed for primary. However secondary replica is not done. Is the log size due to not taking the backup of secondary replica in the high availability.

0 Votes 0 ·
Show more comments