Having a table with 60 columns, and 200 rows. Altering a BIT column from NULL to NOT NULL, now has a running execution time of over 3 hours. Why is this taking so long?
This is the query that I'm execution:
ALTER TABLE tbl
ALTER COLUMN col BIT NOT NULL
Is there a faster way to do it, besides creating a new column, updating it with values from the old column, then dropping the old column and renaming the new one?
This is on MS SQL Server 2005.
IS the ALTER blocked by metadata shared locks held by other statements? Any ALTER requires a metadata exclusive lock, and as such will be block by any other statement that uses the table.
Check Activity Monitor, or look into sys.dm_exec_requests and see who's blocking your ALTER INDEX.
Is it still running after 3 hours (while you post the question), or completes after 3 hours?
If it's still running run DBCC OPENTRAN: you've probably an open transaction somewhere that has left a schema lock on the table
Or SELECT * FROM sys.sysprocesses WHERE blocked <> 0 gives you what has been blocked