mssqlserver.com: FAQ: I have a corrupt SQL Server table that I can't drop. What can I do?

I have a corrupt SQL Server table that I can't drop. What can I do?

DBCC checkdb and newalloc the database in question and look up any other error messages that occur - then you can see the extent of the problem.

The reason SQL won't let you drop a table in this situation is because the allocation pages/extent chain appears to be damaged or cross-linked in some way. So SQL Server thinks that there is actually data from other tables in pages/extents belonging to the problem object. If it lets you drop the table it might remove data from another table by mistake.

Your choices are :-

Restore from a known good backup.
Create a new database and copy all the data/objects out. You can use transfer manager/bcp for this. Then rename the databases round. (or dump and re-load if you had to do this across a network connection)
With a VERY large database with consequent loss of data then if you don't want to do 1 or 2, then you can rename the table by directly updating sysobjects. Then create a new table and just leave the old one there. Note that this is dangerous as there may be more severe corruption problems that will still be there.
Call MS PSS and pay for a support call. They have utilities that allow page/link editting and they MAY try and "fix" the problem for you. However this is not 100% succesful, they may cause more damage, and you have to sign a form accepting all responsibility (and absolving them of any) for any problems that occur.

Michael R. Hotek

All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.