If you store your OperationsManagerDW DB on SQL AlwaysOn replica – I’m sure you’ve seen this error.
Let me first start by stating that I am not an expert in SQL but ever since I’ve started working with SCOM, SCCM and other System Center products I have been using and configuring SQL server pretty intensively.
In this situation, I have 2 SQL 2012 servers hosting both my SCOM DB’s and instead of installing the SQL with a fully loaded cluster I have decided to work with the new AlwaysOn feature. As I’m not a big expert in SQL I have no deep understanding about the clustering features prior to 2012, but I have read on the Technet that it is a solid solution for SCOM 2012 and decided to install it.
I have been using this setup for about six month and I can honestly say that everything is working really good. I have been moving the active DB for one server to another, rebooting servers, running backup’s on the non active servers and everything is working great! up until last week that is 🙂
Last week, for some reason, the OperationsManagerDW has stopped synchronizing with its partner. Eventually, this led to the following error in the SQL server’s application log:
The transaction log for database ‘OperationsManagerDW’ is full due to ‘AVAILABILITY_REPLICA’.
This also led to the fact that SCOM stopped working – no new alerts…
Since I’m not an expert with SQL (we’ve established that already) so I had no clue how to investigate why the SQL stopped replicating that DB. My solution to the problem was pretty simple – I have removed the DB from the AlwaysOn group and re-added it. As soon as the DB was removed from the group SCOM began to reply new alerts.