We've encountered issues with a couple of our customers wherein, as that database grows, index maintenance becomes difficult to manage.
In some cases, because the indexes become quite large, the index rebuilds on badly fragmented indexes fill the transaction logs.
For one of our customers, we resolved it in part by altering the recovery model to be BULK
LOGGED during the index maintenance.
Index Rebuild operations qualify
as a minimally logged operation, and can therefore take advantage of
reduced logging in BULK LOGGED recovery model.
I wanted to do some research or testing before I recommended
this, because it’s a big deal to change recovery models, do index maintenance,
and then switch back.
I did find a link to someone else who did some testing,
though, and found that using bulk logged would, indeed, reduce log file growth
during re-indexing.
For his test Balmukund used Adventure Works, of course 😉. Classic 😀
In FULL RECOVERY MODE, the DBCC Reindex used about 100 MB
space.
(by my math using rounding: 41-2 = 39% used. 258 *.39 = 100.62)
In BULK LOGGED, it used about 2.5 MB.
(again,
rounding: 4.77- 2.98 = 1.8% of 258 is 2.58.)
This is a *VERY* significant difference.
While I understand that switching back and forth between FULL and BULK LOGGED recovery model is scary, this remains something to keep in our hip pocket when index maintenance is filling the transaction log!
TODO: Verify that partitioning the table/index (es) involved will not also help this situation. My guess is that it will not, because there is only one transaction log for a database in sql server, and logging is logging. Still, I'd like to test the theory.
Someday 😏
