Skip navigation

Why do I get "SQL Server has run out of locks" error message with SQL 7 - I thought it dynamically allocated them?

A. First check that you haven't given locks a fixed number rather than letting it automatically expand (the default) - check sp_configure.

If dynamic allocation is set, then this does work - it does dynamically allocate them as long as there is ENOUGH MEMORY to allocate them from. The error should really say that SQL has no more memory to allocate the locks from.

If you have artificially limited the amount of memory SQL Server can get then consider putting it back to an unlimited maximum and let it handle it itself. If it is already automatic then you will need to reduce the memory requirements of the queries/workload concerned or add more memory to the server.

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.