Script สำหรับตรวจสอบว่ามีใครใช้งาน Server อยู่บ้าง เอาไว้ Check กรณีที่ะทำอะไรกับตัว Database Server ครับ
SELECT Cast(SP.[spid] AS INTEGER) AS [Connection ID],
Cast(Rtrim(SP.[loginame]) AS NVARCHAR(64)) COLLATE thai_cs_as AS [User ID],
Cast(Rtrim(SP.[hostname]) AS NVARCHAR(64)) COLLATE thai_cs_as AS [Host Name],
Cast(CASE
WHEN SP.[spid] = @@SPID THEN 1
ELSE 0
END AS TINYINT)
AS [My Session],
CONVERT(VARCHAR(8),SP.[login_time],108) AS [Login Time],
CONVERT(DATETIME, CONVERT(CHAR(10), SP.[login_time], 120) + ' 00:00:00:000', 121) AS [Login Date],
Cast(SD.[name] AS NVARCHAR(128)) COLLATE thai_cs_as AS [Database Name],
Cast(Rtrim(SP.[program_name]) AS NVARCHAR(64)) COLLATE thai_cs_as AS [Application Name],
CASE
WHEN SP.[nt_domain] <> '' THEN 1
ELSE 0
END
AS [Login Type],
SP.[cpu] AS [CPU Time (ms)],
CASE
WHEN SP.[memusage] < 0 THEN 0
ELSE SP.[memusage] * 8
END
AS [Memory Usage (KB)],
SP.[physical_io] AS [Physical I_O],
Cast(CASE
WHEN SP.[blocked] <> 0 THEN 1
ELSE 0
END AS TINYINT)
AS [Blocked],
Cast(CASE
WHEN SP.[blocked] <> 0 THEN SP.[waittime]
ELSE 0
END AS INTEGER)
AS [Wait Time (ms)],
Cast(SP.[blocked] AS INTEGER) AS [Blocking Connection ID],
Cast(Isnull(Rtrim(SPB.[loginame]), '') AS NVARCHAR(64)) COLLATE thai_cs_as AS [Blocking User ID],
Cast(Isnull(Rtrim(SPB.[hostname]), '') AS NVARCHAR(64)) COLLATE thai_cs_as AS [Blocking Host Name],
Cast('' AS NVARCHAR(64)) COLLATE thai_cs_as AS [Blocking Object],
CASE
WHEN SP.[cmd] = 'AWAITING COMMAND' THEN Cast(
Datediff(second, SP.[last_batch], Getdate()) AS BIGINT) * 1000
ELSE 0
END
AS [Idle Time]
FROM [master].[dbo].[sysprocesses] AS SP
JOIN [master].[dbo].[sysdatabases] AS SD
ON ( SP.[dbid] = SD.[dbid] )
LEFT OUTER JOIN [master].[dbo].[sysprocesses] AS SPB
ON ( SP.[blocked] = SPB.[spid] )
WHERE SP.[ecid] = 0
