Sql-server – shrink database in always on availability

availability-groupssql servertransaction-log

hi i was using this query for shrinking the database log file

USE Tfs_CoE;
GO
ALTER DATABASE Tfs_CoE
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (Tfs_CoE_Log, 1);
GO
ALTER DATABASE Tfs_CoE
SET RECOVERY FULL;
GO

it was working fine until i have added the database to always on availability group and now i get this error

Msg 1468, Level 16, State 2, Line 4
The operation cannot be performed on database "Tfs_CoE" 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 4
ALTER DATABASE statement failed.
The log for database 'Tfs_CoE' cannot be shrunk until all secondaries have moved past the point where the log was added..

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

Can anyone help me in shrinking the log file of the database which has grown to 650gb which is configured for always on availability group actual size of the database is 39gb only ?

I have checked with taking backup of transaction log even but that dont work for me the log file size remains same 650gb.

Best Answer

For a database to be in an Availability Group it needs to be in FULL recovery mode. Part of your script tries to set it to Simple mode.

The bigger question of course is, why are you shrinking your log at all?

Edit: OK, having seen your edit - the error indicates (one of) your secondary database(s) is not up to date, I'd look into why that might be... You won't be able to truncate the log until all secondaries are up to date.

Related Question