The information in this article applies to:
- Microsoft SQL Server, versions 6.0 and 6.5
SUMMARY
This article describes how to change sp_configure parameter values when the
MSSQLServer service fails to start after changes have been made to the
sp_configure parameters.
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 errorlog. Using
Notepad, open up 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:
- From a command prompt on the server, run the following command:
sqlservr -c -f
This will start the server in single-user and minimally configured
modes. It will also 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, because these 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 a command similar to the following:
Sp_configure memory, 8192
Go
Sp_configure tempdb, 0
Go
- Run the RECONFIGURE command to install, 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 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 master device. You must manually expand
tempdb as necessary after the operations outlined above have been completed
successfully.
|