Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

User DB Transaction Log fills up When Running Online Index Rebuild With SORT_IN_TEMPDB = ON Expand / Collapse
Author
Message
Posted Thursday, September 2, 2010 4:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 21, 2011 12:17 PM
Points: 103, 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
Post #979965
Posted Thursday, September 2, 2010 5:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 21,340, Visits: 15,016
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #979976
Posted Thursday, September 2, 2010 5:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 5:06 PM
Points: 42, Visits: 272
Interesting one. You said that you migrated from 2005, is the user database in 2008 compatability mode?
Post #979981
Posted Thursday, September 2, 2010 11:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 21, 2011 12:17 PM
Points: 103, 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.
Post #980037
Posted Friday, September 3, 2010 1:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 6:10 AM
Points: 350, Visits: 429
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
Post #980054
Posted Friday, September 3, 2010 2:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 234, Visits: 1,085
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!
Post #980078
Posted Friday, September 3, 2010 7:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 1,232, Visits: 9,678
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?
Post #980177
Posted Sunday, September 5, 2010 9:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 21, 2011 12:17 PM
Points: 103, 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

Post #980798
Posted Tuesday, September 7, 2010 10:47 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 20, 2013 8:21 AM
Points: 61, Visits: 699
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.
Post #981714
Posted Tuesday, September 7, 2010 11:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 21, 2011 12:17 PM
Points: 103, 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. 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

Post #981740
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse