SQL: Watching the System
Date: 2014-01-22 09:37:23
Here are some scripts to help peer inside SQL. I use these snippets everyday to help watch what is happening in system.
Check the General Processes that are running
This query helps identify blocking queries, long running queries, it will show backups and check database commands, etc...
What is that process?
SELECT percent_complete, (estimated_completion_time/1000)/60 Estimated_compMins ,
(total_elapsed_time/1000)/60 Total_Elap_Mins ,DB_NAME(Database_id) DBName, dm.session_id, dm.command, blocking_session_id, wait_type, *
FROM sys.dm_exec_requests dm
where dm.command not in ('BRKR EVENT HNDLR', 'BRKR TASK', 'DB MIRROR', 'FSAGENT TASK', 'TASK MANAGER', 'SIGNAL HANDLER', 'TRACE QUEUE TASK', 'RESOURCE MONITOR', 'LOG WRITER', 'LOCK MONITOR', 'LAZY WRITER', 'CHECKPOINT', 'XE DISPATCHER', 'XE TIMER', 'HADR_AR_MGR_NOTIFICATION_WORKER', 'SYSTEM_HEALTH_MONITOR', 'UNKNOWN TOKEN', 'RECOVERY WRITER')
order by dm.command asc;
This query can help identify what process is running. The SPID goes between the parentheses. The Event Info column will contain some of the SQL query running or the stored procedure name.
Who is it?
This query can help identify who is running the process.
What is my Log File size?
This query shows the Percentage Used for the Transaction Logs.
Thanks to MSDN, Technet, and Pinal Dave at the SQL Authority! Seriously, that is a great site: http://blog.sqlauthority.com