- ดูด้วย Activity Monitor ของ MS SQL Server Management Studio
- ใช้คำสั้ง SQL ไป select ข้อมูลเอง หรือเรียกใช้ stored procedure ของระบบเอง
หรือจะเรียกใช้ของเราที่สร้างขึ้นเอง
Activity Monitor
วิธีที่ 2 ใช้คำสั่ง stored procedure ใน SQL เช่น sp_lock, sp_who, sp_who2 ฯลฯ คำสั่งที่มากับ SQL 2005 ไม่ค่อยแจ๋วเท่าไร ต้องสร้างมาใช้เองดีกว่าอันแรกก็
sp_lock2
จะแสดงข้อมูล process ต่างๆ โดยจะแสดง UOW ด้วย (แก้ไขเพิ่มเติมเอง) ต้นฉบับ
CREATE PROC sp_lock2
(
@dbname sysname = NULL,
@spid int = NULL
)
AS
/*************************************************************************************************
Copyright © 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: To display detailed lock information
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Tested on: SQL Server 7.0 and SQL Server 2000
Date modified: Jan-27-2010 1:30 PM
Email: vyaskn@hotmail.com
Add New column: UOW ให้มันแสดงข้อมูลนี้เพื่อนำไปจัดการกับ spid กำพร้า (พวก -2 เป็นต้น)
by Pom
Examples:
To see all the locks:
EXEC sp_lock2
To see all the locks in a particular database, say 'pubs':
EXEC sp_lock2 pubs
To see all the locks held by a particular spid, say 53:
EXEC sp_lock2 @spid = 53
To see all the locks held by a particular spid (23), in a particular database (pubs):
EXEC sp_lock2 pubs, 23
*************************************************************************************************/
BEGIN
SET NOCOUNT ON
CREATE TABLE #lock
(
spid int,
dbid int,
ObjId int,
IndId int,
Type char(5),
Resource char(20),
Mode char(10),
Status char(10)
)
INSERT INTO #lock EXEC sp_lock
IF @dbname IS NULL
BEGIN
IF @spid IS NULL
BEGIN
SELECT a.spid AS SPID,(SELECT DISTINCT req_transactionUOW from master..syslockinfo where req_spid = a.spid) AS UOW,
(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],
db_name(dbid) AS [Database Name], ISNULL(object_name(ObjId),'') AS [Object Name],IndId, Type, Resource, Mode, Status
FROM #lock a
END
ELSE
BEGIN
SELECT a.spid AS SPID,(SELECT DISTINCT req_transactionUOW from master..syslockinfo where req_spid = a.spid) AS UOW,
(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],
db_name(dbid) AS [Database Name], ISNULL(object_name(ObjId),'') AS [Object Name],IndId,
Type, Resource, Mode, Status
FROM #lock a
WHERE spid = @spid
END
END
ELSE
BEGIN
IF @spid IS NULL
BEGIN
SELECT a.spid AS SPID,(SELECT DISTINCT req_transactionUOW from master..syslockinfo where req_spid = a.spid) AS UOW,
(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],
db_name(dbid) AS [Database Name], ISNULL(object_name(a.ObjId),'') AS [Object Name],a.IndId,
ISNULL((SELECT name FROM sysindexes WHERE id = a.objid and indid = a.indid ),'') AS [Index Name],
a.Type, a.Resource, a.Mode, a.Status
FROM #lock a
WHERE dbid = db_id(@dbname)
END
ELSE
BEGIN
SELECT a.spid AS SPID,(SELECT DISTINCT req_transactionUOW from master..syslockinfo where req_spid = a.spid) AS UOW,
(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],
db_name(dbid) AS [Database Name], ISNULL(object_name(a.ObjId),'') AS [Object Name],a.IndId,
ISNULL((SELECT name FROM sysindexes WHERE id = a.objid and indid = a.indid ),'') AS [Index Name],
a.Type, a.Resource, a.Mode, a.Status
FROM #lock a
WHERE dbid = db_id(@dbname) AND spid = @spid
END
END
DROP TABLE #lock
END
ส่วนนี้ก็เป็นแบบที่หาตัวที่ทำให้เกิด log เลยล่ะมั้ง
Create Table #Tmp
(
spid smallint,
ecid smallint,
status nchar(30),
loginame nchar(128),
hostname nchar(128),
blk char(5),
dbname nchar(128),
cmd nchar(128),
request_id smallint
)
Create Table #TmpLocks
(
spid smallint,
dbid smallint,
ObjId int,
IndId smallint,
Type nchar(4),
Resource nchar(16),
Mode nvarchar(8),
Status nvarchar(28)
)
Insert Into #Tmp
Exec sp_who
Insert Into #TmpLocks
Exec sp_lock
--If (
Select TL.spid,Count(*) From #Tmp T Join #TmpLocks TL On T.spid = TL.spid
Where /*This is for tempdb*/ dbid = 2 And objid In (1, 2, 3)
group by TL.spid
--) > 0
--Then you can kill the concerned spid with the command :
--Kill — The concerned spid
Drop Table #Tmp
Drop Table #TmpLocks ต้นฉบับ
ลำดับต่อมา วิธีจัดการกับ spid -2
use master
go
select req_transactionUOW from syslockinfo where req_spid = -2
go
Use KILL 'UOW' to terminate orphaned DTC transactions
KILL 'FE4A57F2-28C5-44F9-8416-B08760DFE7E9'
Once the KILL process completes successfully then you can be assured by referring to SQL error log for following text:
Spid 60 tried to abort the distributed transaction with UOW {FE4A57F2-28C5-44F9-8416-B08760DFE7E9}. ต้นฉบับ
ไม่มีความคิดเห็น:
แสดงความคิดเห็น