September 17, 2012 at 9:43 am
Recently we began receiving this error on a production database when a schedules job with DBCC CHECKTABLES runs:
Message
Process ID 115 attempted to unlock a resource it does not own: PAGE: 27:1:xxxxxxx. Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
This is happening on the reported roll foward/roll backs between the CHECKTABLES. What is interesting is that it does not happen consistently but almost every time CHECKTABLE runs on a certain set of tables. It was recommended to run a CHECKDB, so I backed up the database, restored to another server, and ran a full CHECKDB, but got no errors. However, our scheduled job to run CHECKTABLES has been still getting this error on the production database.
Another interesting (maybe unrelated) note is that database id 27 does not exist. I thought the 27 pointer was supposed to refer to a database, however, we only have dbid up to 26 in sys.databases. Also, when I ran a CHECKDB on the other backup server, the dm_exec_requests showed that the CHECKDB was running on dbid + 1 greater thant the highest db id on that server as well, however, running select db_name(dbid) showed the correct database despite not being the id in sys.databases.
Anyone have an idea about this? Any ideas about the error? I'm thinking we may have to run against production to get the error for some reason. Could this be another issue with SQL Server and not an actual corruption issue?
September 17, 2012 at 10:05 am
Database ID 27 could be the hidden snapshot that the integrity check uses. Can you try a full checkDB?
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
September 17, 2012 at 12:18 pm
We ran CHECKDB on the restored copy of the database, which should report the same problems as the production database right? My boss is very hesitant about running against production directly because in the past we have had a full CHECKDB bring a server down. It is not likely but we may have to wait for maintenance to run.
Is it possible that CHECKDB would show something on the production database, but not on the restored copy?
September 17, 2012 at 1:06 pm
Can you run a full CheckDB on whatever database threw the CheckTable errors?
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
September 24, 2012 at 7:37 am
Ok, so I was able to run a full CHECKDB on the database over the weekend and it returned no errors. So I guess this is not a corruption issue, which is good, but it doesn't help explain why we suddenly started getting this message about once a week, or 5% of our CHECKTABLE runs, after NEVER getting it before.
The only change I can think of is we have recently upgraded to SQL2008 SP2 CU10.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply