June 16, 2011 at 9:08 am
Hi @ all.
While performing an online rebuild of the indexes you cannot perform any inserts due to the locks in each phase. (we get Timeout Errors which are caused by table locks)
This is now absolutely theoretical since I don't have the needed knowledge for this 🙂
We have put our heads together in order to find a solution for having an online rebuild of the indexes since we cannot go offline with the production system since it is 24/7 (airport).
If (theoretically speaking) we set up the DB system behind our application as a mirroring solution with a widness server, could we take 1 DB server offline (from the syncronisation) and perform a online rebuild on this machine, and afterwards bring it back online and do the same with the other one?
Would the syncronisation catch up or would the data be missing for this time period?
Ah I forgot, we are using SQL Server 2008 R2 Enterprise
Thanks a lot in advance,
David
June 16, 2011 at 11:57 am
mirroring won't help because one database will always be offline in NORECOVERY mode having the transactions applied. you won't be able to do anything with that database. you might try partitioning your table, then you can rebuild the index based on a single partition at a time. that might get your index rebuild without any locking issues.
June 16, 2011 at 1:16 pm
OLDCHAPPY (6/16/2011)
mirroring won't help because one database will always be offline in NORECOVERY mode having the transactions applied. you won't be able to do anything with that database. you might try partitioning your table, then you can rebuild the index based on a single partition at a time. that might get your index rebuild without any locking issues.
Thanks for your reply 🙂
I understand what you mean, but wouldn't it be possible to unset the NORECOVERY MODE of the database on the principal server (which is not accessible anymore by the application) so I can access it, run the rebuild indexes jobs and set it back to NORECOVERY before switching over again?
Also doing the same for the mirror server or do I need to restore a full & transaction backup each time I do this with the RECOVER WITH NORECOVERY option?
June 16, 2011 at 2:24 pm
sysadm 77674 (6/16/2011)
OLDCHAPPY (6/16/2011)
mirroring won't help because one database will always be offline in NORECOVERY mode having the transactions applied. you won't be able to do anything with that database. you might try partitioning your table, then you can rebuild the index based on a single partition at a time. that might get your index rebuild without any locking issues.Thanks for your reply 🙂
I understand what you mean, but wouldn't it be possible to unset the NORECOVERY MODE of the database on the principal server (which is not accessible anymore by the application) so I can access it, run the rebuild indexes jobs and set it back to NORECOVERY before switching over again?
Also doing the same for the mirror server or do I need to restore a full & transaction backup each time I do this with the RECOVER WITH NORECOVERY option?
No - you cannot do this. With mirroring - you can change the principal by failing over to the mirror. When that happens, the mirror becomes active and the principal becomes the mirror. You still cannot access the other database.
You would have to break the mirror - and then, you would not be able to synchronize the changes between the online/available database and the other.
What you want to do is research rebuilding your indexes online (ALTER INDEX ... WITH (ONLINE = ON). You probably would also benefit from setting the SORT_IN_TEMPDB option on also.
This will rebuild the indexes while keeping the existing index ONLINE and available. Once the index is rebuilt, there is a momentary lock taken to switch out the old index and the newly rebuilt index.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 17, 2011 at 1:58 am
Thanks for your reply.
As mentioned before the online rebuild is not working as expected.
Whenever we do this and there is a transaction which makes an insert into the designated table where we are running the rebuild on, we get errors (Table locked).
That way the transactions are not inserted into the db and are missing (we are talking here about money which does not show up) so I am open for every idea which comes to mind 🙂
Thanks,
David
June 17, 2011 at 2:12 am
sysadm 77674 (6/17/2011)
As mentioned before the online rebuild is not working as expected.Whenever we do this and there is a transaction which makes an insert into the designated table where we are running the rebuild on, we get errors (Table locked).
this part i am surprised to hear. i thought the whole point of the ONLINE option (available only in Enterprise Edition) was to avoid locking problems. my understanding is that with the ONLINE option, SQL creates a new index parallel to the existing index, and then after the rebuild is complete it drops the old and replaces it with the new one, such that there is no disruption of service other than maybe an unnoticeable pause during the switch.
how large is this table? how many rows? how long does it take to rebuild the index?
my idea was that if you divide up your table with partitioning, you can index the partitions individually, and spread out the time to rebuild, thus limiting your exposure to locking issues.
June 28, 2011 at 8:15 am
Sorry for the delay in answering.
We were also surprised to see what is happening.
Because of the Online Rebuild functionality we sold the customer the Enterprise license.
The most ciritcal table has the following dimensions:
Index Space: 18.742,250 MB
Row Count: 68404264
Data Space: 6.326,234 MB
I have shrunk the DB with the data compression (ROW) because normally it would be 3 to 4 times as big.
Could you kindly tell me more about the partitioning you mentioned?
Do you have a good address where I can check some docs on that one?
June 28, 2011 at 8:27 am
I don't have any hands on experience with partitioning but here's some doc I found:
Partitioned Table and Index Concepts
http://msdn.microsoft.com/en-us/library/ms190787.aspx
Check out the partition parameters in the ALTER INDEX command:
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply