This article describes how to change sp_configure parameter values when the
MSSQLServer service fails to start after changes have been made to the
An attempt to start the MSSQLServer service may fail as follows:
- From the Windows NT Service Control Manager, the following message is returned:
Could not start the MSSQLServer service on \\servername
Error 2140: An internal Windows NT error occurred
- From the SQL Service Manager, the light turns green and then turns red.
- From the command line, a net start mssqlserver command returns the following message:
The MSSQLServer service is starting.
The MSSQLServer service could not be started.
The service did not report an error.
More help is available by typing NET HELPMSG 3534.
A reason for the failure may be logged in the SQL Server error log. Using
Notepad, open the file called Errorlog. You can find this file in the
Log directory of the SQL Server installation (for example, the Sql60\Log
directory or the Mssql\Log directory).
parameter was set too high if the last line of the errorlog is:
initdata: suballocation for buffer pages failed (%d bytes requested)
The tempdb in RAM
parameter was set too high if the last few lines read:
Clearing temp db
udactivate(IN_RAM): Operating system error 8(Not enough storage is
available to process this command.) encountered
Device activation error. The physical filename 'IN_RAM' may be incorrect
crdb_tempdb: Unable to move tempdb into RAM; RAM device doesn't exist,
cannot be created, or doesn't have enough space for tempdb
Follow the steps below to start up the SQL Server and modify the
sp_configure parameters so that the MSSQLServer service will be able to
- Open a command prompt on the server. Type sqlservr -c -f to start SQL Server in minimal configuration mode.
This will also start the server in single-user mode and skip the execution of startup stored procedures. When the last line on the screen reads "Warning: override, autoexec procedures skipped", leave the screen as it is and use ISQL/w to connect to the server.
- Ensure that SQL Executive and any other services that may log in to SQL Server have been stopped and that there is no attempt to open up multiple ISQL/w connections. Some programs or services that you should verify are stopped include, but are not limited to: SQL Enterprise Manager, SQL Executive, MSDTC, SQL Trace, Performance Monitor, Systems Management Server, and ArcSrv related services. There may be other programs or services that should be stopped as well; you should check your software documentation to determine whether it attempts to make a connection to SQL Server. These services must be stopped because they may cause the following message to be returned:
Msg 4002, Level 14, State 1, Server Microsoft SQL Server,
Line 0 Login failed
DB-Library: Login incorrect
- In ISQL/w, use sp_configure to change the memory or tempdb in RAM settings. For example, issue commands similar to the following:
sp_configure memory, 8192
sp_configure tempdb, 0
- Run the RECONFIGURE command to apply the changes, as in the following example:
reconfigure with override
- Execute the SHUTDOWN command.
- Start the MSSQLServer service; it should succeed.
You can also use these steps for situations where the server may start
but connections cannot be established to SQL Server because of some other
sp_configure parameter settings. For example, you can use the method
described in this article when the procedure cache is set too high.
When the server is started up with sqlservr -c -f
, the size of tempdb reverts to 2 MB on the default device. You must manually expand tempdb as necessary after the operations outlined above have been completed successfully.
Article ID: 173090 - Last Review: February 24, 2004 - Revision: 4.0
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
|kbhowto kbinfo kbtshoot KB173090|Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.