I am running Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) on a Windows Server 2008 x64 EE server.
I wondered if anyone has come across this issue or knows why this may be happening. 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 based off the fragmentation percentage of the index.
A couple of months after porting the database to SQL 2008 from SQL 2005 and switching servers, the transaction log in the user database started blowing up when this SP was run. The trouble definitely occurs with online rebuilds and I'm not sure about offline rebuilds. Each online rebuild executes this code:
ALTER INDEX [IX_NAME] on [tbl_TABLENAME] REBUILD WITH ( STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = on, maxdop = 4)
(IX_NAME is just a generic index name and tbl_TABLENAME is a generic table name, both generalized for posting this on a public forum.)
This MSDN article states 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." http://msdn.microsoft.com/en-us/library/ms184246(v=SQL.100).aspx
The user database operates under the FULL recovery model. There are almost no concurrent user transactions happening when this runs. The size of the index is ~20GB, the size of the user database transaction log is ~60GB, the size of the tempdb transaction log is ~60GB, and each t-log usually starts out with between 1 and 3% full, as we have a log backup job running every 10 minutes and there are almost no concurrent transactions running during this time. The SP that runs this uses no explicit transactions.
Still, the user database's transaction log blows up during this operation, reaching 99% full in a matter of minutes, while the tempdb transaction logs fill by less than 1%. I have also tried running the same index operation manually in SSMS and the same results happen.
Any ideas on what may be causing this? Is it because the operation is online? I can't seem to find any documentation that specifically mentions what happens in the transaction log for online index operations that use SORT_IN_TEMPDB.
Any help or clues to the puzzle will be greatly appreciated.