FIX: Insufficient Tempdb Space During Sort May Cause an Access Violation

• Microsoft SQL Server version 6.5

SYMPTOMS

Queries that require a sort operation, such as those that include the DISTINCT or ORDER BY keywords, may experience a handled access violation (AV) within SQL Server if you run out of space in tempdb. In the SQL Server errorlog, you will see error 1105 for tempdb, immediately followed by an access violation exception message for SQL Server.

WORKAROUND

To work around this problem, ensure that there is adequate space is available in tempdb, by increasing the size of the database.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

MORE INFORMATION

The access violation is handled and causes no adverse effects on the server. However, to avoid encountering the problem again, ensure that tempdb is sufficiently sized so that you do not run out of space when running such an operation. Error 1105 is logged to the errorlog when no free space is available.

Sizing of tempdb largely depends on the number of users and the types of queries being performed. As a general rule, consider the number of users who will be active, as well as the typical size of the result sets that the users will be sorting. For example, if you have 10 users running a query that must sort a 1-MB result set, ensure that tempdb is at least 10 MB in size.

You can also use Windows NT Performance Monitor to monitor typical tempdb usage and help determine an appropriate tempdb size. Make sure that you are monitoring the server during a typical workload, and also allow some additional space for infrequently run jobs that may also require tempdb space.

Additional query words: prodsqlsp sp5resultset perfmon

Keywords          : SSrvAdmin SSrvErr_Log SSrvGPF SSrvTran_SQL kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: May 4, 1999