January 25, 2013 at 6:55 am
Hi experts,
CHECKDB threw error
Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. The duplicate key value is (55822684521).
January 25, 2013 at 7:38 am
Please run the following and post the full and complete, unedited output.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
January 28, 2013 at 7:34 am
Msg 2601, Level 14, State 1, Procedure sp_flush_commit_table, Line 15
Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. The duplicate key value is (55822684521).
The statement has been terminated.
Msg 2601, Level 14, State 1, Procedure sp_flush_commit_table, Line 15
Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. The duplicate key value is (55822684521).
Msg 5269, Level 16, State 1, Line 1
Check terminated. The transient database snapshot for database 'ABC' (database ID 8) has been marked suspect due to an IO operation failure. Refer to the SQL Server error log for details.
Msg 3999, Level 17, State 1, Line 1
Failed to flush the commit table to disk in dbid 8 due to error 2601. Check the errorlog for more information.
Msg 3999, Level 17, State 1, Line 1
Failed to flush the commit table to disk in dbid 8 due to error 2601. Check the errorlog for more information.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
January 28, 2013 at 7:45 am
Can you run the CHECKDB using WITH TABLOCK?
Can you also try creating a database snapshot of your database to see if that succeeds? (nothing to do with CHECKDB)
I suspect there's something amiss with it and when the snapshot creation starts, it's trying to roll back a transaction in the snapshot and failing.
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
January 28, 2013 at 7:51 am
Check this out, see if it's applicable.
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
January 29, 2013 at 2:29 am
Thanks SQL Gurus Paul and Gail for the response.
January 29, 2013 at 2:30 am
SNAPSHOT CREATION FAILED WITH BELOW ERROR
--------------------------------------------------------
Msg 2601, Level 14, State 1, Procedure sp_flush_commit_table, Line 15
Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. The duplicate key value is (55822684521).
The statement has been terminated.
Msg 2601, Level 14, State 1, Procedure sp_flush_commit_table, Line 15
Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. The duplicate key value is (55822684521).
The statement has been terminated.
Msg 3999, Level 17, State 1, Line 1
Failed to flush the commit table to disk in dbid 8 due to error 2601. Check the errorlog for more information.
Msg 3999, Level 17, State 1, Line 1
Failed to flush the commit table to disk in dbid 8 due to error 2601. Check the errorlog for more information.
DBCC CHECKDB WITH TABLOCK GAVE BELOW ERROR
---------------------------------------------------------------
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
January 29, 2013 at 2:34 am
Ratheesh.K.Nair (1/29/2013)
DBCC CHECKDB WITH TABLOCK GAVE BELOW ERROR---------------------------------------------------------------
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Make sure there are no users in the database and try again. Tablock requires exclusive locks.
Definitely looking like it's not a checkDB problem. Did you read the blog post I referenced?
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
January 29, 2013 at 2:51 am
Yes Gail I have go through the link you provided.
Mine is SQL Server 2008 R2 SP1 & CU3 and in that post its mentioned like fix for this issue was first released in Cumulative Update 1 for SQL Server 2008 R2 Service Pack 1.
Do i need to upgrade to SP2?
What I am worried now is all these requires restart.
January 29, 2013 at 8:23 am
deleted
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply