• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

SQL 2012 Always On - T-Log on Primary won't release

Hello All,

We have a 2 node SQL server 2012 AlwaysOn setup for automatic failover. Availability group is configured for the database in question. We have a rebuild indexes script created for this database back in 2008 R2, didn't cause the issue back then.

When we ran this maintenance script in 2012 after the DB is upgraded from SQL 200R2, the log got filled up on both log drives on Primary and Secondary. checked DBCC opentran and Loginfo, looks like the database had replication enabled but sys.databases says it's not. T-log backup was running every 15 mins, I tried to run it again but it couldn't truncate the logs in use by replication.

Unfortunately I don't know enough about AlwaysOn and how it utilizes SQL replication so is having a hard time trying to free up drive space. I tried to remove the database from the availability group but it didn't remove the "replication" status.

Any ideas? Thanks!
3 Solutions
Jim P.Commented:
Talking from a similar experience with a SQL2005.

Shutdown everything, as gracefully as possible, and reboot both nodes. Once replication won't release the end of the log, that is about the only solution. Not pretty, but the only one I know.
It sounds like your script ran rebuilds on both ends of the replication at once - or, at least, and one end and then immediately afterwards at the other end. Rebuilding indexes generates a heap of disk I/O, and that slows down the whole system - including the secondary that's trying to keep up with the primary system. I'd suggest stopping using that job and starting writing a new rebuild job. Get the main machine's indexes rebuilt and, while you're doing that, watch how the two machines' logs fare. Then do the second machine's rebuild.

Ref: See Kendra Little's blog here.


Mark WillsTopic AdvisorCommented:

Sounds like you have a few challenges. Your secondary DB sounds like it is time to be suspended from availability group, or removed. Most likely need to re-establish the secondary DB. Have a read of : http://technet.microsoft.com/en-us/library/ff878303.aspx especially the follow up section down the bottom.

Also, have a look on the list of topics on the left hand side of the above link.

Rebuilding indexes is sometimes performed unnecessarily and would not be rebuilding unless there was going to be a positive impact. Read my Article about fragmentation http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_691-Managing-Fragmentation-for-the-Accidental-DBA.html

You should be able to do that online in 2012, but need to make sure you have performed your transaction log backup and has replicated properly. Some of the lag will depend on synch or asynch (with more lag on async).

How are you doing your rebuilds ? Are they using the latest "online" option ? You may need to reassess not just the frequency, but how you are doing that housekeeping.

Also, there has been a series of AlwaysOn articles by the MS team using SAP as an example and has brilliant performance tips and checks : http://blogs.msdn.com/b/saponsqlserver/archive/2013/04/21/sql-server-2012-alwayson-part-11-performance-aspects-and-performance-monitoring-i.aspx
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now