SQL Server DB管理筆記

查詢當前LOCK狀態
select * from master..sysprocesses where blocked <> 0

查詢執行中的語法(可以查到Lock源由及Heavy SQL...等)

SELECT r.scheduler_id as 排程器識別碼,
e.nt_username as 'USER',
r.status as 要求的狀態,
r.session_id as SPID,
r.blocking_session_id as BlkBy,
substring(
ltrim(q.text),
r.statement_start_offset/2+1,
(CASE
WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2)
AS [正在執行的T-SQL 命令],
r.cpu_time as [CPU Time(ms)],
r.start_time as [開始時間],
r.total_elapsed_time as [執行總時間],
r.reads as [讀取數],
r.writes as [寫入數],
r.logical_reads as [邏輯讀取數],
-- q.text, /* 完整的T-SQL 指令碼*/
d.name as [資料庫名稱]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
LEFT JOIN sys.sysprocesses e ON (e.spid = r.session_id)
WHERE r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time desc

取消某執行語法
kill (PID)


2012-07-05 20:21:42

I said something before.....
Textbox限制輸入數字 2016-11-14 21:17
C# 字串分割 2016-07-28 20:25
LINQ筆記 2016-07-27 03:33
SQL Server的記錄檔無限成長 2015-11-02 05:47
安裝MySQL ODBC 5.2錯誤 – Error 1918 2014-02-03 19:27
在Reporting Service報表上顯示QRCode 2013-04-01 02:59
還原MSSQL遇到「備份組包含現有的XXX資料庫以外的資料庫備份」的錯誤 2012-05-15 12:35
Informix計算兩時間的差距 2010-08-12 06:01
ReportViewer顯示LocalReport及ServerReport 2010-05-24 04:13
SQL Server取得當月第一天及最後一天 2010-01-19 00:00
簡單的對DataTable做Distinct 2009-09-20 22:18
使用 svcutil 編譯 WCF 錯誤解法 2009-07-09 07:59
PHP加載MSSQL函式庫 2008-10-20 08:54
資料庫抓取固定筆數資料 2008-09-18 22:27
PHP與ASP共存 2008-07-05 10:03