SMS: No Query Results Returned When SQL Tempdb is Too Small

• Microsoft Systems Management Server versions 1.0, 1.1 and 1.2

SYMPTOMS

If you run a query that requires more space than is allocated for tempdb, no return is displayed and no errors are given.

By default, Systems Management Server sorts the query results before displaying them. This sorting is done in tempdb.

RESOLUTION

If this problem exists for a single large query, it may be possible to refine the query so that the query result is smaller.

-or-

Increase the size of tempdb.

Device Size Recommendations

- The data and log devices should each be a minimum of 10 MB.

- For each machine, 35k of data device space should be allocated.

- For the site database, the log device should be at least 10% of the data

   device.

   for the site database.

   data device.

- The site data device would be 35k x 10,000 for a 350 MB data device and

   10% x 350 MB for a 35 MB log device.

   a 20% x 70 MB for a 14 MB log device.

Free Space Requirements in the Database

To find the space used in the data device, run sp_spaceused against the database. Divide the space reserved by the size of the data device. For example, if this is over 90% for the site database, expand the database. If this is over 60% for the tempdb data device, expand the database. The tables in tempdb are all temporary, so this needs to be checked at peak usage, when several Admin user interfaces are querying the database. The Admin user interfaces put more of a load on tempdb than the site can, so even a few can consume the space recommended above.

The log devices can be monitored with perfmon. If any of these get over 60%, expand the database.

STATUS

Microsoft has confirmed this to be a problem in Systems Management Server versions 1.0, 1.1 and 1.2. We are researching this problem and will post new information in the Microsoft Knowledge Base as it becomes available.