Encountered the need to operate SQL Server servers, I even command operation will not, Baidu a half-day finally found a can use, although it is not how to use but record a better, or later in the future if you meet and need to check half a day
SELECT * FROM Master. sysprocesses
Query result Field Description
- Spid:sql servr Session ID
- Kpid:windows thread ID
- Blocked: Blocking the plea's session ID. If this column is Null, the identity request is not blocked
- Waittype: The waiting resource number for the current connection, indicating whether to wait for the resource, 0 or null indicates no need to wait for any resources
- Waittime: Current wait time in milliseconds, 0 means no wait
- DBID: The database ID currently being used by the process
- UID: User ID to execute the command
- Login_time: The time that the client process logs on to the server.
- Last_batch: The last time the stored procedure or EXECUTE statement was executed. For system processes, the startup time for SQL Server will be stored
10.open_tran: The number of open transactions for the process. If there is a nested transaction, it will be greater than 1
11.Status: Process ID status, dormant = resetting the reply; Running = reply is running one or more batches; Background = reply is running a background task; Rollback = The session is processing a transaction rollback; Pending = reply is waiting for work to become available; runnable = The task in the session is in an executable queue that is waiting to get Scheduler to run; Spinloop = The task in the session is waiting for the spin lock to become available; Suspended = Session is waiting for event to complete
12.Hostname: Name of the client workstation that establishes the link
13.program_name: The name of the application, which is the application name in the connection string
14.Hostprocess: Process ID Number of the connection application in the client workstation
15.CMD: currently executing command
16.Loginame: Login Name
Application Examples:
- Check if the database is blocked
Find out which Link's blocked field is not 0 first. The blocked field, such as SPID53, is not 0, but 52. The blocked of SPID 52 is 0, so we can conclude that there is a blockage and 53 is blocked by 52. If you find that the value of a connected blocked field equals its own, it means that the connection is doing disk reads and writes, and it waits for its own I/O to finish.
- Find links on that database
Check the dbid. Get dbid, you can run the following query to get the name of the database:
Select Name,dbid from Master.sys.sysdatabases
Reference links
Https://www.cnblogs.com/zengkefu/p/6854196.html
Https://jingyan.baidu.com/article/b2c186c820b054c46ff6ff68.html
SQL Server Operations
