SQLTeam.com | Weblogs | Forums

Re-indexing on Always On SQL Cluster


#1

At my new job we have a SQL 2012 Always On Cluster. I'm looking at wanted to re-index some of the databases and want to change the recovery state to simple when re-indexing to limit log growth. Don't believe I can change recovery mode with Always on. Could do a rebuild online? Does anyone have best practice experiences they could share?

Appreciate insight


#2

I would not suggest to change the recovery model for index rebuild (or re-org). For best practice, we should always keep enough log space to cater general DB maintenance, that's includes re-index. My old rules was to keep at least 1.5x of the largest table size as log size, then monitor it for a while to obtain it effective size. For most of the OLTP workload, the log file should not growth that much, unless you always have bulk insert and huge deletes.

Hope this helps