INF: Changing Configuration Values When SQL Server Won't Start

MORE INFORMATION

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
  -or-
  
  
• 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. 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).

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 the sp_configure parameters so that the MSSQLServer service will be able to start:

1. 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.
2. 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
3. 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
   						

4. Run the RECONFIGURE command to apply the changes, as in the following example:

         reconfigure with override
         go
   						

5. Execute the SHUTDOWN command.
6. 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.

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.