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
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Corrupt Table

Author  Topic 

Kleber
Yak Posting Veteran

67 Posts
Posted - 2003-12-10 : 14:50:01
Hi all

I have a SQL Server 7.0 with a corrupt table, when i try to drop this table, it gives me this error:

ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 8 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

when I execute a DBCC CHECKTABLE i got the same error

When I execute a DBCC CHECKDB with a REPAIR_ALLOW_DATA_LOSS I got the following:

Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:2418) with latch type SH. sysindexes failed.
Server: Msg 8939, Level 16, State 1, Line 1
Table Corrupt: Object ID 2, index ID 0, page (1:2418). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 61768 and 8118.
DBCC results for 'dtbsaldo'.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sysindexes' (object ID 2).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'DtbSaldo'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DtbSaldo repair_allow_data_loss).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

And for now, I cannot drop the table, correct the table and my database isn't working....

Any help?

Kleber Gomes
Brazil

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-12-10 : 14:54:33
RESTORE the database from the last full backup. Then RESTORE the transaction logs up until the point of failure.

Tara
Go to Top of Page

Kleber
Yak Posting Veteran

67 Posts
Posted - 2003-12-10 : 14:58:40
I'd like to restore, but, there is no backup to restore, this database was not of our scope of work, and it becomes now, with this problem and without backup or any maintenace plan. So... If you have any other solution, I will appreciate it.

Regards
Kleber Gomes
Brazil
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-12-10 : 15:02:28
Can you get at the data from the other tables through Query Analyzer? If so, I would start bcping the data out to files that way you save some data. Hopefully you can do this for most tables. You could also use DTS if you are not familiar with bcp.

Tara
Go to Top of Page

Kleber
Yak Posting Veteran

67 Posts
Posted - 2003-12-10 : 15:09:59
Yes, I can export data of other tables. And i was thinking to do it, but i think it will be a very time consume task. So I was trying to drop the table, recreate and repopulate it with a txt file that have the most data of it.

But i couldn't correct it or drop it.... so here i am... begging for help.

Kleber Gomes
Brazil
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-12-10 : 15:11:46
Start exporting. You can easily script out the bcp commands using dynamic sql.

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-12-10 : 15:12:43
Also, have you checked the Event Log to see why the corruption occurred? You should not try to bring up the database on the same server because something is wrong there.

Tara
Go to Top of Page

Kleber
Yak Posting Veteran

67 Posts
Posted - 2003-12-10 : 15:22:40
Exporting gives me another problem. How may i export my store procedures, (to this database we have some hundreds of it)?
And this corruption accured exactly today. It is a Bank and when database fall, the Bank stop to work and they called me.

Kleber Gomes
Brazil
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2003-12-10 : 15:27:59
Have you tried dropping the indexes on this table? My guess is that it will allow you to drop all of the indexes except for one of them.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kleber
Yak Posting Veteran

67 Posts
Posted - 2003-12-10 : 15:31:23
There is no indexes to this table. There is the most strange on it!

Kleber Gomes
Brazil
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2003-12-10 : 15:35:44
Do a reindex on sysindexes

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kleber
Yak Posting Veteran

67 Posts
Posted - 2003-12-10 : 15:46:38
I think I didn't understand. Why (and how) should i do it?

Kleber
The Newbie
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2003-12-10 : 16:09:51
Table Corrupt: Object ID 2, index ID 0, page (1:2418). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 61768 and 8118.


This is referring to the sysindexes table. There is a type of corruption in SQL Server where the space of one object will show up in the space of another object. The only index on sysindexes is the index that is objectid2, indexid0.

It's worth a try to do a DBCC DBREINDEX('sysindexes') and see the results of this. It will basically drop the index and rebuild it, making the table, and database inaccessible for a short time. If this is causing the problem, you might be able to do a dbcc checkdb on the other table and get a working database.

Theoretically speaking of course. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-12-10 : 16:16:57
This is for a bank system and there is no backup?!!! Unbelievable.

To export objects, you can use the generate sql script wizard in Enterprise Manager. Just right click on your database, go to all tasks, then generate sql script. But do what the mean old DBA says first.

