SQL: Watching the System
Author: jason
Date: 2014-01-22 09:37:23
Category: Technical

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...




Code

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;


What is that process?
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.




Code

DBCC INPUTBUFFER(54);


Who is it?
This query can help identify who is running the process.




Code

exec sp_who2 54;


What is my Log File size?
This query shows the Percentage Used for the Transaction Logs.




Code

DBCC SQLPERF(LOGSPACE);


Thanks to MSDN, Technet, and Pinal Dave at the SQL Authority! Seriously, that is a great site: http://blog.sqlauthority.com




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009