PRB: Why SQL Server Error Log Contains "Open Objects" Warnings (89392)

SYMPTOMS

After SQL Server is run for a period of time, the error log may contain many lines (possibly thousands) of the following warning:

Warning: OPEN OBJECTS parameter may be too low;
attempt was made to free up descriptors in localdes().
Run sp_configure to increase parameter value.

CAUSE

This warning message is generated each time SQL Server needs to search for a free descriptor for an object. It is important to note that this is only a warning message, not an error. This warning message was not raised in versions of SQL Server prior to 4.2 but was included beginning with 4.2 to warn the System Administrator that the value for "open objects" should be increased to achieve better performance.

WORKAROUND

To eliminate this warning from being raised, increase the value of the "open objects" parameter. To do this, execute the sp_configure system procedure with the following syntax:

exec sp_configure "open objects", <new value>

Then execute RECONFIGURE, shut down SQL Server, and restart it for the change to take effect. "<new value>" should be a number higher than what is currently set for "open objects". There is no way to determine the best value for this, but it is suggested to increase it by about 500, and examine the SQL Server error log every day or so. If the warnings are still appearing, run the sp_configure procedure to increase it again, until the warnings are no longer generated. Each "open object" requires approximately 70 bytes of memory, which is allocated at the time SQL Server is started. Thus, by increasing the value of "open objects" by 1000, you will lose approximately 69K of memory that would otherwise have been available for SQL Server's procedure and data caches.

The display of this warning message changed slightly with SQL Server version 4.2a. In this version, the warning message is raised only the first time the resource limit is reached, but will not be continually logged in the error log.