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
Discover more from naiwaen@DebuggingSoft
Subscribe to get the latest posts sent to your email.