Error : DBCC could not obtain a lock on this object because the lock request timeout period was exceeded

, 2019-01-18

DBCC CheckDB failed with error “DBCC could not obtain a lock on this object because the lock request timeout period was exceeded”

Error: Today, My DBCC checkdb job failed with below error.

Executed as user: USERNAME. Object ID 451805713 (object ‘dbo.AEETBL’): DBCC could not obtain a lock on this object because the lock request timeout period was exceeded. This object has been skipped and will not be processed. [SQLSTATE 42000] (Error 50000) DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Issue: When I see this error, I ask a question to myself “Does DBCC CHECKDB perform locks?” and the answer is NO. From SQL Server 2005 onwards, DBCC CheckDB works on a hidden database snapshot. A database snapshot is a read-only copy of the database. You can see some blocking on server or slowness for users due to snapshot I/O overhead but for sure no LOCKS.

Cases when DBCC CHECKDB take lock: –

  1. If the TABLOCK option is specified when executing DBCC CheckDB
  2. If hidden database snapshot is not created due to performance reasons or lack of disk space

So, It’s time to check my code and disk space. I found my DBCC CheckDB job is running with TABLOCK which cause this issue. This is not a common thing to use. If you are using TABLOCK you should have valid reasons like either slow disk system or low disk space.

Some people said they used TABLOCK to avoid I/O overhead of snapshot. For those, I need to mention “it’s higher to possess one thing in situ of nothing”. Microsoft suggests running DBCC Checkdb in odd business hours so that user performance is minimally impacted.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles:-

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.sqlservercentral.com/blogs/mssqlfun/

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads