Database problems 5-sys.sysprocesses using and finding deadlocks

Http://blog.sina.com.cn/s/blog_62c4727d0100jc5z.html

(a) part of the theorysys.sysprocesses (Transact-SQL)http://technet.microsoft.com/zh-tw/library/ms179881.aspx

Contains information about the order of the processes that are executed on the SQL Server line. These processes can be either user-ordered or system-ordered. To access sysprocesses, you must be in the master repository, or you must use the master.dbo.sysprocesses three-part name.

Important things:

This SQL Server 2000 System Information table contains overviews of backtracking compatibility. We recommend that you use the current SQL Server system Check table instead. To find an equivalent system check or check, please refer to the SQL Server 2000 System Information table to the SQL Server 2005 System Check table. Future versions of Microsoft SQL Server will remove this feature. Avoid using this feature in new development work, and plan to modify the application that currently uses this feature.

Information Line name	type of information	Description
spid	smallint	SQL Server work stage knowledge.
Kpid	smallint	Windows performs the knowledge code.
Blocked	smallint	Lock the knowledge of the required work stage. If this information line is NULL, it means that there is no lock-in required, or that the work-stage information in the lock-up stage is not used (or is not known). 2 = Lock resource is provided by the abandoned decentralized exchange. -3 = Lock resource is available from the deferred exchange. -4 = 閂 locked in the inner part of the lock, but not the lock 閂 Lock the work of the person's knowledge.
Waittype	Binary (2)	is reserved.
Waittime	bigint	The current waiting time (in milliseconds). 0 = The process order is not in wait.
Lastwaittype	NCHAR (32)	A string that indicates the name of the last or current waiting type.
Waitresource	NCHAR (256)	The text representation of the lock resource.
dbid	smallint	The information that is currently used in the sequencing.
Uid	smallint	The user knowledge of the Execute command. If the number of users and roles exceeds 32,767, it will cause overflow or return NULL. For detailed information, please refer to the SQL Server system catalog .
Cpu	Int	CPU time for sequencing. Regardless of the SET STATISTICS time option, which is on or OFF, all of the items in the order are updated.
Physical_io	Int	Read and write the loaded disks in the process sequence.
Memusage	Int	The page that is currently configured for this order in the program cache. The negative indicates that the order is being released in the memory of other sequencing configurations.
Login_time	Datetime	The time to log in to the server using the client-side sequence. If it is a system order, it will save the time that SQL Server originated.
Last_batch	Datetime	The last time the user has executed the remote memory call or the EXECUTE statement in the client-side process. If it is a system order, it will save the time that SQL Server originated.
Ecid	smallint	Use the unique knowledge to represent the code that performs the work of the child of the single order operation.
Open_tran	smallint	The opening transaction number of the order.
Status	NCHAR (30)	Process the order of knowledge. Possible values are: dormant = SQL Server is resetting the work stage. Running = working stage is executing one or more batches. After you have multiple Active Result Set (MARS), you can execute multiple batches in the work stage. For details, please refer to using multiple Active Result sets (MARS). background = Working stage is doing background work, such as dead-end reconnaissance. rollback = The work stage is being traded back. Pending = Working stage is waiting for a worker to become available. runnable = The work in the work stage is located in the NIN column of the scheduler when waiting for the time to be achieved. Spinloop = Work in the work stage is waiting for a single-threading access lock to become available. suspended = The work stage is waiting for events (such as I/O) to complete.
Sid	Binary (86)	The domain-wide unique knowledge (GUID) of the consumer.
Hostname	NCHAR (128)	The name of the workstation.
Program_name	NCHAR (128)	The name of the application.
Hostprocess	NCHAR (10)	The workstation processes the sequencing knowledge.
Cmd	NCHAR (16)	The command currently being executed.
Nt_domain	NCHAR (128)	Client's Windows domain (if you use Windows certification) or trust connection.
Nt_username	NCHAR (128)	The Windows consumer name of the process (if you use Windows certification) or a trust connection.
Net_address	NCHAR (12)	The unique knowledge of the network adapter assigned on each user's workstation. When the user is logged in, this knowledge will be inserted into the net_address information line.
Net_Library	NCHAR (12)	The storage bank of the user's web-site. When the internet is connected, every user-order is sent. The internet is connected to the Web-Library, which allows them to connect. For detailed information, please refer to the Internet channel and TDS ports .
Loginame	NCHAR (128)	Log in name.
Context_info	Binary (128)	Use SET context_info to save the information in batches.
Sql_handle	Binary (20)	Represents a batch or object that is currently being executed. Note that this value is derived from the object's batch or memory address, rather than using the SQL Server Minatomachi River algorithm.
Stmt_start	Int	Starts the specified sql_handle of the current SQL statement of displacement.
Stmt_end	Int	Ends the displacement of the current SQL statement of the specified sql_handle. -1 = At the end of the Fn_get_sql function of the specified sql_handle.
request_id	Int	Require knowledge of the password. The requirements to be executed in a particular work stage.

