vbscript that maps OS PID to SQL Instance name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

vbscript that maps OS PID to SQL Instance name

Author

Topic

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-17 : 17:14:26

I stumbled across this really useful little script by Bill Wunder the other day when I was attempting to determine which of the 8 SQL instances was hogging all my cpu. Normally I use pslist and process explorer from sysinternals for this type of thing. but this vbscript is simple and just outputs the SQL Server names and their corresponding PIDs.

Here is the original posting by Bill with the code http://sigs.sqlpass.org/Resources/Articles/tabid/35/ctl/ArticleView/mid/349/articleId/40/Default.aspx

I have reproduced the code below for convenience.

Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

'create a file to save the report
Set objLogFile = objFSO.OpenTextFile("c:\SQLserviceList.csv", _
    ForWriting, True)

'report heading
objLogFile.Write ("pid    Service Name")
objLogFile.Writeline
objLogFile.Write ("_______        _______________________")
objLogFile.Writeline

'go after the local server
strComputer = "."

'open the WMI collection
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

'list the process id and name of each SQL Server service
Set colListOfServices = objWMIService.ExecQuery _
        ("SELECT * FROM Win32_Service")

For Each objService in colListOfServices
  If (InStr(UCase(objService.Name),"MSSQL") = 1) _
  OR (InStr(UCase(objService.Name),"SQLAGENT") = 1) Then    
    objLogFile.Write(objService.ProcessID)
    objLogFile.Write(vbTab)
    objLogFile.Write(objService.Name)
    objLogFile.Writeline
  End If
Next
objLogFile.Close

'open the report in notepad
createobject("wscript.shell").run "notepad.exe c:\SQLserviceList.csv",1,true

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources