Database Table lock

Question

I'm using SQL 2000 for my application. My application is using N tables. My application has a wrapper for SQL server called Database server. It is running as a 24/7 windows service.

If I have checked the integrity check option in the SQL maintenance plan, when this task is running one time after that one of my tables has been locked and it has been never unlocked. So my history of the database transaction has been lost.

Please provide your suggestion how to solve this problem.

Is this a continuing problem, or is it a one-time lock that has never been released? The CHECK INTEGRITY maintenance plan step locks the database exclusively, which can cause contention issues. If you have SQL Server Enterprise, it can do operations like this while remaining online, but Standard is not as good at sharing. — SqlRyan
– SqlRyan, Commented Jul 24, 2009 at 4:32

Community · Accepted Answer · 2017-05-23 10:33:04Z

1

What if you have a client-side command timeout? And the locks are your own locks as a result of the DBCC?

Your code will timeout waiting for the DBCC to finish, but any locks it's already issued are not rolled back.

A command timeout tells SQL Server to simply stop processing. To release locks you need to either ROLLACK on the connection or close the connection.

Options:

Use SET XACT_ABORT in the SQL: Do I really need to use “SET XACT_ABORT ON”? (SO)
On client error, try and rollback yourself (Literally IF @@TRANCOUNT > 0 ROLLBACK TRAN)

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Jul 26, 2009 at 13:46

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database Table lock

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related