Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


User DB Transaction Log fills up When Running Online Index Rebuild With SORT_IN_TEMPDB = ON


User DB Transaction Log fills up When Running Online Index Rebuild With SORT_IN_TEMPDB = ON

Author
Message
charles evans
charles evans
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 158
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)
go

(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.

-Charley
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22716 Visits: 18261
You are attempting to run this against just one index or is a bunch of indexes (when you run it manually in SSMS)?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

henners72
henners72
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 313
Interesting one. You said that you migrated from 2005, is the user database in 2008 compatability mode?
charles evans
charles evans
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 158
Jason,

I'm running this against just one index when I do so manually.

Henners,

Yes the user database is run in SQL Server 2008 (100) compatibility mode.

Thanks for the responses.
COOL_ICE
COOL_ICE
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 431
If this issue reported is on production box, I would recommend upgrading MSSQL server to the latest and greatest SP and HF on test box just to check if the issue is reproduceable.

- SAMJI
If you marry one they will fight with you, If you marry 2 they will fight for you :-)
vultar
vultar
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 1213
I'm also having the same problem as Charley using SQL Server 2008 R2 & SQL Server 2005 SP3 (both x64).

Any help would be greatly appreciated!
HowardW
HowardW
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1415 Visits: 9892
As far as I'm aware, regardless of where the data is sorted, the transaction log of the user database must fully log the operation to enable Rollback.

In an Online Index Rebuild, this would involve:

1) Logging the creation and all updates to the Temporary Mapping Index (this is the temporary index that records all changes made to the index being rebuilt during the operation to enable the new index to be updated again at the end of the operation) - the size of this is dependent on how many INSERT/UPDATE/DELETE's are performed against the base table during the rebuild

2) The creation of the new Target Index

3) The de-allocation of the Source Index

The logging you're potentially avoiding by sorting in Tempdb is just the temporary write to enable a sort to be performed before it can be written back to the Target index (incidentally, MS has a note that says this option is ignored if it can perform the sort in memory).

I don't believe there's been any fundamental change between 2005 and 2008 though, so I'm not so sure this behaviour should have substantially changed. The only thing I can think is that they may now have made the sort operation more minimally logged as it's not required for recovery so you may see less TLog growth in Tempdb...

Have you got an identical copy of the database running on 2005 that you can test side by side?
charles evans
charles evans
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 158
Hi Howard, thanks for your response.

No, we don't have any servers that still run 2005, but your explanation makes the most sense to what is happening on the server. I know for a fact that the transaction log file in the user database was considerably larger and had been shrunk (due to a poor DBA decision) when we ported our production server from our old machine to the new machine. This would indicate why we hadn't seen this problem in years.

We've negotatiated the problem by simply introducing more log space, though I'd like to see Microsoft retract their statement that when SORT_IN_TEMPDB is used "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 is obviously not the case.


Thanks to everyone on their input here. I think the only thing to do from this point would be to ask MSFT to clarify the quoted text from this article: http://msdn.microsoft.com/en-us/library/ms184246(v=SQL.100).aspx. Perhaps I'll open a support request with them for this (though support requests opened with MSFT have often been a disappoinment for me, due to the seeming lack of dedication from the MSFT support engineers.)

Cheers,

Charley
David BAFFALEUF
David BAFFALEUF
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 712
Hi charles,

Just a though, but haven't you tried to switch to BULK_LOGGED before you run the index rebuild ? As you don't mention it, I'm wondering if there are any restriction that would prevent you going to bulk_logged (db mirroring first guess) ?

David B.
charles evans
charles evans
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 158
Hi David, Thanks for the post, I have considered switching to bulk-logged, but our business model requires us to be in full at all times. I suppose I should have mentioned that. And of course, that would require the addition of two whole lines of code to the SP, which is simply too much work for me to do. :-D I'd much rather spend hours of time trying to figure out why something the documentation from MSFT says doesn't appear to be true.

FYI for those who might be interested, I reposted this issue a few hours ago over on the Technet SQL Server forum to see what folks over there might say. So far, no replies. Here's the link: http://social.technet.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d2f4d5c5-4d4d-4e85-a3b1-e1cff2f2ec91
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search