Registration

If the consumer has VIEW SERVER state rights for the server, the user will see all the working stages in the SQL SERVER implementation, or the user will only see the current working stage.

(ii) part of the war

(2.1) Restore the database without special access, especially when recovering the database, error: (The database is being used by another user)

Use master

Go

Select spid

From sys.sysprocesses

where dbid=db_id (' db_sunrise ') and spid<>@ @spid

KILL 51;

KILL-statusonly with;

GO

--Returns the session ID, login name, and user name of the current user process.

SELECT @ @SPID as ' ID ',

System_user as ' Login Name ',

User as ' user Name '

--If the process is much more, you can perform

DECLARE @sql varchar (8000)

Select @sql =coalesce (@sql, ') + ' Kill ' +cast (spid as VARCHAR (10)) + '; '

From sys.sysprocesses WHERE dbid=db_id (' Db_wip ')

EXEC (@sql)

(2.2) Search for deadlock, solve the deadlock

Concept:

Deadlocks are a state that can occur in any multithreaded system, not just in a relational database management system. A thread in a multithreaded system may acquire one or more resources, such as locks. If the resource being fetched is currently owned by another thread, the first one may have to wait for the owning thread to release the target resource. It is said that the wait thread is relevant to the owning thread on that particular resource.

If the owning thread needs to acquire another resource that is currently owned by the waiting thread, this situation becomes a deadlock: No two threads can release the resource until the transaction commits or rolls back, and they cannot commit or roll back the transaction because they are waiting for the resource owned by the other. For example, a thread running transaction 1 T1 has an exclusive lock on the Supplier table. The thread that runs transaction 2 T2 has an exclusive lock on the part table, and then requires a lock on the Supplier table. Transaction 2 cannot obtain this lock because transaction 1 already owns it. Transaction 2 is blocked, waiting for transaction 1. Then, transaction 1 requires a lock on the part table, but the lock cannot be obtained because transaction 2 locks it. A transaction cannot release a held lock until it is committed or rolled back. Because transactions require a lock that is controlled by the other to continue, they cannot be committed or rolled back.

Deadlocks are often confused with normal blocking. A second transaction waits for a lock to be freed when one transaction locks the resource that another transaction requires. By default, SQL Server transactions do not time out (unless Lock_timeout is set). The second transaction is blocked, not a deadlock.
Use master

Go

declare @spid int, @bl int

DECLARE s_cur cursor FOR

Select 0, blocked

From (SELECT * from sysprocesses where blocked>0) a

Where NOT EXISTS (SELECT * from sysprocesses where blocked>0) b where a.blocked=spid)

Union

Select spid,blocked from sysprocesses where blocked>0

Open S_cur

FETCH NEXT from S_cur to @spid, @bl

While @ @fetch_status = 0

Begin

If @spid =0

Select N ' cause the number of deadlocks is: ' +ltrim (@bl) +n ' process number, its SQL syntax is as follows '

Else

Select N ' Process number: ' + ltrim (@bl) +n ' is blocked by ' +n ' process number: ' +ltrim (@bl) +n ', and the SQL syntax for the current process is as follows: '

DBCC INPUTBUFFER (@bl)

FETCH NEXT from S_cur to @spid, @bl

End

Close S_cur

Deallocate s_cur

(2.3) Get information about connecting to SQL Server

If object_id (' P_getlinkinfo ', ' P ') is not null drop proc P_getlinkinfo

Go

Create proc P_getlinkinfo

@dbname Sysname=null,--the name of the library to be looked at, which implied that all

@includeip bit=0--Whether IP information is displayed

As

Begin

DECLARE @dbid int

Set @dbid =db_id (@dbname)

If object_id (' tempdb.. #tb ') is not a null drop table #tb

If object_id (' tempdb.. #ip ') is not a null drop table #ip

CREATE TABLE #tb

(ID int identity (),

dbname sysname,

Hostname nchar (128),

LoginName nchar (128),

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 [email protected])

If @includeip =0 goto lb_show--Do not show ip

DECLARE @sql varchar, @hostname nchar (+), @id int

CREATE TABLE #ip (hostname nchar (+), a varchar (200))

Declare TB cursor Local for SELECT distinct hostname from #tb

Open TB

FETCH NEXT from TB to @hostname

While @ @fetch_status =0

Begin

Set @sql = ' ping '[email protected]+ ' -a-n 1-l 1 '

Insert #ip (a) EXEC master: xp_cmdshell @sql

Update #ip set [email protected] where hostname is NULL

FETCH NEXT from TB to @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

Lb_show:

Select ID,

dbname

Hostname

LoginName

Net_address,

NET_IP,

Prog_name

From #tb

End

Go

exec p_getlinkinfo @dbname = ' Db_wip ', @includeip =1

Database problems 5-sys.sysprocesses using and finding deadlocks