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!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.