Just another WordPress.com site

SQL Server

Post navigation

In a new environment I find I waste a lot of time trying to figure out what everybody already knows. For example, what SQL Server instances are running on a given machine? Is Reporting Services running on this server?

Here’s a handy little script you can paste into a PowerShell window and see what’s on any computer. With a little tinkering you can get it to work on a list of servers — but that’s for another day. Here’s the script:

get-service -computername DWHSQL1`

-displayname “*sql*” `

sort-object -property DisplayName `

format-table -auto

And the results:

Status

Name

DisplayName

Stopped

TSSQLPublisher$Default DM TS

SQL Publisher [Default]

Stopped

TSSQLListener$Default DM TS

SQL TCP/IP Listener Service [Default]

Stopped

MSSQL$DWHSQL2

SQL Server (DWHSQL2)

Running

MSSQLSERVER

SQL Server (MSSQLSERVER)

Stopped

MSSQLServerADHelper

SQL Server Active Directory Helper

Stopped

SQLAgent$DWHSQL2

SQL Server Agent (DWHSQL2)

Running

SQLSERVERAGENT

SQL Server Agent (MSSQLSERVER)

Running

SQLBrowser

SQL Server Browser

Stopped

msftesql$DWHSQL2

SQL Server FullText Search (DWHSQL2)

As you can see, there are two instances on the server but only one is running, the default instance, DWHSQL1.

If you need to look at another server simply change the computername. You can modify the filtered column (displayname) to suit your needs as well.

You could get the same information by remoting in to the box and looking at services, but this is quicker and easier, especially when you are dealing with several servers.