Microsoft KB Archive/193452

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

Article ID: 193452

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q193452



BUG #: 16848 (SQLBUG_65)



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 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

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: kbbug kbfix KB193452

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.