0

I have a database whose log file size is 527GB, showing almost 100% use. The DB is in AO Asynchronous replication with another SQL server. DB is in Full backup mode, and Log backup is happening every hour. I tried to shrink log file, it didn't work, gave me following message. 

Msg 1468, Level 16, State 2, Line 2
The operation cannot be performed on database "MYDB" 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 2
ALTER DATABASE statement failed.
The log for database 'MYDB' 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.

I think its not doing it because of replication all logs are not transferred and its been all the time. How do solve the issue without taking Database out of AO replication group? I can move it to Synchronous mode, but I do not want it to move out of AO.

jarlh
  • 42,561
  • 8
  • 45
  • 63
arifr
  • 61
  • 2
  • 9

1 Answers1

0

To check out the cause of your problem have a look at results of the query below (log_truncation_holdup_reason field):

select * from sys.dm_db_log_stats(db_id('YourDatabaseName'))

UPD: For versions lower than 2016 SP2 you can get the same using a query:

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'YourDatabaseName'

If it is AVAILABILITY_REPLICA then check active transactions to understand which one could cause it.

dbcc opentran

Depending on results you will be able to decide what you should do.

Denis Rubashkin
  • 2,151
  • 1
  • 9
  • 14
  • sorry for delay. I am getting this message when running your query. 'Invalid object name sys.dm_db_log_stats' – arifr Oct 17 '18 at 01:32