SQL locks

I have a table in an SQL database. It has 100000 records in it so is not huge.
If I run an update on it e.g update table set column1 = 0 it can take ages and the users notice a huge difference i.e. the system slows down. If however I restart SQL server and run it, it only takes a second or 2. This leads me to think that something is locking the records or something like that and that the restart clears theses locks. Does this make sense and is there something I can run to clear locks.

It might be worth noting that the users are using MSAccess front end. I use ADO
LVL 1
EamonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brian-jgCommented:
I've seen far too many database apps which don't close connections. Check how many processes are running in the db when you have this issue.
0
chapmandewCommented:
>>If I run an update on it e.g update table set column1 = 0 it can take ages and the users notice a huge difference i.e. the system slows down.

Its because it locks the table.  Instead of updating everything, try updating x number of records at a time until you loop through and update them all.  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chapmandewCommented:
BTW...this has nothing to do w/ closing connections.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

advfinanceCommented:
When it locks, and before you restart run "exec sp_who2" and it'll give you a list of active processes. One of the columns it returns, "BlkBy" indicates which process IDs are blocking other processes, so you might be able to narrow down your options of what's causing the block that way
0
chapmandewCommented:
>>This leads me to think that something is locking the records or something like that and that the restart clears theses lock

Sure, when you update an entire table, it is going to lock it...which prevents access.  If you loop through and update x number of records at a time, you'll get around this. Restarting is NOT going to help.  In fact, it won't matter because the process will need to be rolled back when the instance comes back up.
0
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
This might not fit your situation, but it's something to consider.  Is the field you are trying to update also updated by users directly, or is it separate?  If separate, then you could try to take advantage of partial row concurrency checking.  If users are busy updating fields A, B and C, but you need to sweep through and update field D while they are working, then this can help.  You need to make sure that the Access update forms don't use Select * or include field D in their recordsource.  You also can't have a RowVersion (aka TimeStamp) field in your table, because that will force full row concurrency.  With this scenario, you'll be able to zip through and make changes to field D without disturbing the users.
0
EamonAuthor Commented:
I dont mean restarting after it is going slow. I mean that if I restart before I run it I know it will run way faster.

I have attached the result from exec sp_who2.
The database I use is PopTables.

Book1.xls
0
chapmandewCommented:
sp_who2 doesn't matter...you already know what is causing the problem.
0
EamonAuthor Commented:
Maybe I am confused. I don't know what is causing the problem.
0
EamonAuthor Commented:
thanks ArmenStein but there are too many unknows with the access side. lots of different forms use that table.
0
EamonAuthor Commented:
I think at this stage I am looking to see are there any 'Maintenance' stored procedures in SQL. Something to clear all locks or whatever. You see if I ask the users to stop for a few minutes and then run the update it is still slow compare to when I restart SQL server. I dont think the problem is that they are updating the table at the same time as me. I have a feeling that access is leaving the table in some way that is causing the problem.

I hope I am making this clear and that you know I am just guessing at the cause.
0
Anthony PerkinsCommented:
>>I hope I am making this clear and that you know I am just guessing at the cause.<<
Just to be clear you are attempting to UPDATE all 100,000 rows with a single UPDATE statement?  If so, did you try chapmandew suggestion?
0
EamonAuthor Commented:
Yes that is what I am trying to do. I dont know how to loop through without writing code and I dont want to have to write code for every update I will ever want to do.

I think what I am looking for is a statement or something to UNLOCK the records. If such a thing does not exist then I think that might be the answer to this question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.