Why is my application locking up in SQL Server?

A. This is due to a documented change in SQL 6.5 because tables created by using SELECT INTO hold to the ACID (atomicity, consistency, isolation, durability) transaction properties. This also means that system resources, such as pages, extents, and locks, are held for the duration of the SELECT INTO statement. With larger system objects, this leads to the condition where many internal tasks can be blocked by other users performing SELECT INTO statements. For example, on high-activity servers, many users running the SQL Enterprise Manager tool to monitor system processes can block on each other, which leads to a condition where the SEM application appears to stop responding. (This happens on tempdb which is the biggest problem with this new feature for most users)

You can revert to the old 6.0 and below behaviour where these locks are not held by applying at least SQL 6.5 ServicePack 1 and then setting traceflag 5302 on startup.

It is recommended that you amend your application to not use select into, or if you do, create the table using "SELECT ... INTO .... WHERE 1=0" to create the table and then use standard inserts to populate the table.

(This problem does not occur in SQL 7.0 and above as row-level locks are taken and so system table pages aren't unnecessarily blocked)

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.