Disabling all access to the server

  • Hello everyone!

    We have a small group of users (4) using MS Access 2000 to run queries on our MS SQL 2000 SP3 server. When specific queries are run, a deadlock is created, disabling all access to the server. We narrowed it down to these specific queries. We've tried setting the queries to "Record Locks: no lock" and "Recordset Type: SnapShot" in the query's properties and it still causes deadlocks. If someone can provide me with any information that might help me, I would be very happy.

    Thank you.

  • Really need to upgrade.

    SQL2000 and Access 2000 really using 21+ year old technology in this day and age your asking for issues.

    Your going to have to look at the queries and look at what’s causing the deadlocks and tune them so they don’t.

    Make sure table access in each query is the same order etc.


  • Wait, a deadlock is an error that will clear immediately. It won't lock the server and prevent access. I think you're saying that you're getting blocking that is preventing access. That suggests an open transaction. Find and kill it. Then fix the code so that it doesn't open a transaction and then leave it.

    Oof, trying to remember how to deal with this in SQL Server 2000 where there isn't a single DMV... sp_who? Is that right for back then? I think it is.

    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • AFAIK you cannot disable an index on SQL2000 !

    syntax "alter INDEX myix ON myschema.mytable disable;" only works as of SQL2005  ...

    And even if you can, DO NOT disable index id 1 ( i.e. the clustering index )


    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply