dcsimg

Using Windows PowerShell to get SQL Server connection information

November 5, 2008

Part I of this series illustrated the first and most important check on SQL Server–how to ping the host. Part 2 examined how to check all of the windows services related to SQL Server and part 3 discussed how to check the hardware and operating system information. Part 4 illustrated how to get hard disk and network adapter information from the host machine. Part 5 demonstrated how we could connect to SQL Server and how we could query SQL Server related properties and configuration. Part 7 of the article series illustrated how to get information on the TOP 10 queries, based on the CPU usage. This installment of the series discusses how to get all the connection information.

Step 1

Type or copy and paste the following code to C:\CheckSQLServer\Check_sp_who2.ps1.

function check_sp_who2(
[string] $servername
)
{
 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$DataSet = New-Object System.Data.DataSet
 
$SqlConnection.ConnectionString = 
    "Server=$servername;Database=master;Integrated Security=True"
 
$SqlCmd.CommandText = "
 
If LEFT(convert(varchar(100),
    SERVERPROPERTY('productversion')),1) in ('9','1')
begin 
 
SELECT 
                   SPID = s.session_id,
                   Status = UPPER(COALESCE
                      (
                                      r.status,
                                      ot.task_state,
                                      s.status, 
                      '')),
                   [Login] = s.login_name,
                   HostName = COALESCE
                      (
                                     s.[host_name],
                                     '  .'
                      ),
                   BlkBy = COALESCE(RTRIM
                      (
                                     NULLIF(r.blocking_session_id, 0)),
                                     '  .'
                      ),
                   DBName = COALESCE
                      (
                                     DB_NAME(COALESCE
                                     (
                                                     r.database_id,
                                                     t.database_id
                                     )),
                                     ''
                      ),
                   Command = COALESCE
                     (
                                    r.Command,
                                    r.wait_type,
                                    wt.wait_type,
                                    r.last_wait_type,
                                    ''
                     ),
                   CPUTime = COALESCE
                     (
                                    NULLIF(r.cpu_time, 0), 
                                    NULLIF(s.cpu_time, 0),
                                    NULLIF(s.total_scheduled_time, 0), 
                                    NULLIF(tt.CPU_Time, 0),
                                    0
                     ),
                   DiskIO = COALESCE
                     (
                                    NULLIF(r.reads + r.writes, 0),
                                    NULLIF(s.reads + s.writes, 0),
                                    NULLIF(c.num_reads + c.num_writes, 0),
                                    0
                     ),
                   LastBatch = COALESCE
                     (
                                    r.start_time,
                                    s.last_request_start_time
                     ),
                   ProgramName = COALESCE
                     (
                                    s.program_name, 
                                    ''
                     ),
                   SPID = s.session_id,
                   REQUESTID = 0
           FROM
                           sys.dm_exec_sessions s
           LEFT OUTER JOIN
                           sys.dm_exec_requests r
           ON
                           s.session_id = r.session_id
           LEFT OUTER JOIN
                           sys.dm_exec_connections c
           ON
                           s.session_id = c.session_id
           LEFT OUTER JOIN
           (
                           SELECT 
                                           request_session_id,
                                           database_id = MAX(resource_database_id)
                           FROM
                                           sys.dm_tran_locks
                           GROUP BY
                                           request_session_id
           ) t
           ON
                           s.session_id = t.request_session_id
           LEFT OUTER JOIN
                           sys.dm_os_waiting_tasks wt
           ON 
                           s.session_id = wt.session_id
           LEFT OUTER JOIN
                           sys.dm_os_tasks ot
           ON 
                           s.session_id = ot.session_id
           LEFT OUTER JOIN
           (
                           SELECT
                                           ot.session_id,
                                           CPU_Time = MAX(usermode_time)
                           FROM
                                           sys.dm_os_tasks ot
                           INNER JOIN
                                           sys.dm_os_workers ow
                           ON
                                           ot.worker_address = ow.worker_address
                           INNER JOIN
                                           sys.dm_os_threads oth
                           ON
                                           ow.thread_address = oth.thread_address
                           GROUP BY
                                           ot.session_id
           ) tt
           ON
                           s.session_id = tt.session_id

           ORDER BY
                           s.session_id;
end
else
begin
exec master..sp_who2
end
 
"
 
$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)|out-null
$dbs =$DataSet.Tables[0]
 
$dbs 
 
 
$SqlConnection.Close()
 
}

Step 2

Append C:\CheckSQLServer\CheckSQL_Lib.ps1 with the following code.

../check_sp_who2.ps1

Now C:\CheckSQLServer\CheckSQL_Lib.ps1 will have pinghost, checkservices, checkhardware, checkOS, checkHD, checknet, checkinstance, Checkconfiguration and checkdatabases as shown below.