Tara
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2003-12-10 : 16:17:41
I hope it's not my bank. :) Then I would just be a mean old man.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kleber
Yak Posting Veteran

67 Posts
Posted - 2003-12-11 : 06:28:43
Well... it is really a bank, and we have more than 100 server over all region. And, believe me, there is no backup. Here we fight with things like that. We show to CEOs, what we neeed and why... but imagine a SQL Server in a machine with 4.5Gb HD, 128Mb Ram and a clock of 233Mhz... it is the majority of our servers and SQL server runs concurrently with a lot of other services on this machine. There is no local DAT tape, there is nos local hard disk space and we have a link that is 9.600bps when it is on good days....

Yes, umbelieveble... but is my reallity... does someone wants to work here with me?

Kleber
Brazil
Go to Top of Page

GreatInca
Posting Yak Master

102 Posts
Posted - 2003-12-11 : 14:03:16
I'm not much better. Got backups but they never really leave the machine. Maybe when sombody feels like downloading 60GB(Unzipped)/12GB(Zipped) (and growing) over the internet once or twice a year. Helps with currupted tables but not in the case of flooding or a double-disk failure. Unfortunatly (for backups) I don't control any hardware or hosting, and there is just too much data to haul over the internet for even log shipping (bandwidth bill).

****************************************
Check out my pictures at http://www.pbase.com/GreatInca

Check out my company's site!
http://www.jobing.com (Market picker for first visit)
http://phoenix.jobing.com (corporate home market if you don't live in a covered market)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-12-11 : 14:12:29
GreatInca, you should really check out SQL Litespeed. It doesn't cost that much and will significantly reduce the backup file size plus length of time it takes to backup the database.

We are purchasing it for a 4CPU box with SQL Enterprise Edition and it only costs 1500 bucks. For 2CPUs, it's 1300 or something like that. It is very cost beneficial for us because it is going to save us a lot of SAN disk space which is quite expensive. I am told by our server guys that just by giving them back one disk from the SAN that the product has already paid for itself.

Tara
Go to Top of Page

GreatInca
Posting Yak Master

102 Posts
Posted - 2003-12-11 : 14:17:55
Already have the evaluation version. Compression is poorer than zip. Backup time with sqllitespeed is the same as an uncompressed-only backup (but much faster than uncompressed backup then zip). Gregg is getting SQLZip for comparison but has been procrastinated enough to think it won't happen. SQLZip costs half as much but has poorer integration (but probably don't need it). Not sure about backup time or compression (sqlzip might not have deflate64)

****************************************
Check out my pictures at http://www.pbase.com/GreatInca

Check out my company's site!
http://www.jobing.com (Market picker for first visit)
http://phoenix.jobing.com (corporate home market if you don't live in a covered market)
Go to Top of Page

chadmat
The Chadinator

1974 Posts
Posted - 2003-12-11 : 18:43:23
I would call MS SQL Support. They should be able to help you figure out how to recover. Though you may get a hard time for not having backups.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.
Go to Top of Page

GreatInca
Posting Yak Master

102 Posts
Posted - 2003-12-11 : 19:30:14
Oh yeah another competitor to the automated SQL LiteSpeed and SQL zip and the Manual PkZipC is WinRar, which is manual also. But it is the king of compression ratio, but also the slowest. SQLLitespeed (in my experience) is equal to regular compressed backup. DOn't know SQLZip yet. PKZipC (PKWare V4.5+) at compression Level 3 needs 3x the non-compressed backuptime to finish. WinRar needs 5-6x the non-compressed backuptime to finish but compresses more than double than PkZipC (with compression options maxxed out especially advanced settings for text files). PKZipC (@ compression Level 3 out of 10 using Deflate64) compresses about 10-15% smaller than SQLLiteSpeed. WinRar is your king for your small <100MB databasees if you write your own automation code.

****************************************
Check out my pictures at http://www.pbase.com/GreatInca

Check out my company's site!
http://www.jobing.com (Market picker for first visit)
http://phoenix.jobing.com (corporate home market if you don't live in a covered market)
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site