This article was previously published under Q173090
This article has been archived. It is offered "as is" and will no longer be updated.
This article describes how to change sp_configure parameter values when theMSSQLServer service fails to start after changes have been made to thesp_configure parameters.
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.-or-
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. UsingNotepad, open the file called Errorlog. You can find this file in theLog directory of the SQL Server installation (for example, the Sql60\Logdirectory or the Mssql\Log directory).
The memory 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 thesp_configure parameters so that the MSSQLServer service will be able tostart:
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 failedDB-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 go sp_configure tempdb, 0 go
Run the RECONFIGURE command to apply the changes, as in the following example:
reconfigure with override go
Execute the SHUTDOWN command.
Start the MSSQLServer service; it should succeed.
You can also use these steps for situations where the server may startbut connections cannot be established to SQL Server because of some othersp_configure parameter settings. For example, you can use the methoddescribed in this article when the procedure cache is set too high.
NOTE: 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.