因為沒辦法進 SQL Server 去看活動監視器，所以想說自己做一個完全一模一樣的活動監視器吧 !! 不過我卻發現這個功能還是會受 SQL Server 的權限的影響，如果是因為SQL Server Lisence 的問題而不進去SQL Server ，但又想看到目前在這一台 SQL Server 上的活動，就用下面的指令吧(記得 Connection 的 User 最好能是 Admin)。
QryStr = "select a.spid as 處理序識別碼,case when b.is_user_process = 1 then 0 else 1 end as 系統處理序,"
QryStr = QryStr & "a.loginame as 使用者,isnull(db_name(a.dbid),N'') as 資料庫,a.status as 狀態,a.open_tran as 開啟交易,"
QryStr = QryStr & "a.cmd as 命令,a.program_name as 應用程式,a.waittime as 等候時間,case when a.waittype = 0 then N'' else a.lastwaittype end as 等候類型,"
QryStr = QryStr & "case when a.waittype = 0 then N'' else a.waitresource end as 資源,a.cpu as CPU,"
QryStr = QryStr & "a.physical_io as 實體IO,a.memusage as 記憶體使用量,a.login_time as 登入時間,"
QryStr = QryStr & "a.last_batch as 上個批次,a.hostname as 主機,a.net_library as 網路程式庫,a.net_address as 網路位置,"
QryStr = QryStr & "a.blocked as 被封鎖,0 as 封鎖,a.ecid as 執行內容"
QryStr = QryStr & " from master.dbo.sysprocesses a, master.sys.dm_exec_sessions b"
QryStr = QryStr & " Where a.spid = b.session_id"
QryStr = QryStr & " order by a.spid"
若想按不同的欄位去排序，只要加個小 function 就可以改變 order by 啦~
QryStr = "select a.spid as 處理序識別碼,case when b.is_user_process = 1 then 0 else 1 end as 系統處理序,"
QryStr = QryStr & "a.loginame as 使用者,isnull(db_name(a.dbid),N'') as 資料庫,a.status as 狀態,a.open_tran as 開啟交易,"
QryStr = QryStr & "a.cmd as 命令,a.program_name as 應用程式,a.waittime as 等候時間,case when a.waittype = 0 then N'' else a.lastwaittype end as 等候類型,"
QryStr = QryStr & "case when a.waittype = 0 then N'' else a.waitresource end as 資源,a.cpu as CPU,"
QryStr = QryStr & "a.physical_io as 實體IO,a.memusage as 記憶體使用量,a.login_time as 登入時間,"
QryStr = QryStr & "a.last_batch as 上個批次,a.hostname as 主機,a.net_library as 網路程式庫,a.net_address as 網路位置,"
QryStr = QryStr & "a.blocked as 被封鎖,0 as 封鎖,a.ecid as 執行內容"
QryStr = QryStr & " from master.dbo.sysprocesses a, master.sys.dm_exec_sessions b"
QryStr = QryStr & " Where a.spid = b.session_id"
QryStr = QryStr & " order by a.spid"
若想按不同的欄位去排序，只要加個小 function 就可以改變 order by 啦~
全站熱搜
留言列表
