I understand it is very simple script but we often forget simple scripts. Hence I thought to write this simple script and also we can use GUI to find the same. Both the ways I have described below. Using GUI it is very easy and simple and additionally you will get some more information such as SQL edition, collation, number of databases including system.
1 2 3 4 5 6 7 8 9 10 |
-- ============================ -- -- Owner: SOYELUDDIN BISWAS -- -- Created Date: 29/09/2019 -- -- Email: st.biswas99@gmail.com -- -- ============================ -- use master go select SERVERPROPERTY('ServerName') as Server ,sqlserver_start_time as [SQL instance last restarted date and time] from sys.dm_os_sys_info |
Step 1. Connect to SQL instance using SSMS.
Step 2. Right click on SQL instance –> Select Reports –> Standard Reports –> then click on Server Dashboard
DMV ‘sys.dm_exec_requests’ provides details on all of the processes running in SQL Server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT qs.Session_ID, Blocking_Session_ID, qs.Status, Wait_Type, Wait_Time, Wait_Resource, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate() SnapshotDateTime, --Open_Transaction_Count, ss.PROGRAM_NAME, ss.HOST_NAME, ss.Login_Name FROM sys.dm_exec_requests AS qs INNER JOIN sys.dm_exec_sessions ss ON qs.session_id = ss.session_id CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE Wait_Time > 0 ORDER BY Wait_Time DESC |
“Please let us know if there…