[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How to release locks on sql sever 05/08?

Posted on 2011-10-13
Medium Priority
Last Modified: 2012-05-12
I have an application, after running for a while(a week or two), the sql server will slow down and make some deadlocks. But when I reboot it. It'll be better. But it will cause deadlocks again later.
It looks like there are locks are not released. So when I reboot the server, it becomes better because all locks are released.

My question is how to avoid this senario? Is there another way to release locks besides reboot?
For example to run a query to release....Any help or comment will be very appreciated!!!
Question by:jssong2000
LVL 18

Expert Comment

ID: 36964075
if you use activity monitor you can see the locks and kill the process assocaited with the locks - in 2008 i think its right click on the server at the top level and select activity monitor from the context menu

but you would be best off using this to track down the source of the locks to try and prevent them from happening or stop it at the source gracefully
LVL 25

Expert Comment

ID: 36964143
You can kill the processes creating the situation, but its good to find the root cause and remove it. When the deadlocks happen, check in the sysprocesses or the dmv's and found out what resource it is waiting for and what other processes are using the resource. Check if you can move any of the processes during hours where they are coinciding or if some plan is taking time because of unavailability of proper index.
Can you share more information on the deadlocks and the processes running during the hours?

Expert Comment

ID: 36964234
you can run the statement:

USE master
EXEC sp_who 'active'

and upon coming result check the colum blk does not equal to 0 and kill them with command

kill spid

this can be easily automised with a cursor
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


Author Comment

ID: 36968571
I have imput service, imput process service and print sevice(all window service) working at the same time. I dump 1000 input files in the imput folder every time, and I got some deadlocks. but less than 10 at most of times.

I don't get any deadlocks after reboot. But once I got deadlocks, I will get everytime later. So I feel locks are still on sql server and not released. I don't see bllk=1 if I run the following query, but I see a lot of blk= 69 and 121
USE master
EXEC sp_who 'active'

When I am testing, I see from active monitor wait time about 60,000 and wait count around 60 and cumulative wait time 420,000.

I really don't know where is the problem and how to fix it? Where to find those locks not released? they should be the culprit. I guess once I know where are they, I could run a query to release.
Thank you guys!!

LVL 18

Expert Comment

ID: 36968930
if you have a specific process that it doing that then you can run a trace on that specific SPID to see what SQL it is extracting

Author Comment

ID: 36969319
Is there somebody know how to manually release locks on a specific table? For example run a query. Because I know I use two tables when processing. I could release those two tables' lock before processing. Thanks.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36971902
You are on a wild goose chase.  This has nothing to do with locks and everything to do with a query or queries that need to be optimized.  It does not get any simpler than that.
LVL 15

Accepted Solution

Anuj earned 2000 total points
ID: 36977958
You are experiencing problems because of concurrent read and write occurs in more than one transactions. This problem can be solved by creating efficient indexes for your tables. Capture your slow running queries using SQL Server profiler or Activity Monitor or using DMV, Analyze its execution plans and Index usages, optimize the queries if necessary. Act accordingly if you find missing indexes or fragmented indexes or outdated statistics.

Also keep your transaction short, and access the objects in proper order.

I am sure this will solve your problem.

Author Closing Comment

ID: 36980257
Thank you very much! It worked.
I did some performance tuning. But fiIxing a deadlock problem is my first time.
Very appreciated!!!

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question