Table lock during partition compression - by Alexandre VM

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
0
Sign in
to vote
ID 709976 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 11/30/2011 11:55:38 AM
Access Restriction Public

Description

We have a table with 880 million rows (51GB of space). This table is partitioned by date.

To save space, we are trying to compress all old partitions. To avoid table lock, we change the LOCK_ESCALATION to AUTO, according the SQL Server documentation.

But, the Partition Compress is generating a Table Lock, blocking my application sessions.

SQL Server Build: 10.50.1720
Sign in to post a comment.
Posted by Microsoft on 1/30/2013 at 4:33 PM
Alexandre,

Thank you for reporting your problem.

Unfortunately, in the current version of SQL Server only Offline Index Rebuild is supported for a single partition.
This means that the entire table is inaccessible for the duration of the operation.
Online Index Rebuild is supported only for the whole table and this will allow concurrent access, but, as you mentioned in your workaround, you always have to rebuild the entire table.
We are currently working on supporting Online Index Rebuild for a single partition and hopefully this will be available in our next release.

Please let me know if you have further questions.

Thank you,
Panagiotis Antonopoulos