Why does dropping foreign keys take long?
Dropping a constraint requires a Sch-M (Schema Modification) lock that will block others to query the table during the modification. You are probably waiting to get that lock and has to wait until all currently running queries against that table are finished.
A running query has a Sch-S (Schema Stability) lock on the table and that lock is incompatible with a Sch-M lock.
From Lock Modes, Schema Locks
The Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released.
Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.
The Database Engine uses schema stability (Sch-S) locks when compiling and executing queries. Sch-S locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions, including those with X locks on a table, continue to run while a query is being compiled. However, concurrent DDL operations, and concurrent DML operations that acquire Sch-M locks, cannot be performed on the table.
I will walk you through an example so, you can see why it was taking long time. Creating an empty database for this test.
CREATE DATABASE [TestFK]
GO
Creating 2 tables.
USE [TestFK]
GO
CREATE TABLE dbo.[Address] (
ADDRESSID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Address1 VARCHAR(50),
City VARCHAR(50),
[State] VARCHAR(10),
ZIP VARCHAR(10));
GO
CREATE TABLE dbo.Person (
PersonID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
LastName VARCHAR(50) NOT NULL,
FirstName VARCHAR(50),
AddressID INT);
GO
Creating a Foreign Key constraint on Person table.
USE [TestFK]
GO
ALTER TABLE dbo.Person ADD CONSTRAINT FK_Person_AddressID FOREIGN KEY (AddressID)
REFERENCES dbo.Address(AddressID)
GO
Insert some data into both tables.
USE [TestFK]
GO
INSERT dbo.Address (Address1,City,[State],Zip)
SELECT '123 Easy St','Austin','TX','78701'
UNION
SELECT '456 Lakeview','Sunrise Beach','TX','78643'
GO
INSERT dbo.Person (LastName,FirstName,AddressID)
SELECT 'Smith','John',1
UNION
SELECT 'Smith','Mary',1
UNION
SELECT 'Jones','Max',2
GO
Open a new query window and run this (do not close the window once the query is completed).
USE [TestFK]
GO
BEGIN TRAN
INSERT dbo.Person (LastName,FirstName,AddressID)
SELECT 'Smith1','John1',1
UNION
SELECT 'Smith1','Mary1',1
UNION
SELECT 'Jones1','Max1',2
Open another query window and run this.
USE [TestFK]
GO
ALTER TABLE dbo.person DROP CONSTRAINT FK_Person_AddressID
You will see you drop constraint will keep on running (waiting) and now run the query to see why it is running longer and what locks it is waiting for.
SELECT * FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL;
Once you commit your insert operation, drop constraint will complete immediately because now drop statement can acquire required lock.
For your case you need to make sure no session is holding a compatible lock which will prevent drop constraint to acquire necessary lock/locks.