Should I attempt to restore the database again or proceed with attempting to repair? I only hesitate because it is a rather large DB and restoring takes quite a while.
I'd probably do both, but restore it to a new name.
All of the table errors are related to a single table I could just drop and recreate. However I don't understand what the IAM error really means or its impact.
If the data in that table really, truly doesn't matter, and you can really, truly drop and recreate the table, that's probably the fastest option. Especially since "repair_allow_data_loss" will probably cause data loss, yes.
As for what the IAM error means:
Short version: Your table innards are corrupted.
Longer version: The Index Allocation Map tells SQL Server where to get the actual objects. In this case, based on the references to RIDs I'm guessing this is a table without a clustered index (in other words, a heap), and that it's telling SQL where to get the data that was actively updated inside the heap. (One of the things that happens when a heap has updates is that if the new information doesn't fit in the old space, a pointer is created that tells SQL where to get the new information.)
Here's an article on IAM pages, and here's a good video on heaps in SQL Server that came up when I was looking for IAM info for you.
When you first kick off a DBCC CHECKDB it will create a database snapshot to allow the database to stay online during the process. There are some things you can look at if you are running into problems with this:
- Defragment you NTFS system, there is an issue where if the NTFS storage is heavily fragmented it will not allow SQL Server to create the database snapshot. This is because the snapshot can only have a certain amount of fragments.This is documented on the microsoft support center in OS errors 1450 and 665 are reported for database data files. (I believe your problem lies here)
- Check to see if anyone has updated an NTFS filter driver as there can be a problem with alternate filter streams.
Additionally, you can always attempt to create a database snapshot of the database on a less fragmented part of the NTFS and run CHECKDB there to hopefully.
Best Answer
CHECKDB need two types of storage while executing:
Tempdb. This is to keep track of where it is (to simplify quite a bit). This is what Dominique refers to. However, this was not your problem (as we can see by the error message).
On the same disk where each data file for the database resides. It creates one "CHECKDB working file" for each data file for your database. Since CHECKDB work with an internal snapshot database, it need to do copy-on-write. I.e., whenever a modification is done in your database, SQL Server need to first copy that extent (64 KB block) to a snapshot file. You can see the snapshot file in the error message - this is how we can deduce that the problem isn't with tempdb.
I.e., the more data you modify, the more storage is needed for the snapshot file. You don't want to have a checkdb running while you rebuild an index or do a large import, as a couple of examples. As you can imagine, we can't say how much space is needed since we don't know how much data modifications you do while checkdb is running.
So, check the timing for your jobs.