Monitoring Engine Status

Before you bring an engine online or offline, check the status of the engines that are currently running. sysengines includes any of the following in the status column:

  • online – indicates the engine is online.

  • in offline – indicates that sp_engine offline has been run. The engine is still allocated to the server, but is having its tasks migrated to other engines.

  • in destroy – indicates that all tasks have successfully migrated off the engine, and that the server is waiting on the OS-level task to deallocate the engine.

  • in create – indicates that an engine is being brought online.

  • dormant – indicates that sp_engine offline was not able to migrate all tasks from that engine. If the tasks terminate themselves (through a timeout), engines switch to being permanently offline. Dormant engines process only those tasks that are causing the dormant state; they are not available to work on any other tasks.

The following command shows the engine number, status, number of tasks affinitied, and the time an engine was brought online:
select engine, status, affinitied, starttime 
from sysengines
engine status       affinitied  starttime         
------ ------------ ----------- --------------------------
     0 online                12        Mar  5 2007 9:40PM
     1 online                 9        Mar  5 2007 9:41PM
     2 online                12        Mar  5 2007 9:41PM
     3 online                14        Mar  5 2007 9:51PM
     4 online                 8        Mar  5 2007 9:51PM
     5 in offline            10        Mar  5 2007 9:51PM