Starting and stopping engines with sp_engine

You can dynamically stop or start engines using sp_engine, which allows a system administrator to reconfigure CPU resources as processing requirements fluctuate over time.

The syntax for sp_engine is:

sp_engine {“online” | [offline | can_offline] [, engine_id] | 
[“shutdown”, engine_id]

For example, the following brings engine 1 online. Messages are platform specific (in this example, Sun Solaris was used):

sp_engine "online", 1 
02:00000:00000:2001/10/26 08:53:40.61 kernel  Network and device connection limit is 3042.
02:00000:00000:2001/10/26 08:53:40.61 kernel  SSL Plus security modules loaded successfully.
02:00000:00000:2001/10/26 08:53:40.67 kernel  engine 1, os pid 8624  online
02:00000:00000:2001/10/26 08:53:40.67 kernel  Enabling Sun Kernel asynchronous disk I/O strategy
00:00000:00000:2001/10/26 08:53:40.70 kernel  ncheck: Network fc0330c8 online

You can check whether or not a specific engine can be brought offline with the can_offline parameter. The following determines whether engine 1 can be brought offline:

sp_engine can_offline, 1

sp_engine specifies a return code of 0 if you can bring the specified engine offline. If you do not specify an engine_id, sp_engine describes the status of the engine in sysengines with the highest engine_id.

Engines can be brought online only if max online engines is greater then the current number of engines with an online status, and if enough CPU is available to support the additional engine.

To bring an engine offline, enter the engine ID. The following takes engine number one offline:

sp_engine offline, 1

Adaptive Server waits for any tasks that are associated with this engine to finish before taking the engine offline, and returns a message similar to the following:

01:00000:00000:2001/11/09 16:11:11.85 kernel  Engine 1 waiting for affinitied process(es) before going offline
00:00000:00000:2001/11/09 16:16:01.90 kernel  engine 1, os pid 21127  offline

You cannot take engine number zero offline.

sp_engine “shutdown” forces any tasks associated with the specified engine to finish in a five-second period, and then shuts down the engine. You can use sp_engine shutdown when an engine has gone into a dormant state. Although it is perfectly acceptable to leave an engine in dormant state, you can use this command if you need to bring an engine offline. sp_engine kills any remaining processes that are preventing the engine from going offline normally. The following shuts down engine 1:

sp_engine "shutdown", 1

For more information about sp_engine, see the Reference Manual.


Relationship between network connections and engines

Due to the operating system limit on the number of file descriptors per process on UNIX, reducing the number of engines reduces the number of network connections that the server can have. On Windows, the number of network connections is independent of the number of engines.

There is no way to migrate a network connection created for server-to-server remote procedure calls—or example, connections to Replication Server and XP Server—so you cannot take an engine offline that is managing one of these connections.


Logical process management and dbcc engine(offline)

If you are using logical process management to bind particular logins or applications to engine groups, use dbcc engine(offline) carefully. If you take all engines for an engine group offline:

Since engine affinity is assigned when a client logs in, users who are already logged in are not migrated if the engines in the engine group are brought online again with dbcc engine("online").