SQL Server Performance Forum – Threads Archive

Release locked resource on SQL 2005

hi there, I am still running a large numbers of Visual basic 6 windows applications which use ADODB. We’d just migrated from SQL2000 to SQL2005 two weeks ago. So all SQL connection is done using ADODB object in the applications. There’s no changes in the connection string and all locking settings stay the same on the server (no changes had been made to mirgate from SQL2000 to SQL2005). But we constantly get process block issues on the SQL2005 server. I got a couple of questions regarding the issue: 1. Is there any different in locking behaviour in SQL2000 and SQL2005? If there is, how do I change them? 2. Instead of using @@LOCK_TIME, is there another setting that I can use to let SQL server to release locked process automatically once it lock over 20 minutes? We desperately need helps and advices. The SQL2005 is on live and even though we can solve the problem by identifying the locked process and kill it BUT it happens all the time! and we won’t be able to handle them when there’s hundreds of users accessing the data. Since it involved more than 40 application it’s also hard for us to change all VB code to solve the problem (e.g. using SET TRANSACTION ISOLATION LEVEL in every TSQL). I hope to get a database/server level settings that’ll solve the problem. Please help. Cheers Pammy

Have you rebuilt the indexes since you migrated to 2005? If not, do so, and this will also update the statistics. It is possible that out of date statistics are causing performance problems, which are leading to the blocking. —————————–
Brad M. McGehee, SQL Server MVP

I’ve moved to relevant forum. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

http://www.microsoft.com/technet/prodtechnol/sql/2005/cncrrncy.mspx on the topic. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

]]>