April 20, 2015 at 10:45 pm
Hi,
I am trying to partition few tables, this requires the primary key/clustered index to be dropped and recreated with partitioning column and partition schema. The primary key/clustered index would not drop unless I drop and later re-create few foreign keys, which point to the PK, so the FK column list matches the one of the PK it is pointing to.
Even during low load times the process gets stuck on the very first FK I am trying to drop. It gets blocked and does not move at all, the process's CPU and IO stay unchanged for hours (in test environment). This drop also causes a lot of blocking.
It appears there is not "ONLINE" option for a FK.
Are there any other options of doing this without requesting a downtime window?
I am using SQL Server 2008 R2 SP2.
Thanks.
April 21, 2015 at 2:17 am
Altering a table (including dropping an FK) requires a schema modification lock. Schema mod locks are not compatible with any other locks at all, and hence you need a short period where no one is using the table to make the changes you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply