July 26, 2013 at 7:26 am
Hi all,
I have the following scenario on my 6 TB database (it's a mobile billing database) on SQL Server 2008 R2 SP2 (with Cumulative Update #5):
- Huge amount of data inserted every day, at every time on a single table
- Table partitioned on a DATETIME column with a range of 15 minutes
- Daily maintenance to merge the oldest partitions (it's only necessary to keep data from the last 60 days) and add the future partitions
- On this daily maintenance, I also rebuild the indexes partitions with fragmentation greater than 30%
In order to complete this daily maintenance, I execute some steps to avoid locks and deadlocks:
- Kill all sessions that are inserting data and/or querying this particular table
- Disable the users which inserts and query
- Execute the steps to add/remove partitions
- Rebuild indexes partitions
- Enable users
I applied the Cumulative Update # 5 patch , which says that the deadlocks on adding/removing partitions should be fixed (http://support.microsoft.com/kb/2713501/en-us). So, after that I removed the first and forth steps, so process can keep on inserting and users, querying. Well, it didn't work. The scenario occurred with deadlocks and locking on the rebuild.
I sort of "workarounded" the deadlock issue by setting the DEADLOCK_PRIORITY to HIGH, but when we rebuild the index partitions, even if I switch the fragmented partition to an auxiliary table, execute the rebuild on the index partition on this auxiliary table and then switch back the partition to the original table, it also locks the users and the inserting and querying (and the rebuild).
Is there anything I should do to fix this issue, so the users are not disabled? Any thoughts?
Thanks
July 26, 2013 at 11:33 am
Anyone?
July 26, 2013 at 2:08 pm
Go back to your original strategy but, fix your process, the first two tasks should go like:
- Disable the users which inserts and query
- Kill all sessions that are inserting data and/or querying this particular table
First disable the users then wait of sessions to end or eventually kill them.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply