SQL SERVER 2005 SYS.SYSPROCESSES 的使用
(一)理論部份
sysprocesses 表中保存關(guān)于運行在 Microsoft® SQL Server™ 上的進(jìn)程的信息。這些進(jìn)程可以是客戶端進(jìn)程或系統(tǒng)進(jìn)程。sysprocesses 只存儲在 master 數(shù)據(jù)庫中。
Sysprocesses各部份作用:
字段 數(shù)據(jù)類型 描述
spid smallint 進(jìn)程ID
kpid smallint 線程ID
blocked smallint 分塊進(jìn)程ID (spid)
waittype binary(2) 保留
waittime int 當(dāng)前等待時間（以毫秒為單位）當(dāng)進(jìn)程不處于等待時，為 0。
lastwaittype nchar(32) 表示上次或當(dāng)前等待類型名稱的字符串。
waitresource nchar(32) 鎖資源的文本化表示法。
dbid smallint 當(dāng)前正由進(jìn)程使用的數(shù)據(jù)庫 ID。
uid smallint 執(zhí)行命令的用戶 ID。
cpu int 進(jìn)程的累計CPU時間無論SET STATISTICS TIME ON選項是ON還是OFF都為所有進(jìn)程更新該條目。
physical_io int 進(jìn)程的累計磁盤讀取和寫入。
memusage int 當(dāng)前分配給該進(jìn)程的過程高速緩存中的頁數(shù)。一個負(fù)數(shù)，表示進(jìn)程正在釋放由另一個進(jìn)程分配的內(nèi)存。
login_time datetime 客戶端進(jìn)程登錄到服務(wù)器的時間。對于系統(tǒng)進(jìn)程，是存儲 SQL Server 啟動發(fā)生的時間。
last_batch datetime 客戶端進(jìn)程上次執(zhí)行遠(yuǎn)程存儲過程調(diào)用或 EXECUTE 語句的時間。對于系統(tǒng)進(jìn)程，是存儲 SQL Server 啟動發(fā)生的時間。
ecid smallint 用于唯一標(biāo)識代表單個進(jìn)程進(jìn)行操作的子線程的執(zhí)行上下文 ID。
open_tran smallint 進(jìn)程的打開事務(wù)數(shù)。
status nchar(30) 進(jìn)程 ID 狀態(tài)（如運行、休眠等）。
sid binary(85) 用戶的全局唯一標(biāo)識符 (GUID)。
hostname nchar(128) 工作站的名稱。
program_name nchar(128) 應(yīng)用程序的名稱。
hostprocess nchar(8) 工作站進(jìn)程 ID 號。
cmd nchar(16) 當(dāng)前正在執(zhí)行的命令。
nt_domain nchar(128) 客戶端的 Windows NT 4.0 域（如果使用 Windows 身份驗證）或信任連接的 Windows NT 4.0 域。
nt_username nchar(128) 進(jìn)程的 Windows NT 4.0用戶名（如果使用 Windows 身份驗證）或信任連接的 Windows NT 4.0 用戶名。
net_address nchar(12) 指派給每個用戶工作站上的網(wǎng)絡(luò)接口卡唯一標(biāo)識符。當(dāng)用戶登錄時，該標(biāo)識符插入 net_address 列。
net_library nchar(12) 用于存儲客戶端網(wǎng)絡(luò)庫的列。每個客戶端進(jìn)程都在網(wǎng)絡(luò)連接上進(jìn)入。網(wǎng)絡(luò)連接有一個與這些進(jìn)程關(guān)聯(lián)的網(wǎng)絡(luò)庫，該網(wǎng)絡(luò)庫使得這些進(jìn)程可以建立連接。有關(guān)更多信息，請參見客戶端和服務(wù)器 Net-Library。
loginame nchar(128) 登錄名。
(二)實戰(zhàn)部份
(2.1)恢復(fù)數(shù)據(jù)庫而不能獲得專門的訪問，特別是恢復(fù)數(shù)據(jù)庫時候，報錯：（數(shù)據(jù)庫正在被其他用戶使用）
use master
go
select spid
from sys.sysprocesses
where dbid=db_id('db_Sunrise') and spid<>@@spid
/*
spid
------
51
52
57
58
59
61
62
64
*/
KILL 51;
KILL 52 WITH STATUSONLY;
GO
--返回當(dāng)前用戶進(jìn)程的會話 ID、登錄名和用戶名。
SELECT @@SPID AS 'ID',
SYSTEM_USER AS 'Login Name',
USER AS 'User Name'
--如果進(jìn)程比較多,可以動態(tài)執(zhí)行
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)查詢死鎖,解決死鎖
概念:
死鎖是一種可能發(fā)生在任何多線程系統(tǒng)中的狀態(tài)，而不僅僅發(fā)生在關(guān)系數(shù)據(jù)庫管理系統(tǒng)中。多線程系統(tǒng)中的一個線程可能獲取一個或多個資源（如鎖）。如果正獲取的資源當(dāng)前為另一線程所擁有，則第一個線程可能必須等待擁有線程釋放目標(biāo)資源。這時就說等待線程在那個特定資源上與擁有線程有相關(guān)性。
如果擁有線程需要獲取另外一個資源，而該資源當(dāng)前為等待線程所擁有，則這種情形將成為死鎖：在事務(wù)提交或回滾之前兩個線程都不能釋放資源，而且它們因為正等待對方擁有的資源而不能提交或回滾事務(wù)。例如，運行事務(wù) 1 的線程 T1 具有 Supplier 表上的排它鎖。運行事務(wù) 2 的線程 T2 具有 Part 表上的排它鎖，并且之后需要 Supplier 表上的鎖。事務(wù) 2 無法獲得這一鎖，因為事務(wù) 1 已擁有它。事務(wù) 2 被阻塞，等待事務(wù) 1。然后，事務(wù) 1 需要 Part 表的鎖，但無法獲得鎖，因為事務(wù) 2 將它鎖定了。事務(wù)在提交或回滾之前不能釋放持有的鎖。因為事務(wù)需要對方控制的鎖才能繼續(xù)操作，所以它們不能提交或回滾。
死鎖經(jīng)常與正常阻塞混淆。當(dāng)一個事務(wù)鎖定了另一個事務(wù)需要的資源，第二個事務(wù)等待鎖被釋放。默認(rèn)情況下，SQL Server 事務(wù)不會超時（除非設(shè)置了 LOCK_TIMEOUT）。第二個事務(wù)被阻塞，而不是被死鎖。
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 (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 into @spid,@bl
while @@fetch_status = 0
begin
if @spid =0
select N'引起數(shù)死鎖的是:'+ltrim(@bl)+N'進(jìn)程號,其執(zhí)SQL語法如下'
else
select N'進(jìn)程號：'+ ltrim(@bl)+N'被'+N'進(jìn)程號：'+ltrim(@bl)+N'阻塞,其當(dāng)前進(jìn) 進(jìn)程執(zhí)行的SQL語法如下:'
dbcc inputbuffer (@bl )
fetch next from s_cur into @spid,@bl
end
close s_cur
deallocate s_cur
(2.3) 獲取連接SQL服務(wù)器的信息
if object_id('p_getlinkinfo','P')is not null drop proc p_getlinkinfo
go
create proc p_getlinkinfo
@dbname sysname=null, --要查詢的數(shù)據(jù)庫名,默認(rèn)表示所有
@includeip bit=0 --是否顯示IP信息
as
begin
declare @dbid int
set @dbid=db_id(@dbname)
if object_id('tempdb..#tb')is not null drop table #tb
if object_id('tempdb..#ip')is not null drop table #ip
create table #tb
(id int identity(1,1),
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 dbid=@dbid)
if @includeip=0 goto lb_show --不顯示IP
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_cmdshell @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
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
