Maximum number of databases used for each query has been exceeded. The maximum allowed is %d.

Details
Product: SQL Server
Event ID: 925
Source: MSSQLServer
Version: 8.0
Component: SQL Engine
Message: Maximum number of databases used for each query has been exceeded. The maximum allowed is %d.
   
Explanation
SQL Server 2000 allows up to eight active (open) database references to be tracked in an internal stack at any given time within a query. A reference to a database is tracked for a given query as long as activity is still occurring for that portion of the query that uses that database reference.

A database reference is tracked each time a database is accessed, even if there is already a separate reference to that database. The reference is no longer tracked when the query is no longer actively using the reference. Therefore, you can encounter this error if you have nested queries that cross back and forth across fewer than eight databases. An example of that would be a stored procedure in one database that calls a stored procedure in a second database that then calls a stored procedure in the first database. In that example there are three database references made and tracked even though there are only two databases involved. Whether all three are held open at once depends on several factors including the length of time it takes to complete each part of the query.

If any of your activities make use of tempdb, either explicitly or implicitly, then tempdb will count as one of the eight databases. Internal activities such as autoupdate statistics will also count as an additional reference.

A query that explicitly references eight or more databases does not automatically hit this limit. For instance this query does not have all eight database references tracked in its internal stack at one time, so it does not cause the above error:

Select * from db1..t1
Select * from db2..t1
Select * from db3..t1
Select * from db4..t1
Select * from db5..t1
Select * from db6..t1
Select * from db7..t1
Select * from db8..t1
Select * from db9..t1

   
User Action
Follow these steps:

  • Rewrite the query to reduce the number of database references that will be made.
  • Execute UPDATE STATISTICS before executing the query. This may reduce the amount of time a given database reference is tracked.
  • Perform general performance tuning, such as evaluating indexes, to reduce the amount of time each database reference is tracked.

Related:

Leave a Reply