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.
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.
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:
The login or application can run on any engine
An advisory message is sent to the connection logging in to the server
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").
