Skip to content
No results
  • About Me (Resume)
  • Meetup Note/Share
  • เงินทอง งอกเงย
naiwaen@DebuggingSoft วงแหวนเว็บ
  • About Me (Resume)
  • Meetup Note/Share
  • เงินทอง งอกเงย
naiwaen@DebuggingSoft วงแหวนเว็บ
Microsoft SQL Server

[SQL SERVER] Script สำหรับตรวจสอบว่ามีใครใช้งาน Server อยู่บ้าง

  • AdminpingAdminping
  • July 22, 2014
  • MS SQL Server

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

Share this:

  • Tweet
  • Click to email a link to a friend (Opens in new window) Email
  • Click to print (Opens in new window) Print
  • Click to share on Telegram (Opens in new window) Telegram
  • Click to share on WhatsApp (Opens in new window) WhatsApp

Like this:

Like Loading...

Related


Discover more from naiwaen@DebuggingSoft

Subscribe to get the latest posts sent to your email.

Tags
# check login# user# session# MS SQL Server# SQL Server

About me

Adminping

Adminping

My name is Chatri Ngambenchawong and I am a Software Developer, Blogger, Geek and FoodMania ^__^

View Full Profile →

Donate (ร่วมสนับสนุน)

Facebook Page

Facebook Page

Categories

Archives

Pages

  • About Me (Resume)
  • Meetup Note/Share
  • นโยบายการใช้คุกกี้ (Cookies Policy)
  • นโยบายความเป็นส่วนตัวของข้อมูล (Privacy Policy)
  • เงินทอง งอกเงย

Recent Posts

  • แก้ปัญหา GitLab อาการผีหลอกก เช่น push branch หาย!! / Branch protection rule ไม่ทำงาน
  • Man Hin By Chef Man-ข้าวมันไก่ฮ่องกง 99 บาท
  • [JAVA] Json Path
  • รื้อเสาอากาศทีวีอายุ 30 ปี !!!
  • แยกร่างมาฟัง Global Azure 2025 Thailand + AzureSecEve

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Related Posts

DB2 Data Type DECFLOAT แล้ว SQL Server ใช้อะไร ?

  • November 3, 2023

[MSSQL] สรุปงาน SQL Server Community Thailand Meeting #16

  • July 20, 2022

[MSSQL] สรุปงาน SQL Server Community Thailand Meeting #14

  • November 20, 2021

ถ้าสนใจ Blog สรุปต่างๆ ลองมา Subscribe กันได้ครับ เดี๋ยวจะมีเมล์จาก donotreply@wordpress.com มาให้กด Confirm อีกทีครับ

  • About Me (Resume)
  • นโยบายการใช้คุกกี้ (Cookies Policy)
Copyright © 2025 - WordPress Theme by CreativeThemes
Manage Cookie Consent
To provide the best experiences, we use technologies like cookies to store and/or access device information. Consenting to these technologies will allow us to process data such as browsing behavior or unique IDs on this site. Not consenting or withdrawing consent, may adversely affect certain features and functions.
Functional Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
Manage options Manage services Manage {vendor_count} vendors Read more about these purposes
View preferences
{title} {title} {title}
%d