Summary

The SQL Server tools are top notch & one of the tools is "sqlservr.exe" which starts, stops, pauses, and continues Database Engine from the command prompt. This article describes how to start an instance of the Database Engine.

Over-Committing Memory

I had an issue recently where SQL server services didn't started after min & max server memory configuration of SQL server was changed. The Server got configured for AWE along with min & max server memory configuration. The SQL Server didn't start after stopping the services. What I intend to show you here is how problem came to happen & how it got resolved. In this article I'd like to talk about how this over committed memory issue is been addressed when value defined for "max server memory" (RAM) is not physically present on the server. Below topic will take you from Server configuration to Boot.INI file & to enable AWE & configuring server memory using system store procedure sp_configure & finally sqlservr.exe coming in rescue to start SQL server.

My Server Configuration:

1> Windows 2000 Datacenter

2> Physical memory 36GB

3> SQL Server 2000 Enterprise Edition

Configuring AWE for SQL Server 2000

Use of the /PAE switch in the Windows Boot.INI configuration file provides access to physical memory above the 4 GB limit. This is a requirement for AWE memory access above 4 GB.

Any change to Boot.INI file requires server restart\reboot. Now is time to enable AWE on SQL Server 2000 by running "sp_configure" from query analyzer.

The LOCK PAGE IN MEMORY permission must be granted to the SQL Server account before enabling AWE (SQL SERVER 2005); this may be enabled if Windows 2003 is on SP1

(USE MASTER)
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
-- Note: - max server memory is in MB
sp_configure 'min server memory', 1024
RECONFIGURE
GO
-- 30GB is 30720MB but accidentally I typed 307200, which is 300GB
sp_configure 'max server memory', 307200
RECONFIGURE
GO

I stopped SQL Server & when I started SQL Server 2000, it didn't start, I tried other possibilities but that didn't help.

Solution

To work around the problem, start SQL Server 2000 in minimal configuration mode by using Sqlservr.exe with the -c -f option and reconfigure "max server memory"

For a SQL Server 2000 Default Instance: Navigate to the Binn folder where the SQL Server 2000 default instance is installed and run the following command:

sqlservr.exe -c -f 

For a SQL Server 2000 Named Instance: Navigate to the Binn folder where the SQL Server named instance is installed and run the following command:

sqlservr.exe -c -f -s Instance_Name

Connect to SQL Server through Query Analyzer, and then run this code:

(USE MASTER)
sp_configure 'max server memory', 30720 --- (Which is now 30GB)
RECONFIGURE
GO

Navigate to the command prompt and then press CTRL+C. To

shut down the SQL Server 2000 instance, type:

Y 

After that when you start again SQL Server it will come up fine & your"max server memory" issue is been resolved.

Feedback is highly appreciated.