For anyone that has every manually created SQL Server Mirroring, they understand how troublesome a Mirroring configuration can be to implement, and how frustrating it can be to troubleshoot. On the flip side of that coin, those same people truly understand how freakin' awesome it is that the Lync Server 2013 Topology Builder will completely take care of the Mirroring setup on its own if the proper permissions are in place, including a Witness! With such beautiful automation in place regarding the backend SQL configurations, it can be easy to forget about the the SQL portion of the Lync Server 2013 environment all together; you almost have a false sense of "set it and forget it".
Obviously, it would be incredibly foolish to purposefully forget about the SQL environment after Lync installation, but let's just say in the excitement of finally getting all the pieces successfully deployed and having a functional Lync deployment, you forget to go back onto the primarly SQL server in the Mirror (or just the SQL server, if Mirroring was not set up), and set up the necessary maintenance plans. Why would you need maintenance plans for backups if you have a real-time copy of the databases on your Mirror at all times, you ask? Well, Mirroring requires that the databases have a Full recovery model, which means that their logs will continue to grow, without being truncated, until they cannot grow any longer.
Going back to our hypothetical, you have this successfully setup Lync environment, and a pristine SQL Mirror setup, and life is good. Then suddenly life is not so good when suddenly your address book in Lync is not displaying all the results that it should. Eventually you discover that the Address Book is either corrupt or not functioning propery, and you discover that the drive on your SQL server that contains the database log files is full. Why is it full? Well, that pesky rtcab database gets a LOT of transactions and updates, for some reason, and that transaction log has grown to a few hundred GB in size! None of the other logs may have grown much at all, but that rtcab log is just ornery.
So, first thing to do is realize that we should have made a maintenance plan within SQL Server Management Studio from the get-go to keep our databases regularly backed up with a Full backup, and then a second maintenance plan to backup all the Transactions Logs, which would in turn truncate the logs. However, in many cases, an hourly transaction log backup might be necessary for keeping the rtcab log from growing, Alright, so we are a bit lit, but we get those maintenance plans in place, and try to manually backup the database and transaction log, which is successful, but we notice that the transaction log is still several hundred GB in size. So, we try to shrink the rtcab log file, and while it seems like the command completed successfully in the Management Console, the file is STILL the same size.
Next, we turn to good ole' Google to find out why the Shrink command is not working on the log file (make sure when you try to shrink the log file in the Management console that you choose "Log" from the drop-down instead of leaving it on the default of "Data"). Google isn't much help because all that every DBA and their mother wants to do is scream about why "you should never Shrink the database; best practice is to have full backups and transactional log backups running on a schedule." This is the point where we are thinking, "Yeah, thanks for that overwhelming helpfulness, DBAs. We are aware of what Best Practice is, and will be using that moving forward, but how in the world do I free up the hundreds of GBs on my drive in the mean time?"
So, now that I have made you read through all this to find out why that rtcab log file won't shrink after using the Management Console, or using the DBCC SHRINKFILE command, the answer is simple: because the log is still being utilized in the Mirroing relationship, it cannot be shrunk. You must go into the Properties of the database, go to the Mirroring tab, and then Pause the mirror. This leaves the Mirror in tact for the database, but shows a status of Suspended. Once this is done, you should be able to run the Shrink operation, and the file should instantly shrink down to almost nothing. BOOM! You now have hundreds of GBs of space back. And as long as your Maintenance Plans are setup properly with a retention period (use a Maintenance Cleanup Task), you will keep your space back! Oh, almost forgot, don't forget to go back in to the Mirroring tab and Resume the Mirror!
Stay techy, my friends!
super helpful!.. other steps including actually breaking the mirror for the operation, which is far less desired. very accurate description of events too! :)
ReplyDelete