#Source all the functions relate to CheckSQL
. ./PingHost.ps1
. ./checkservices.ps1
. ./checkhardware.ps1
. ./checkOS.ps1
. ./checkHD.ps1
. ./checknet.ps1
. ./checkinstance.ps1
. ./checkconfiguration.ps1
. ./checkdatabases.ps1
. ./checktopqueries.ps1
. ./check_sp_who2.ps1

Note: This CheckSQL_Lib.ps1 will be updated with sourcing of new scripts, such as checktopqueries.ps1 and check_sp_who2.ps1.

Step 3

Append C:\CheckSQLServer\CheckSQLServer.ps1 with the following code.

Write-host "Checking sp_who2"
Write-host "............................."
 
check_sp_who2 $instancename |format-table

The CheckSQLServer.ps1 will become:

#Host, instances and databases.
#Author: MAK
#Date Written: June 5, 2008
param (
  [string] $Hostname, 
  [string] $instancename
)
$global:errorvar=0
. ./CheckSQL_Lib.ps1 
 
Write-host "Checking SQL Server....."
Write-host "........................"
Write-host " "
Write-host "Arguments accepted : $Hostname"
write-host "........................"
Write-host "Pinging the host machine"
write-host "........................"
pinghost $Hostname
 
if ($global:errorvar -ne "host not reachable")
{
Write-host "Checking windows services on the host related to SQL Server"
write-host "..........................................................."
checkservices $Hostname
Write-host "Checking hardware Information....."
Write-host ".................................."
checkhardware $Hostname
Write-host "Checking OS Information....."
Write-host "............................."
checkOS $Hostname
 
Write-host "Checking HDD Information....."
Write-host "............................."
checkHD $Hostname
 
Write-host "Checking Network Adapter Information....."
Write-host "........................................."
checknet $Hostname
Write-host "Checking Configuration information....."
Write-host "........................................."
checkconfiguration $instancename |format-table
 
Write-host "Checking Instance property Information....."
Write-host "............................."
checkinstance $instancename |format-table
 
Write-host "Checking SQL Server databases....."
Write-host "Checking Database status and size....."
 
Write-host "............................."
checkdatabases $instancename |format-table
 
Write-host "Checking Top 10 Queries based on CPU Usage."
Write-host "............................."
 
checktopqueries $instancename |select-object query_text,avgcputime |format-table 
 
Write-host "Checking sp_who2"
Write-host "............................."
 
check_sp_who2 $instancename |format-table
 
}

Note: This CheckSQLServer.ps1 will be updated with new conditions and parameters in future installments of this article series as well.

Sourcing loads the functions listed in the script file and makes it available during the entire PowerShell session. In this case, we are sourcing a script, which in turn is going to source many scripts.

Step 4

Now let us execute the script, CheckSQLServer.ps1, by passing “PowerServer3” host as the argument and Powerserver3\SQL2008 instance as the second argument as shown below.

./CheckSQLServer.ps1 PowerServer3 PowerServer3\SQL2008

You will get the following results. [Refer Fig 1.0]

Checking sp_who2
.............................
 
WARNING: 4 columns do not fit into the display and were removed.
 
SPID Status     Login                      HostName     BlkBy DBName
---- ------     -----                      --------     ----- ------
   1 BACKGROUND sa                           .            .   master
   2 BACKGROUND sa                           .            .   master
   3 BACKGROUND sa                           .            .   master
   4 BACKGROUND sa                           .            .   master
   5 BACKGROUND sa                           .            .   master
   6 BACKGROUND sa                           .            .   master
   7 BACKGROUND sa                           .            .   master
   8 SLEEPING   sa                           .            .   master
   9 BACKGROUND sa                           .            .   master
  10 BACKGROUND sa                           .            .   master
  11 BACKGROUND sa                           .            .   master
  12 BACKGROUND sa                           .            .   master
  13 BACKGROUND sa                           .            .   master
  14 BACKGROUND sa                           .            .   master
  15 BACKGROUND sa                           .            .   master

execute the script, CheckSQLServer.ps1, by passing “PowerServer3” host as the argument and Powerserver3\SQL2008 instance as the second argument
Figure 1.0

Step 5

Now let’s execute the script on a machine that doesn’t exist, as shown below.

./CheckSQLServer.ps1 TestServer testserver

The results are shown below. [Refer Fig 1.1]

execute the script on a machine that doesn’t exist
Figure 1.1

Conclusion

This is the eighth part of the article series “Check your SQL Server using Windows PowerShell”. The article illustrated how to get all the connection information from the SQL Server.

Download the scripts for this article from here.

» See All Articles by Columnist MAK








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers