December 1, 2008 at 9:37 pm
Hi,
I got the below error, when doing a database shrink on SQL2000EE, SP4; ver:8.00.2039:
***********************************************************************************************
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:12/1/2008
Time:5:21:15 PM
User:SQLD\AGENTU
Computer:RIPLEY
Description:
Error: 644, Severity: 21, State: 5
Could not find the index entry for RID 'XXXXXXXXXXXXXXXXXXX' in index page (1:20951542), index ID 2, database 'LG_REFRIDGE'.
***********************************************************************************************
Action:
Index ID 2 (Looks like non-clustered index; need to drop and rebuild), I am Currently Running, 'DBCC CHECKDB('LG_REFRIDGE') WITH ALL_ERRORMSGS, NO_INFOMSGS'. The Database is 150GB so will take some time to finish.
Question:
Is there a faster way to find out where(in which table or which index) the corruption happened?
December 1, 2008 at 9:49 pm
Yes- if this is a 2000 database (guessing so because of the forum you posted in):
DBCC TRACEON (3604) -- just directs the DBCC PAGE output to the current connection
GO
DBCC PAGE ('LG_REFRIDGE', 1, 20951542, 1)
GO
And grab the m_objId from the Page Header output. OBJECT_NAME ( ) is the table with the corruption and you can do a DBCC CHECKTABLE on that table. I'd still do a full DBCC CHECKDB though as some of the rest of the database might be damaged.
Post the results when you have them.
PS If this is a 2005 database, the DBCC PAGE output will say what the actual object ID is in a line starting "Metadata: object ID". The metadata changed from 2000->2005 and I rewrote DBCC PAGE to cope with it.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 1, 2008 at 10:27 pm
Thanks for the inputs. And yes, it is a SQL Server 2000 Enterprise Edition, SP4. I will post the results once, I have them.
Regards,
December 1, 2008 at 10:44 pm
DBCC TRACEON (3604) -- just directs the DBCC PAGE output to the current connection
GO
DBCC PAGE ('LG_REFRIDGE', 1, 20951542, 1)
GO
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:20951542)
------------------
BUFFER:
-------
BUF @0x0123D280
---------------
bpage = 0x57A04000 bhash = 0x00000000 bpageno = (1:20951542)
bdbid = 43 breferences = 0 bstat = 0x9
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x57A04000
----------------
m_pageId = (1:20951542) m_headerVersion = 1 m_type = 2
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId = 530152984 m_indexId = 2 m_prevPage = (1:21153897)
m_nextPage = (1:21153401) pminlen = 18 m_slotCnt = 352
m_freeCnt = 0 m_freeData = 7488 m_reservedCnt = 0
m_lsn = (3623140:255:34) m_xactReserved = 0 m_xdesId = (0:17757905)
m_ghostRecCnt = 0 m_tornBits = 303568666
Allocation Status
-----------------
GAM (1:20449280) = ALLOCATED
SGAM (1:20449281) = NOT ALLOCATED
PFS (1:20947920) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL
DIFF (1:20449286) = CHANGED
ML (1:20449287) = NOT MIN_LOGGED
DATA:
-----
Slot 0, Offset 0x60
-------------------
Record Type = INDEX_RECORD
Record Attributes = NULL_BITMAP
57A04060: 0607d916 1a4e181e d9e90000 00010136 ......N.....6...
57A04070: 0006001d 00 .....
USE LG_REFRIDGE
GO
SELECT OBJECT_NAME(530152984)
RESULT:
OUT_54
QUESTION:
I am still keeping the DBCC CHECKDB run on. Will post those results once I get them. Anything, that I should be getting ready before, the results come in?
December 1, 2008 at 11:37 pm
Nope - but that's the name of the table where the corruption happened.
Do you have backups?
PS You're in India - which is PST + 12.5 - I'm heading to bed - back in 7-8 hours. Gail (GilaMonster) is only about 4 hours behind you.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 1, 2008 at 11:57 pm
Sorry, Completely missed answering the question. Yes, we do have backups configured via SnapManager. And, I should have mentioned this earlier; the corruption happened while doing a database shrink.
Thanks for your help Paul!:)
December 2, 2008 at 1:01 am
So,Finally DBCC finishes execution and the output is posted below:
Consolidated:
----------------------------------------------------------------------------------------------------------------
CHECKDB found 0 allocation errors and 201 consistency errors in table 'OUTP_54' (object ID 434152642).
CHECKDB found 0 allocation errors and 197 consistency errors in table 'OUT_54' (object ID 530152984).
CHECKDB found 0 allocation errors and 398 consistency errors in database 'LG_REFRIDGE'.
repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (LG_REFRIDGE ).
----------------------------------------------------------------------------------------------------------------
FirstTableError:
----------------------------------------------------------------------------------------------------------------
DBCC_MSG:
CHECKDB found 0 allocation errors and 201 consistency errors in table 'OUTP_54' (object ID 434152642).
Sample_Error_String:
Table error: Table 'OUTP_54' (ID 434152642). Missing or invalid key in index 'IX_OUTP_54' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 17
Data row (1:20693920:1) identified by (RID = (1:20693920:1) ) has index values
(YEAR = 2009 and MONTH = -1.255420352E+058 and DAY = 1.409001293E-232 and HOUR = 2.196825851E-308 and UPID = 4044 and IT_NO = 1).
Server: Msg 8951, Level 16, State 1, Line 17
----------------------------------------------------------------------------------------------------------------
SecondTableError:
----------------------------------------------------------------------------------------------------------------
DBCC_MSG:
CHECKDB found 0 allocation errors and 197 consistency errors in table 'OUT_54' (object ID 530152984).
Sample_Error_String:
Table error: Table 'OUT_54' (ID 530152984). Missing or invalid key in index 'IDXOUT1_54' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 17
Data row (1:21229048:4) identified by (RID = (1:21229048:4) ) has index values
(YEAR = 2009 and MONTH = 1.344974624E-284 and DAY = 6.953355808E-309 and HOUR = 2.716154613E-311 and UPUID = 5).
Server: Msg 8951, Level 16, State 1, Line 17
----------------------------------------------------------------------------------------------------------------
Now, the Questions are:
1. Are the repair options useful?
2. Should I user repair_fast as suggested or should I use, repair_rebuild?
Regards,
December 2, 2008 at 1:16 am
hemanth.damecharla (12/1/2008)
And, I should have mentioned this earlier; the corruption happened while doing a database shrink.
Not overly likely. More likely the shrink picked up corruption that was already there.
When the full checkDB results are available, please post them. That will determine the course of action.
Do you have checkDB running as a regular job? If so, when did it last run clean?
Can you check the system event log and any logs for your RAID/SAN (whichever you're running on) see if there are any errors or warnings.
p.s. (unrelated to the corruption)
Why are you shrinking your database on a regular basis? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
Please read the following blog post and also the two that it links to.
http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2008 at 1:27 am
hemanth.damecharla (12/2/2008)
Sample_Error_String:
Please post the entire output (or save to a text file and attach it)
There may be stuff that you cut out that you think isn't important, but is.
Hold off on any repairs for now. I personally prefer not to use the repairs (most of the time). They certainly do work though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2008 at 1:37 am
When the full checkDB results are available, please post them. That will determine the course of action.
Attached!(sorry, wrong attachment)
Do you have checkDB running as a regular job? If so, when did it last run clean?
It is an adhoc job. So, the last clean run was about a month ago.
Can you check the system event log and any logs for your RAID/SAN (whichever you're running on) see if there are any errors or warnings.
None in the past one week, that I can see.
p.s. (unrelated to the corruption)
Why are you shrinking your database on a regular basis? Databases tend to grow as more data gets put in them. It's in their nature.
Not my choice 🙂 but, when you approach 1.8TB for every 2 databases on the server business wants us to manage them with the available disk space. So, I guess the guys who worked before me set up jobs for this.
Regards,
December 2, 2008 at 1:44 am
hemanth.damecharla (12/2/2008)
p.s. (unrelated to the corruption)
Why are you shrinking your database on a regular basis? Databases tend to grow as more data gets put in them. It's in their nature.
Not my choice 🙂 but, when you approach 1.8TB for every 2 databases on the server business wants us to manage them with the available disk space. So, I guess the guys who worked before me set up jobs for this.
Regards,
May I suggest that you point out to management that by forcing you to shrinking the DBs, they are seriously impacting both performance and availability of the DBs and that you really, really need more disk space. I hate to think how fragmented your indexes and files are by this point. Both are going to cause performance problems. If not now, in the future.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2008 at 1:50 am
😀 will take it up with them.
By the way, the earlier attachment was not the right one.
I am adding the full details here.
December 2, 2008 at 2:16 am
Ok...
I think that this whole mess can be fixed by dropping and recreating two indexes. I'm not going to promise it will, I'm a bit unsure of the meaning of one of the errors:
Data row (1:21229048:195) identified by (RID = (1:21229048:195) ) has index values (YEAR = 2009 and MONTH = -3.689348829E+019 and DAY = 5.696189078E-305 and HOUR = 1.064732608E-309 and UPLANLINEID = 196).
Can you try dropping and recreating the following two indexes and then running a checkDB again, same options as last time.
Table: OUTP_54 Index: IX_OUTP_54
Table: OUT_54 Index: IDXOUT1_54
The main problem with repair is that it needs the entire database offline (single user mode) for the duration of the repair, so it can't be run while the DB is in use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2008 at 2:26 am
Ran this:
CREATE
INDEX [IDXOUT1_54] ON [dbo].[OUT_54] ([YEAR], [MONTH], [DAY], [HOUR], [UPLANLINEID])
WITH
DROP_EXISTING
ON [PRIMARY]
Restarting the CHECKDB, it will take about 3 hrs to run :)...will update once done.
Regards,
December 2, 2008 at 2:42 am
What about the other index, on OUTP_54?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply