Re: Sysindex table problems
From: Paul S Randal [MS] (prandal_at_online.microsoft.com)
Date: 10/04/04
- Next message: dch: "Re: SQL 2000 with SP3: Profiler tracing does not capture any data"
- Previous message: Mike: "NULL values handled differently in stored procedure"
- In reply to: anonymous_at_discussions.microsoft.com: "Re: Sysindex table problems"
- Next in thread: Chester: "Re: Sysindex table problems"
- Reply: Chester: "Re: Sysindex table problems"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 4 Oct 2004 12:26:39 -0700
The 8968 would also do it. So, yes, you hit exactly the problem I described
in my previous email. If you can run a clean DBCC CHECKALLOC then its gone
away with the last shrink otherwise you'll need to run repair as I described
(on CHECKALLOC, not CHECKTABLE).
AS some background, the reason this single error causes so many reported
errors is due to the way the consistency cheks work. The bad IAM page is
part of the text IAM chain for sysindexes. When the bad IAM page is
encountered, none of the pages for that IAM chain are processed and so all
the text objects contained within the IAM chain show up as broken.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
<anonymous@discussions.microsoft.com> wrote in message
news:386f01c4aa41$d0cdc940$a401280a@phx.gbl...
> We did not find error 8964 in the error log or output of
> the maintenance job for the database.
>
> Below is what we see:
>
> The integrity check part of the database weekly
> maintenance plan returned the following results:
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB
> found 1 allocation errors and 7533 consistency errors in
> table 'sysindexes'
>
> When we run:
> DBCC CHECKTABLE (db.dbo.sysindexes, repair_allow_data_loss)
>
> we got back "found 7453 errors and repaired 0 errors."
>
> Late last night I decided to run the db optimization part
> of the weekly maintenance plan hoping that having it try
> to clean-up the pages and indexes would fix the issue
> below is what we for back:
>
> [1] Database database: Index Rebuild (leaving 10%% free
> space)...
> - this ran for Execution Time: 7 hrs, 13 mins, 36 secs
> (its a 184gig DB)
>
> [2] Database database: Removing unused space from the
> database files (if database size is more than 65000 MB).
> Reducing free space to 10 percent of data...[Microsoft SQL-
> DMO (ODBC SQLState: 42000)] Error 8968: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Table error: IAM page
> (1:4560407) (object ID 2, index ID 255) is out of the
> range of this database.
> Deleting old text reports... 1 file(s) deleted.
>
> End of maintenance plan 'database Maintenance Plan' on
> 10/4/2004 6:44:47 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
> But now when I run
> DBCC CHECKTABLE (db.dbo.sysindexes)
>
> we get no errors - it looks fine/normal
>
> could and would the optimization job fix this problem??
>
>
> >-----Original Message-----
> >Please check the CHECKDB output for a single 8964 error.
> If there is one,
> >this is likely caused by a known (benign) bug in DBCC
> SHRINKDATABASE that
> >very rarely occurs and causes this error. If there is
> one, you should be
> >able to run DBCC CHECKALLOC WITH REPAIR_ALLOW_DATA_LOSS
> (it will not lose
> >any data) to fix the 8964 error. After that is fixed, the
> 8929 errors should
> >disappear. No guarantees here, but it seems likely this
> is what you have
> >hit.
> >
> >If you're uncomfortable doing this, you should call PSS
> and explain that
> >you've been told by me that the symptoms look like the
> problem in
> >SRX020812600531.
> >
> >Please let me know how you get on.
> >
> >Regards.
> >
> >--
> >Paul Randal
> >Dev Lead, Microsoft SQL Server Storage Engine
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >
> >"Chester" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:40be01c4aa1e$ac1d5200$a601280a@phx.gbl...
> >> We are running SQL2000 and encountered over 7000 errors,
> >> similar
> >> to those below, when running CHECKDB.
> >>
> >> DBCC results for 'sysindexes'.
> >> Server: Msg 8929, Level 16, State 1, Line 1
> >> Object ID 2: Errors found in text ID 196608 owned by
> data
> >> record identified by RID = (1:24:1) id = 1 and indid =
> 2.
> >> Server: Msg 8929, Level 16, State 1, Line 1
> >> Object ID 2: Errors found in text ID 262144 owned by
> data
> >> record identified by RID = (1:24:2) id = 1 and indid =
> 3.
> >> Server: Msg 8929, Level 16, State 1, Line 1
> >> Object ID 2: Errors found in text ID 12517376 owned by
> >> data record identified by RID = (1:24:3) id = 2 and
> indid
> >> = 1.
> >>
> >> Is there a way to fix the sysindexes table without
> doing a
> >> restore?
> >> Would trying to run DBCC DBREINDEX('sysindexes') help?
> >>
> >> TIA.
> >
> >
> >.
> >
- Next message: dch: "Re: SQL 2000 with SP3: Profiler tracing does not capture any data"
- Previous message: Mike: "NULL values handled differently in stored procedure"
- In reply to: anonymous_at_discussions.microsoft.com: "Re: Sysindex table problems"
- Next in thread: Chester: "Re: Sysindex table problems"
- Reply: Chester: "Re: Sysindex table problems"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
