reindexing and transaction log

[jcaulder]

Will reindexing cause appends to the transaction log? It wouldn't seem to me that it would since this is not insert/update/delete operations but rather standalone index file updates only. I ask because I was told that the transaction log backup jumped up to 11 GB when the entire size of the db itself is only around 7 GB. I was told that a reindex job was executed that hadn't been ran since the db was brought live but other than that, nothing unusual had occurred.

Any ideas on what would cause such a large jump in the transcation log?

TIA!

 

[tlbroadbent]

Reindexing will make the transaction log grow very large if the DB recovery model is FULL. SQL writes detailed transactions to the log for recovery purposes. You can set the database recovery model to Bulk Logged rather than FULL. This will reduce the log growth considerably. We have found that the log can grow large even when the recovery model is Bulk Logged so we only reindex tables when needed. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm