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

How bad is this??

After detaching a database and deleting the logfile, I use
sp_attach_single_file_db to attach the database and get the following error:
Could not open new database 'Orders'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'F:\'Orders' may be
incorrect.
Is there any way to recover without the log file now?
> After detaching a database and deleting the logfile
Can I ask why you would ever do this?
http://www.aspfaq.com/
(Reverse address to reply.)
|||You have any backups of the database or database files?
"Bryan" <bryan.charlton@.api-wi.com> wrote in message
news:%23IVTepRZEHA.3016@.tk2msftngp13.phx.gbl...
> After detaching a database and deleting the logfile, I use
> sp_attach_single_file_db to attach the database and get the following
error:
> Could not open new database 'Orders'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'F:\'Orders' may be
> incorrect.
> Is there any way to recover without the log file now?
>
>
>
>
>
|||Also, is your Resume up to date?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Joe" <JoeD777@.lycos.com> wrote in message
news:uZ7y3yRZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> You have any backups of the database or database files?
>
> "Bryan" <bryan.charlton@.api-wi.com> wrote in message
> news:%23IVTepRZEHA.3016@.tk2msftngp13.phx.gbl...
> error:
>
|||Yes, I have a backup that I can restore from. This is my only option I take
it?
"Joe" <JoeD777@.lycos.com> wrote in message
news:uZ7y3yRZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> You have any backups of the database or database files?
>
> "Bryan" <bryan.charlton@.api-wi.com> wrote in message
> news:%23IVTepRZEHA.3016@.tk2msftngp13.phx.gbl...
> error:
>
|||I have actually done this more times than I can count when moving
development databases from server to server and have never had a problem
(until now).
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OQl33yRZEHA.716@.TK2MSFTNGP11.phx.gbl...
> Can I ask why you would ever do this?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||> I have actually done this more times than I can count
Move the data and the log file. Or, backup and restore. What you're doing
is quite similar roulette, and you've merely been on a lucky roll until this
spin.
http://www.aspfaq.com/
(Reverse address to reply.)
|||> Yes, I have a backup that I can restore from. This is my only option I
take
> it?
Or, you can call PSS.
|||Isn't it possible that the physical file name really is incorrect, as the
error says? Are you sure it isn't 'F:\'Orders.mdf' instead of 'F:\'Orders'?
You don't need the log file to attach the DB.
-John Oakes
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Omp91KSZEHA.1048@.tk2msftngp13.phx.gbl...
> Move the data and the log file. Or, backup and restore. What you're
doing
> is quite similar roulette, and you've merely been on a lucky roll until
this
> spin.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||theoretically he should have been alright
Inside SQL 2000 "One benefit of using the sp_detach_db procedure is that SQL
Server will know that the database was cleanly shut down, and the log file
does not have to be available to attach the database. SQL will build a new
log file for you. This can be a quick way to shrink a log file that has
become much larger than you would like, because the new log file that
sp_attach_db creates for you will be the minimum sizeless than 1 MB. Note
that this trick for shrinking the log will not work if the database has more
than one log file."
But you're right he should have been a bit more careful on a production
server. P45 time!
Br,
Mark Broadbent
mcdba , mcse+i
=============
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Omp91KSZEHA.1048@.tk2msftngp13.phx.gbl...
> Move the data and the log file. Or, backup and restore. What you're
doing
> is quite similar roulette, and you've merely been on a lucky roll until
this
> spin.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>

How bad is this??

After detaching a database and deleting the logfile, I use
sp_attach_single_file_db to attach the database and get the following error:
Could not open new database 'Orders'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'F:\'Orders' may be
incorrect.
Is there any way to recover without the log file now?> After detaching a database and deleting the logfile
Can I ask why you would ever do this?
http://www.aspfaq.com/
(Reverse address to reply.)|||You have any backups of the database or database files?
"Bryan" <bryan.charlton@.api-wi.com> wrote in message
news:%23IVTepRZEHA.3016@.tk2msftngp13.phx.gbl...
> After detaching a database and deleting the logfile, I use
> sp_attach_single_file_db to attach the database and get the following
error:
> Could not open new database 'Orders'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'F:'Orders' may be
> incorrect.
> Is there any way to recover without the log file now?
>
>
>
>
>|||Also, is your Resume up to date?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Joe" <JoeD777@.lycos.com> wrote in message
news:uZ7y3yRZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> You have any backups of the database or database files?
>
> "Bryan" <bryan.charlton@.api-wi.com> wrote in message
> news:%23IVTepRZEHA.3016@.tk2msftngp13.phx.gbl...
> error:
>|||Yes, I have a backup that I can restore from. This is my only option I take
it?
"Joe" <JoeD777@.lycos.com> wrote in message
news:uZ7y3yRZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> You have any backups of the database or database files?
>
> "Bryan" <bryan.charlton@.api-wi.com> wrote in message
> news:%23IVTepRZEHA.3016@.tk2msftngp13.phx.gbl...
> error:
>|||I have actually done this more times than I can count when moving
development databases from server to server and have never had a problem
(until now).
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OQl33yRZEHA.716@.TK2MSFTNGP11.phx.gbl...
> Can I ask why you would ever do this?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||> I have actually done this more times than I can count
Move the data and the log file. Or, backup and restore. What you're doing
is quite similar roulette, and you've merely been on a lucky roll until this
spin.
http://www.aspfaq.com/
(Reverse address to reply.)|||> Yes, I have a backup that I can restore from. This is my only option I
take
> it?
Or, you can call PSS.|||Isn't it possible that the physical file name really is incorrect, as the
error says? Are you sure it isn't 'F:'Orders.mdf' instead of 'F:'Orders'?
You don't need the log file to attach the DB.
-John Oakes
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Omp91KSZEHA.1048@.tk2msftngp13.phx.gbl...
> Move the data and the log file. Or, backup and restore. What you're
doing
> is quite similar roulette, and you've merely been on a lucky roll until
this
> spin.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||theoretically he should have been alright
Inside SQL 2000 "One benefit of using the sp_detach_db procedure is that SQL
Server will know that the database was cleanly shut down, and the log file
does not have to be available to attach the database. SQL will build a new
log file for you. This can be a quick way to shrink a log file that has
become much larger than you would like, because the new log file that
sp_attach_db creates for you will be the minimum sizeless than 1 MB. Note
that this trick for shrinking the log will not work if the database has more
than one log file."
But you're right he should have been a bit more careful on a production
server. P45 time!
Br,
Mark Broadbent
mcdba , mcse+i
=============
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Omp91KSZEHA.1048@.tk2msftngp13.phx.gbl...
> Move the data and the log file. Or, backup and restore. What you're
doing
> is quite similar roulette, and you've merely been on a lucky roll until
this
> spin.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>