I am reposting this from another forum.
In this article
http://msdn.microsoft.com/en-us/library/ms184246(v=SQL.100).aspx, it is stated that "The index transactions will be stored in the tempdb transaction log, and the concurrent user transactions will be stored in the transaction log of the user database.
This allows for the transaction log of the user database to be truncated during the index operation if it is required."
However, this doesn't seem to be the case for me.
I am running Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) on a Windows Server 2008 x64 EE server in FULL recovery mode with all user databases set to 100 for their compatibility mode. The server has 48GB of ram. I have a nightly job that
runs ALTER INDEX REBUILD or ALTER INDEX REORGANIZE for each index in a user database. The SP called by the job determines if the table has any underlying LOB datatypes and if so will not attempt an online rebuild, it also determines whether to rebuild or reorganize
based off the fragmentation percentage of the index and does not use explicit transactions. There are almost no concurrent user transactions running when I run the index rebuild job. Each night, the user database log blows up during the index rebuild
job (or it would if I didn't have a job in place that checked the amount of used space in the log and cancelled the inde
View Complete Post