how best to replace a corrupt user table

Hello,
I must have a corrupt user table in my user database. If I try to get
a count of rows, I get the following error:
Could not open FCB for invalid file ID 18 in database
And if I run "DBCC CHECKTABLE ('myTable')" in QA, I get this message:
Server: Msg 7965, Level 16, State 2, Line 1
Table error: Could not check object ID 220579874, index ID 0 due to
invalid allocation (IAM) page(s).
Server: Msg 8946, Level 16, State 1, Line 1
Table error: Allocation page (1:23417559) has invalid IAM_PAGE page
header values. Type is 1. Check type, object ID and page ID on the
page.
DBCC results for 'myTable'.
There are 0 rows in 0 pages for object 'myTable'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'myTable'(object ID 220579874).
repair_allow_data_loss is the minimum repair level for the errors
found by DBCC CHECKTABLE (myDatabase.dbo.myTable).
A little background: we recently went through a server crash and lost
our transaction log, but we were able to rebuild the transaction log
and get the database back online. Also, the data files for this
database are over 420 GB in size. This is all SQL2000.
In the case of "myTable," it's only the table that's important, not
the data. So assuming I can recreate the table correctly, what is the
proper way to go about fixing this problem? I haven't tried dropping
myTable yet because this is a new situation for me and I don't want to
mess up.
Thanks as always,
Eric
If I read it correctly you don't worry about the lost data. Instead you want
the table schema back?
do you have a source control where you can retrieve SQL scripts? if not, do
you have last known good backup to restore and get the schema retrieved?
"Eric Bragas" <ericbragas@.yahoo.com> wrote in message
news:4bf75b52-f331-4712-b80b-219798cee653@.m34g2000hsf.googlegroups.com...
> Hello,
> I must have a corrupt user table in my user database. If I try to get
> a count of rows, I get the following error:
> Could not open FCB for invalid file ID 18 in database
> And if I run "DBCC CHECKTABLE ('myTable')" in QA, I get this message:
> Server: Msg 7965, Level 16, State 2, Line 1
> Table error: Could not check object ID 220579874, index ID 0 due to
> invalid allocation (IAM) page(s).
> Server: Msg 8946, Level 16, State 1, Line 1
> Table error: Allocation page (1:23417559) has invalid IAM_PAGE page
> header values. Type is 1. Check type, object ID and page ID on the
> page.
> DBCC results for 'myTable'.
> There are 0 rows in 0 pages for object 'myTable'.
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'myTable'(object ID 220579874).
> repair_allow_data_loss is the minimum repair level for the errors
> found by DBCC CHECKTABLE (myDatabase.dbo.myTable).
> A little background: we recently went through a server crash and lost
> our transaction log, but we were able to rebuild the transaction log
> and get the database back online. Also, the data files for this
> database are over 420 GB in size. This is all SQL2000.
> In the case of "myTable," it's only the table that's important, not
> the data. So assuming I can recreate the table correctly, what is the
> proper way to go about fixing this problem? I haven't tried dropping
> myTable yet because this is a new situation for me and I don't want to
> mess up.
> Thanks as always,
> Eric
|||Hi, Rick, and thanks. That's correct, I only need the table schema
back. No, there is no source control here on staging servers. Yes, I
have a backup, but I would need a new server just to restore a
database of that size. We simply don't have the space available.
Can you tell me (in theory and/or in actuality) what is the best way
to resolve this situation without a backup or source control? I don't
want to adjust all my scripts, etc. that use this table by switching
them to point to another table. I want to fix this correctly, but
don't know what is correct.
|||How about renaming the table and then creating a new one with the old name?
"Eric Bragas" <ericbragas@.yahoo.com> wrote in message
news:4bf75b52-f331-4712-b80b-219798cee653@.m34g2000hsf.googlegroups.com...
> Hello,
> I must have a corrupt user table in my user database. If I try to get
> a count of rows, I get the following error:
> Could not open FCB for invalid file ID 18 in database
> And if I run "DBCC CHECKTABLE ('myTable')" in QA, I get this message:
> Server: Msg 7965, Level 16, State 2, Line 1
> Table error: Could not check object ID 220579874, index ID 0 due to
> invalid allocation (IAM) page(s).
> Server: Msg 8946, Level 16, State 1, Line 1
> Table error: Allocation page (1:23417559) has invalid IAM_PAGE page
> header values. Type is 1. Check type, object ID and page ID on the
> page.
> DBCC results for 'myTable'.
> There are 0 rows in 0 pages for object 'myTable'.
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'myTable'(object ID 220579874).
> repair_allow_data_loss is the minimum repair level for the errors
> found by DBCC CHECKTABLE (myDatabase.dbo.myTable).
> A little background: we recently went through a server crash and lost
> our transaction log, but we were able to rebuild the transaction log
> and get the database back online. Also, the data files for this
> database are over 420 GB in size. This is all SQL2000.
> In the case of "myTable," it's only the table that's important, not
> the data. So assuming I can recreate the table correctly, what is the
> proper way to go about fixing this problem? I haven't tried dropping
> myTable yet because this is a new situation for me and I don't want to
> mess up.
> Thanks as always,
> Eric
|||Thanks, Aaron, that much worked. But now what do I do with the
"myTable_backup" table? Is there some way to drop it? If I try now,
I get the following error message:
Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 18 in database 'myDatabase'.
Connection Broken
Seems like there's still something wrong with my database that needs
to be fixed. Any ideas?
|||If I were to play it safe, I would create a new database, transfer all the
*good* objects and data there, drop the old database and rename the new one.
(You might now want to consider investing in a backup/recovery plan.)
"Eric Bragas" <ericbragas@.yahoo.com> wrote in message
news:f4e3dfc4-d6d4-4e08-9de4-a0bd93856b74@.e25g2000prg.googlegroups.com...
> Thanks, Aaron, that much worked. But now what do I do with the
> "myTable_backup" table? Is there some way to drop it? If I try now,
> I get the following error message:
> Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 18 in database 'myDatabase'.
> Connection Broken
>
> Seems like there's still something wrong with my database that needs
> to be fixed. Any ideas?
|||Thanks, Aaron, I like that idea about moving the good objects to
another database. Seems like there must be a way to "fix" the
existing database, but perhaps not. I'm going to take your advice.
About the backup/recovery necessity, I have a hard time getting the
managers to recognize the possibility of a problem and dealing with it
before it happens. We have no space left on the HDD, very strange
database config's, and no written backup policy in place that I know
of, but we shove on like Stampeders. But hey, thanks for the advice!
Eric Bragas
|||> About the backup/recovery necessity, I have a hard time getting the
> managers to recognize the possibility of a problem and dealing with it
> before it happens.
Do they know what you're spending your time on right now?
A