Shulou(Shulou.com)06/03 Report--

When I encounter the need to operate the sqlserver server, I don't even know how to operate the command. Baidu finally found one that can be used for a long time. Although it is not very useful, it is better to record it, otherwise, if I encounter it again in the future, I will need to check it for a long time.

Select * from master..sysprocesses

Introduction of query result field

Spid:Sql Servr session IDKpid:Windows thread IDBlocked: blocking the pleading session ID. If this column is Null, it identifies that the request is not blocked Waittype: the waiting resource number of the current connection, indicating whether to wait for the resource, 0 or Null indicates that there is no need to wait for any resource Waittime: current waiting time, in milliseconds, 0 indicates no waiting DBID: database currently being used by the process IDUID: user executing the command IDLogin_time: the time the client process logged in to the server. Last_batch: the last time the stored procedure or Execute statement was executed. For system processes, the startup time of the Sql Server is stored

10.Open_tran: the number of open transactions of the process. If there is a nested transaction, it will be greater than 1

11.Status: process ID status, dormant = resetting callback; running = callback is running one or more batches; background = callback is running a background task; rollback = session is processing transaction rollback; pending = callback is waiting for work to become readily available; runnable = tasks in the session are in an executable queue waiting to get Scheduler to run Spinloop = tasks in the session are waiting for the spin lock to become available; suspended = the session is waiting for the event to complete

12.Hostname: name of the client workstation that established the link

13.Program_name: the name of the application, which is the Application Name allocated in the connection string

14.Hostprocess: the process ID number of the connected application in the client workstation

15.Cmd: currently executing command

16.Loginame: login name

Application example:

Check to see if the database is blocked

First find out which link has a blocked field that is not 0. For example, the blocked field of SPID53 is not 0, but 52. If the blocked of SPID 52 is 0, it can be concluded that blocking occurs at this time, and 53 is blocked by 52. If you find that the value of the blocked field of a connection is equal to itself, the connection is reading and writing to disk, and it has to wait for its own Ibino to finish.

Find the link on that database

Just check the dbid. To get the dbid, you can run the following query to get the name of the database:

Select name,dbid from master.sys.sysdatabases

Reference link

Https://www.cnblogs.com/zengkefu/p/6854196.html

Https://jingyan.baidu.com/article/b2c186c820b054c46ff6ff68.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.