The script used to obtain the connection information of the SQL Server server (improved on the basis of the original login) and the script used to query the blocking and deadlock information of the SQL Server

-- Script used to obtain the connection information of the SQL Server server (based on the original shard creation and writing)

Declare
@ Dbname sysname,
-- The name of the database to be queried (empty for all). The connection information of all databases is queried by default.
@ Brief deip bit
-- Whether to display the IP address (0 NO, 1 Yes). This control is added because the IP address query is time-consuming.

Select @ dbname = NULL, @ brief deip = 1

Declare @ dbid int
Set @ dbid = db_id (@ dbname)
Create Table # TB
(ID int identity (128), dbname sysname, hostname nchar (128), loginname nchar ),
Net_address nchar (12), net_ip nvarchar (15), prog_name nchar (128 ))
Insert into # Tb (hostname, dbname, net_address, loginname, prog_name)
Select distinct hostname, db_name (dbid), net_address, loginame, program_name
From master .. sysprocesses
Where hostname <> ''and (@ dbid is null or dbid = @ dbid)
If @ includeip = 0 goto lb_show
-- If the IP address is not displayed, the IP address is displayed directly.
Declare @ SQL varchar (500), @ hostname nchar (128), @ ID int
Create Table # IP (hostname nchar (128), a varchar (200 ))
Declare TB cursor local for select distinct hostname from # TB
Open TB
Fetch next from TB into @ hostname
While @ fetch_status = 0
Begin
Set @ SQL = 'ping' + @ hostname + '-a-n 1-l 1'
Insert # IP (a) exec master .. xp_mongoshell @ SQL
Update # IP Set hostname = @ hostname where hostname is null
Fetch next from TB into @ hostname
End
Update # TB set net_ip = left (A, patindex ('%: %', a)-1)
From # TB a inner join (
Select hostname, A = substring (A, patindex ('Ping statistics for %: % ', a) + 20, 20)
From # IP
Where a like 'Ping statistics for %: % ') B on A. hostname = B. hostname
Drop table # IP

Lb_show:
Select ID, database name = dbname, client name = hostname, user name = loginname
, Nic physical address = net_address, IP address = net_ip, application name = prog_name
From # TB

Drop table # TB
-- Query Result:

 

Bytes ------------------------------------------------------------------------------------------------------------

---- A script used to query the blocking and deadlock information of the SQL Server

Declare @ spid int, @ BL int,
@ Inttransactioncountonentry int,
@ Introwcount int,
@ Intcountproperties int,
@ Intcounter int
 
Create Table # tmp_lock_who (
Id int identity (1, 1 ),
Spid smallint,
BL smallint)
 
Insert into # tmp_lock_who (spid, BL) Select 0, blocked
From (select * From sysprocesses where blocked> 0)
Where not exists (select * from (select * From sysprocesses where blocked> 0) B
Where a. Blocked = spid)
Union select spid, blocked from sysprocesses where blocked> 0
 
-- Find the number of records in the temporary table
Select @ intcountproperties = count (*), @ intcounter = 1
From # tmp_lock_who
 
If @ intcountproperties = 0
Select n'no blocking and deadlock information' as message
-- Start of Loop
While @ intcounter <= @ intcountproperties
Begin
-- Retrieve the first record
Select @ spid = spid, @ BL = bl
From # tmp_lock_who where id = @ intcounter
Begin
If @ spid = 0
Select n 'causes database deadlocks:' + Cast (@ BL as varchar (10) + N' process number. The SQL syntax executed is as follows'
Else
Select n 'process No. spid: '+ Cast (@ spid as varchar (10) + N' by' + N' process no. spid: '+ Cast (@ BL as varchar (10) + N' blocking. The SQL syntax executed by the current process is as follows'
DBCC inputbuffer (@ BL)
End
 
-- Move the loop pointer down
Set @ intcounter = @ intcounter + 1
End
 
Drop table # tmp_lock_who

 

Note: The script for killing related sessions is kill spid. Note that spid is a constant and cannot be a variable. To use a variable, use a dynamic statement.