February 18, 2009 at 7:11 pm
Hi To All SQL DB Admin,
I am a newbie in SQL and I hope the community can help me with my problem... I need your kind assistance.
We are maintaining 2 production database. We always encounter hanging of the system whenver we are accessing this one database. This happened since last Friday, February 13 until now. When this happened, we cannot anymore access the DB through Enterprise Manager and we are left with no choice but to restart the system. After restart the system and DB is already ok. The last time we encountered it is just yesterday and when we access the tables in DB, it displays a message that it is in a suspect mode and when we restart it, then its ok. Also, when we run scheduled shrink and backup of this DB, it always failed since that date. Below is the usual error we encounter:
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:02/18/2009
Time:6:43:30 PM
User:N/A
Computer:RETAILIGENCE
Description:
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Data:
0000: 00 00 00 00 13 00 00 00 ........
0008: 0d 00 00 00 52 00 45 00 ....R.E.
0010: 54 00 41 00 49 00 4c 00 T.A.I.L.
0018: 49 00 47 00 45 00 4e 00 I.G.E.N.
0020: 43 00 45 00 00 00 0d 00 C.E.....
0028: 00 00 52 00 65 00 74 00 ..R.e.t.
0030: 61 00 69 00 6c 00 69 00 a.i.l.i.
0038: 67 00 65 00 6e 00 63 00 g.e.n.c.
0040: 65 00 00 00 e...
===================================================
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:02/18/2009
Time:6:43:38 PM
User:N/A
Computer:RETAILIGENCE
Description:
Error: 3314, Severity: 21, State: 4
Error while undoing logged operation in database 'Retailigence'. Error at log record ID (1090042:35957:77).
Data:
0000: f2 0c 00 00 15 00 00 00 ò.......
0008: 0d 00 00 00 52 00 45 00 ....R.E.
0010: 54 00 41 00 49 00 4c 00 T.A.I.L.
0018: 49 00 47 00 45 00 4e 00 I.G.E.N.
0020: 43 00 45 00 00 00 0d 00 C.E.....
0028: 00 00 52 00 65 00 74 00 ..R.e.t.
0030: 61 00 69 00 6c 00 69 00 a.i.l.i.
0038: 67 00 65 00 6e 00 63 00 g.e.n.c.
0040: 65 00 00 00 e...
====================================================
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:02/18/2009
Time:6:43:38 PM
User:N/A
Computer:RETAILIGENCE
Description:
Error: 9001, Severity: 21, State: 1
The log for database 'Retailigence' is not available.
Data:
0000: 29 23 00 00 15 00 00 00 )#......
0008: 0d 00 00 00 52 00 45 00 ....R.E.
0010: 54 00 41 00 49 00 4c 00 T.A.I.L.
0018: 49 00 47 00 45 00 4e 00 I.G.E.N.
0020: 43 00 45 00 00 00 0d 00 C.E.....
0028: 00 00 52 00 65 00 74 00 ..R.e.t.
0030: 61 00 69 00 6c 00 69 00 a.i.l.i.
0038: 67 00 65 00 6e 00 63 00 g.e.n.c.
0040: 65 00 00 00 e...
==================================================
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:02/18/2009
Time:6:43:38 PM
User:N/A
Computer:RETAILIGENCE
Description:
Error: 3314, Severity: 21, State: 4
Error while undoing logged operation in database 'Retailigence'. Error at log record ID (1090042:35957:77).
Data:
0000: f2 0c 00 00 15 00 00 00 ò.......
0008: 0d 00 00 00 52 00 45 00 ....R.E.
0010: 54 00 41 00 49 00 4c 00 T.A.I.L.
0018: 49 00 47 00 45 00 4e 00 I.G.E.N.
0020: 43 00 45 00 00 00 0d 00 C.E.....
0028: 00 00 52 00 65 00 74 00 ..R.e.t.
0030: 61 00 69 00 6c 00 69 00 a.i.l.i.
0038: 67 00 65 00 6e 00 63 00 g.e.n.c.
0040: 65 00 00 00 e...
==================================================
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:02/18/2009
Time:6:43:38 PM
User:N/A
Computer:RETAILIGENCE
Description:
Error: 3314, Severity: 21, State: 5
Error while undoing logged operation in database 'Retailigence'. Error at log record ID (1090042:5722:1).
Data:
0000: f2 0c 00 00 15 00 00 00 ò.......
0008: 0d 00 00 00 52 00 45 00 ....R.E.
0010: 54 00 41 00 49 00 4c 00 T.A.I.L.
0018: 49 00 47 00 45 00 4e 00 I.G.E.N.
0020: 43 00 45 00 00 00 0d 00 C.E.....
0028: 00 00 52 00 65 00 74 00 ..R.e.t.
0030: 61 00 69 00 6c 00 69 00 a.i.l.i.
0038: 67 00 65 00 6e 00 63 00 g.e.n.c.
0040: 65 00 00 00 e...
==================================================
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:02/18/2009
Time:5:14:37 PM
User:N/A
Computer:RETAILIGENCE
Description:
Error: 644, Severity: 21, State: 3
Could not find the index entry for RID '164c3035393435365748495445333620202020202020202020202020202020202043444220205245300' in index page (1:1623472), index ID 0, database 'Retailigence'.
Data:
0000: 84 02 00 00 15 00 00 00 ?.......
0008: 0d 00 00 00 52 00 45 00 ....R.E.
0010: 54 00 41 00 49 00 4c 00 T.A.I.L.
0018: 49 00 47 00 45 00 4e 00 I.G.E.N.
0020: 43 00 45 00 00 00 0d 00 C.E.....
0028: 00 00 52 00 65 00 74 00 ..R.e.t.
0030: 61 00 69 00 6c 00 69 00 a.i.l.i.
0038: 67 00 65 00 6e 00 63 00 g.e.n.c.
0040: 65 00 00 00 e...
===================================================
I really hope someone can here can assist me. I am not a DB Admin Pro. I am only beginner. Thank you so much in advance!!!!
February 18, 2009 at 7:33 pm
Some links for you. May them help.
February 18, 2009 at 8:46 pm
Yes,sir thats a big help. I am currently checking it. Also want to understand the general description of the error. Hope you can explain it 🙂
This database were created by a third party supplier. Indexes, SP and Views related to this DB are all created by them. We are updating some of it in the DB during the first time we encountered the unusual hanging of this particular DB. THat made me think that it may have something to do with the updating. If that the case, I can ask supplier to help us fix/repair the problem since they are more knowledgeable than me.
Thank you in advance.
February 18, 2009 at 11:27 pm
Hi,
You can check out the errors in http://technet.microsoft.com/en-us/library/ms164086(SQL.90).aspx. I think one of the index is corrupted in your database. Can you run a DBCC CHECKDB
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
February 19, 2009 at 1:39 am
It looks like there's some corruption. Please run the following and post all of the results
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
Also, read through this - http://www.sqlservercentral.com/articles/65804/
You mentioned Enterprise manager. Is this a 2000 or a 2005 server? (You posted in the 2005 forum)
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
February 19, 2009 at 3:25 am
Its Enterprise Manager 2000,sir.
Sorry I may have overlooked the forum heading. I am just so very eager to resolve this issue because it may affect the whole business operation.
WE are to perform DBCC CHECKDB today. Hopefully it will work out.
Thanks in advance,sir.
February 19, 2009 at 5:18 am
elay (2/18/2009)
====================================================Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:02/18/2009
Time:6:43:38 PM
User:N/A
Computer:RETAILIGENCE
Description:
Error: 9001, Severity: 21, State: 1
The log for database 'Retailigence' is not available.
Data:
0000: 29 23 00 00 15 00 00 00 )#......
0008: 0d 00 00 00 52 00 45 00 ....R.E.
0010: 54 00 41 00 49 00 4c 00 T.A.I.L.
0018: 49 00 47 00 45 00 4e 00 I.G.E.N.
0020: 43 00 45 00 00 00 0d 00 C.E.....
0028: 00 00 52 00 65 00 74 00 ..R.e.t.
0030: 61 00 69 00 6c 00 69 00 a.i.l.i.
0038: 67 00 65 00 6e 00 63 00 g.e.n.c.
0040: 65 00 00 00 e...
==================================================
!
But, Why does it say log is not available? did you further check it?
Yes you should definitely run DBCC and do as recommended.
February 19, 2009 at 5:53 am
elay (2/19/2009)
WE are to perform DBCC CHECKDB today. Hopefully it will work out.
CheckDB's not going to fix anything. It's a diagnostic that will show what's wrong.
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
February 19, 2009 at 7:00 pm
After indentifying which index is corrupted, you may try to rebuild this index.
Do not forget to have a backup before doing anything.
February 19, 2009 at 7:23 pm
Sirs,
Good day to all!
Here are the results f the scripts we run:
A. DBCC CHECKDB (database name) WITH NO_INFOMSGS, ALL_ERRORMSGS
Server: Msg 8933, Level 16, State 1, Line 1
Table error: Object ID 1654296953, index ID 50. The low key value on page (1:548364) (level 0) is not >= the key value in the parent (1:818682) slot 0.
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1654296953, index ID 50. The high key value on page (1:1754006) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:548364).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1654296953, index ID 50. The high key value on page (1:1754006) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:548364).
CHECKDB found 0 allocation errors and 3 consistency errors in table 'StockMovements' (object ID 1654296953).
CHECKDB found 0 allocation errors and 3 consistency errors in database 'Retailigence'.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (Retailigence ).
B. Then we run this command:
DBCC TRACEON (3604)
DBCC PAGE ('Retailigence', 1, 1623472, 3)
GO
I attached the Results for this one. Hopefully, can ask for your time to read it 🙂
C. Then we get the value of the m_objid and run script:
Use Retailigence
Go
Select object_name(m_objid value) -- 46623209
and it give us the result :
Table: StockroomBalance
I want to seek again an advice on how to read the errors and how best to best fix it.
Thanks in advance!
February 19, 2009 at 7:31 pm
Sir,
But, Why does it say log is not available? did you further check it?
That I cannot answer sir because physically we can view the log file. However, we have an issue before that our log file grows bigger than our mdf file which cause our db bugged down. We are left with no choice but to restore our most recent backup...
We do backup everyday.
Thanks.
February 19, 2009 at 7:47 pm
Frankly speaking, I believe that MS support team may interprete the check output much better. If your company has support agreement with MS, it is the best source.
As users, I would like to recommend you create a new database, import data to it, and rebuild indexes.
Let us see what other people will say.
February 19, 2009 at 8:02 pm
Sirs,
The results of the DBCC we run last night was in the first page. Waiting for assistance. Thanks.
February 19, 2009 at 9:56 pm
elay (2/19/2009)
Server: Msg 8933, Level 16, State 1, Line 1Table error: Object ID 1654296953, index ID 50. The low key value on page (1:548364) (level 0) is not >= the key value in the parent (1:818682) slot 0.
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1654296953, index ID 50. The high key value on page (1:1754006) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:548364).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1654296953, index ID 50. The high key value on page (1:1754006) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:548364).
You're lucky. This corruption is repairable. Run this to find the name of the index involved
SELECT name from sysindexes where id = 1654296953 AND indid = 50
Then drop that index (it's on the StockMovements table) and recreate it. (Do not just rebuild it, that may not help). Once you've done that, run checkDB again.
What version are you running? (SELECT @@VERSION)
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
February 20, 2009 at 12:33 am
What version are you running? (SELECT @@VERSION)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
We're running checkdb now. I'll post the result once we have it.
thanks.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply