Saturday, July 3, 2010

SQL WMI : WQL - Select * From SqlService

One of frequently used WQL (WMI Query Language) for SQL service monitoring is "SELECT * FROM SQLSERVICE" against SQL WMI provider.


In order to select service instances for particular service type, one can limit the service type as follows:

SELECT * FROM SqlService WHERE SQLServiceType = '1'

The following list shows all service types at this point.

1 : MSSQL Service
2 : SQL Agent
3 : Full-text Search
4 : DTS Service (SSIS)
5 : OLAP Service
6 : Report Service
7 : SQL Browser
8 : AD Helper Service
9 : FD Launcher Service (full-text filter daemon launcher)

---- Examples ----

PS> gwmi -namespace "root\Microsoft\SqlServer\ComputerManagement10" -query "SELECT * FROM SqlService"
PS> gwmi -namespace "root\Microsoft\SqlServer\ComputerManagement10" -query "SELECT * FROM SqlService WHERE SqlServiceType='1'"



PS> $ssis = gwmi -namespace "root\Microsoft\SqlServer\ComputerManagement11" -class SqlService -filter SqlServiceType=4

PS> $ssis.StopService()

PS> $ssis.StartService()