I have a query that seems to lock other users out of the system.

A. A. In situations like this the usual problem is with locks. Check with sp_lock or sp_lock2 what the offending query is doing.

One common occurrence that people fell foul of was introduced in SQL 6.5 when Microsoft decided to let table creation be allowed in transactions by making it an ATOMIC transaction. A by product of this is that when a SELECT INTO is done it locks out system tables in the database concerned and prevents other users from accessing them. With a long-running select into this can cause real problems.

Microsoft recognised this and as long as you have at least service pack 1 applied you can set traceflag -T5302 to remove this behaviour. Check out Q153441 on the Microsoft Kb and 5302.txt in the FAQ for more information.

SQL 7.0 has row-level locking and so does not block on system tables like this.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.