Msg8939 level16 state1 line1 & errors in sysindexe

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

Data Corruption Issues

Msg8939 level16 state1 line1 & errors in sysindexe

Author

Topic

Del511
Starting Member

8 Posts

Posted - 2010-10-01 : 08:58:15

Hello! I am a systems engineer that uses SQL and occasionaly fixes SQL related issues. I do the later mostly by reading articles and blogs. Our accounting db is corrupt and has been for over 3 years so clean backups do not exist. The db is SQL 2000 SP3. I need to fix the issue and then move the db to a SQL 2005 server w/latest SP. Please help! The following are edited results from DBCC Checkdb

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2, index ID 255: Page (1:2686) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2, index ID 255, page (1:2686). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
DBCC results for 'DELTEKTE'.
CHECKDB found 0 allocation errors and 2 consistency errors in database 'DELTEKTE'.
DBCC results for 'sysobjects'.
There are 3290 rows in 54 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 4814 rows in 220 pages for object 'sysindexes'.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'sysindexes' (object ID 2).
DBCC results for 'syscolumns'.
There are 27788 rows in 632 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 2839 rows in 1012 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 141 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 23 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 10408 rows in 51 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 0 rows in 0 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DELTEKTE ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Thanks in advance!

-Blessings :-)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-01 : 10:47:25

So do you actually have any backups that will restore?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-01 : 11:17:50

Corrupt for 3 years and no one bothered to do anything? Wow! That's a new record.

Please run the following and post the full, complete and unedited output.


DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

--
Gail Shaw
SQL Server MVP

Del511
Starting Member

8 Posts

Posted - 2010-10-04 : 10:58:53

Happy Monday! :-)

To answer Transact Charlie, There are no backsups. I was told the backups starting failing about a year ago so they did not bother with them.

For GilaMonster:
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 5451415552 owned by data record identified by RID = (1:22633:8) id = 1872829834 and indid = 6.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 5500829696 owned by data record identified by RID = (1:32937:14) id = 540633069 and indid = 7.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 5500895232 owned by data record identified by RID = (1:32937:15) id = 540633069 and indid = 8.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 5500960768 owned by data record identified by RID = (1:32937:16) id = 540633069 and indid = 9.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 5501026304 owned by data record identified by RID = (1:32937:17) id = 540633069 and indid = 10.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 5501091840 owned by data record identified by RID = (1:32937:18) id = 540633069 and indid = 11.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2, index ID 255: Page (1:2686) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2, index ID 255, page (1:2686). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:2686), slot 0, text ID 5451415552 is referenced by page (1:3389), slot 21, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:2686), slot 2, text ID 5500829696 is referenced by page (1:32937), slot 14, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:2686), slot 4, text ID 5500895232 is referenced by page (1:32937), slot 15, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:2686), slot 6, text ID 5500960768 is referenced by page (1:32937), slot 16, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:2686), slot 8, text ID 5501026304 is referenced by page (1:32937), slot 17, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:2686), slot 10, text ID 5501091840 is referenced by page (1:32937), slot 18, but was not seen in the scan.
CHECKDB found 0 allocation errors and 14 consistency errors in table 'sysindexes' (object ID 2).
CHECKDB found 0 allocation errors and 14 consistency errors in database 'DELTEKTE'.

I see I'm back to 14 consistency errors in the sysindexes table. What can I do?

-Blessings :-)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-04 : 14:08:24

Fortunately there is a manual way to repair these.
http://sqlinthewild.co.za/index.php/2009/08/24/stats-blob-corruptions/

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-04 : 15:07:37

quote:
Originally posted by Del511

I was told the backups starting failing about a year ago so they did not bother with them.

Is the data not that important to them? Why wouldn't they have figured out why the backups were failing?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Del511
Starting Member

8 Posts

Posted - 2010-10-04 : 15:39:35

I don't know if you know what Deltek Time and Expense is but it's a critical accounting application. It does a lot of things but the most important thing it does for me is to ensure I get paid on time. LOL I work on a contract so I don't deal with corporate IT. The execs pulled me into this mess about 2 weeks ago. The corporate IT staff noticed that the harddrives were failing on the original server about 2 1/2 years ago so they imaged the server (bad data and all) and put in on new hardware. When the apps really started having issues and the backups started to fail (about 18 months ago) they "looked at it" saw that it was corrupt but decided it was too much work to fix so they ignored it. The COO wanted some alerts set in March of this year and after being ignored for 6 1/2 months, he asked me to investigate. In a nutshell, I found that the issues started sometime in 2007 and after a lot of work, I'm down the last 14 errors. I can't thank you guys enough! I am going to do what Gail says and I will let you know how everything turns out.

-Blessings :-)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-04 : 15:57:36

Wow. Only accounting and payroll...nothing important...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-05 : 04:46:35

quote:
Originally posted by Del511

I don't know if you know what Deltek Time and Expense is but it's a critical accounting application. It does a lot of things but the most important thing it does for me is to ensure I get paid on time. LOL I work on a contract so I don't deal with corporate IT. The execs pulled me into this mess about 2 weeks ago. The corporate IT staff noticed that the harddrives were failing on the original server about 2 1/2 years ago so they imaged the server (bad data and all) and put in on new hardware. When the apps really started having issues and the backups started to fail (about 18 months ago) they "looked at it" saw that it was corrupt but decided it was too much work to fix so they ignored it. The COO wanted some alerts set in March of this year and after being ignored for 6 1/2 months, he asked me to investigate. In a nutshell, I found that the issues started sometime in 2007 and after a lot of work, I'm down the last 14 errors. I can't thank you guys enough! I am going to do what Gail says and I will let you know how everything turns out.

-Blessings :-)

SO.......... MUCH........... FAIL!!!

At least you are in good hands with Gail. As soon as you can take a backup. Do you have a recent image of the server as a last gasp emergency backup plan?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-05 : 05:55:01

One thing, NB, please.
These MUST be fixed before upgrading to 2005. On 2000 there's a chance of fixing, on 2005 not so much (due to how the system tables have changes)

Also note that the fix I gave you may fail. If it does, there's no other solution than scripting out the objects, exporting the data and recreating the DB.

--
Gail Shaw
SQL Server MVP

Del511
Starting Member

8 Posts

Posted - 2010-10-05 : 15:57:01

If Gail ever comes to Washington DC, I'm taking her out to a dinner of her choice! Charlie, I have an image of the error ridden server. I attempted to do what Gail said, which is to run the following query:

SELECT object_name(id) as TableName, name as IndexName, IndexProperty(id, name, 'IsStatistics') AS IsColumnStatistics
FROM sysindexes
WHERE (id = 540633069 and indid = 7)
I got this result:
DESKTOP_TASKS _WA_Sys_S_TASK_CD_203967ED 1
Then I ran the drop command with both index and statistics
Drop statistics desktop_tasks._WA_Sys_S_TASK_CD_203967ED and got the same error msg:
Server: Msg 3701, Level 11, State 6, Line 1
Cannot drop the statistics 'desktop_tasks._WA_Sys_S_TASK_CD_203967ED', because it does not exist in the system catalog.
Therefore, I have no choice but to script out the entire db and re-creating it. In the case that I lose data, is there a way to get the deltas so the accounting department can input the missing data? I asked if they had a way to know if stuff was missing and the answer was no.

-Blessings :-)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-05 : 16:54:00

Something like Redgate's SQLDataCompare will make the job of data comparison easy.

You shouldn't lose anything. The bad stats won't prevent data from being exported (bcp would be my preference because of simplicity).

p.s. If I cashed in all the offers of lunch/dinner/drinks from various forum threads I wouldn't have to buy a meal for an entire US visit. :)
No trips to Washington DC planned, just one to Washington state.

--
Gail Shaw
SQL Server MVP

elliswhite
Starting Member

36 Posts

Posted - 2014-05-10 : 01:51:37

hi
The main reason of the occurence of this error is due to harware failure or corruption and you are suffering it since 3 years so i recommend you to use specific and trusted SQL Server database recovery software.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources