SQL 2000 corrupted record! can't delete table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL 2000 corrupted record! can't delete table

Author

Topic

John_Idol
Starting Member

39 Posts

Posted - 2008-05-09 : 07:33:05

Hi All,

I have just one corrupted record on a table: I copied everything else to another table but I can't delete or rename the old one!

Is restoring the whole DB my only way out there?

Any help or suggestion would be much appreciated!

Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 07:36:26

Have you tried DBCC CheckDB?

E 12°55'05.25"
N 56°04'39.16"

John_Idol
Starting Member

39 Posts

Posted - 2008-05-09 : 07:37:55

Nope, I've never used it - can you plz point me in the right direction with that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 07:42:03

[code]
DBCC CHECKDB
[
(
'database_name' | database_id | 0
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
)
]
[ WITH {
[ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ] | [ , [ DATA_PURITY ] ]
}
]
[/code]

Run

DBCC CHECKDB WITH ALL_ERRORMSGS

E 12°55'05.25"
N 56°04'39.16"

John_Idol
Starting Member

39 Posts

Posted - 2008-05-09 : 07:46:18

Thanks!

Shall I go first with dbcc checktable if I am farly sure about which one the corrupted table is?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 07:48:58

You could do that first.
It is not necessary the only problem you have with the database.

Try table first, and the database.

DBCC CHECKTABLE 
(
        'table_name' | 'view_name'
    [ , NOINDEX 
    | index_id 
    | { REPAIR_ALLOW_DATA_LOSS 
    | REPAIR_FAST 
    | REPAIR_REBUILD } 
    ] 
)
    [ WITH 
        { [ ALL_ERRORMSGS ]
          [ , [ NO_INFOMSGS ] ]
          [ , [ TABLOCK ] ] 
          [ , [ ESTIMATEONLY ] ] 
          [ , [ PHYSICAL_ONLY ] ] 
        }
    ]

E 12°55'05.25"
N 56°04'39.16"

John_Idol
Starting Member

39 Posts

Posted - 2008-05-09 : 09:23:12

Tried both CheckTable and CheckDB:

DBCC CHECKTABLE('TABLE_NAME', REPAIR_REBUILD) or DBCC CHECKTABLE('DB_NAME', REPAIR_REBUILD)

but it keeps running for 40 mins and nothing happens (no feedback), so I stopped it. DB is about 6 Gigs in size.
Should I wait more?

Thanks for helping!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-05-09 : 11:55:34

Do you see progress/activity using Profiler while DBCC is running??
You may wish to contact member PaulRandal (ex-Microsoft) - he wrote the DBCC commands.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-05-09 : 12:14:43

I've run a DBCC CHECKDB on a 250Gb database and it ran in under an hour so what you're reporting doesn't seem quite right. Re-post this (or if the superpowers can move this) to the data corruption forum so that Paul will see this (as suggested by Andrew).

Terry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources