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

A. 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 let you drop the table it might remove data from another table by mistake.

Your choices are :-

1. Restore from a known good backup.
2. 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)
3. 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.
4. Call Microsoft 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.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.