BUG: Error 610 When a Query Generates More than 15 Worktables

ID: Q225490

The information in this article applies to:

Microsoft SQL Server versions 6.5, 6.5 Service Pack 5a

BUG #: 18229 (SQLBUG_65)

SYMPTOMS

When a single SELECT statement, which could include sub-queries, creates more than 15 worktables you may receive Error message 610:

Error 610
Maximum number of databases that may be accessed by a transaction is 8.
This number has been exceeded by this query.

The error occurs even though the SELECT statement only accesses one database. Those worktables may be generated explicitly or implicitly by reformatting, group by, sorting, and so forth.

CAUSE

This error message is misleading. The error has nothing to do with the eight database limit on a query statement. The error occurs because the implicit transaction hits the "max available transaction descriptors" limit, which is 16.

WORKAROUND

Avoid creating more than 15 worktables on a single query statement. If many worktables are generated due to reformatting, you may create indexes on the corresponding columns to avoid reformatting so as to reduce the number of worktables on a select statement.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5.

MORE INFORMATION

This error does not occur in SQL Server 7.0.

Additional query words:

Keywords : kbSQLServ650bug kbSQLServ650sp5bug
Version : winnt:6.5,6.5 Service Pack 5a
Platform : winnt
Issue type : kbbug