Process status:
http://technet.microsoft.com/en-us/library/ms179881.aspx
Get open connections by DB name, connections, login name:
SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
Information about current users, sessions, and processes (1)
Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.
USE master;
GO
EXEC sp_who 'active';
GO
Information about current users, sessions, and processes (2)
SELECT
R.SESSION_ID,
R.REQUEST_ID AS SESSION_REQUEST_ID,
R.STATUS,
S.HOST_NAME,
C.CLIENT_NET_ADDRESS,
CASE WHEN S.LOGIN_NAME = S.ORIGINAL_LOGIN_NAME THEN S.LOGIN_NAME ELSE S.LOGIN_NAME + ' (' + S.ORIGINAL_LOGIN_NAME + ')' END AS LOGIN_NAME,
S.PROGRAM_NAME,
DB_NAME(R.DATABASE_ID) AS DATABASE_NAME,
R.COMMAND,
ST.TEXT AS QUERY_TEXT,
QP.QUERY_PLAN AS XML_QUERY_PLAN,
R.WAIT_TYPE AS CURRENT_WAIT_TYPE,
R.LAST_WAIT_TYPE,
R.BLOCKING_SESSION_ID,
R.ROW_COUNT,
R.GRANTED_QUERY_MEMORY,
R.OPEN_TRANSACTION_COUNT,
R.USER_ID,
R.PERCENT_COMPLETE,
CASE R.TRANSACTION_ISOLATION_LEVEL
WHEN 0 THEN 'UNSPECIFIED'
WHEN 1 THEN 'READUNCOMITTED'
WHEN 2 THEN 'READCOMMITTED'
WHEN 3 THEN 'REPEATABLE'
WHEN 4 THEN 'SERIALIZABLE'
WHEN 5 THEN 'SNAPSHOT'
ELSE CAST(R.TRANSACTION_ISOLATION_LEVEL AS VARCHAR(32))
END AS TRANSACTION_ISOLATION_LEVEL_NAME
FROM SYS.DM_EXEC_REQUESTS R LEFT OUTER JOIN SYS.DM_EXEC_SESSIONS S ON S.SESSION_ID = R.SESSION_ID
LEFT OUTER JOIN SYS.DM_EXEC_CONNECTIONS C ON C.CONNECTION_ID = R.CONNECTION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) ST
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(R.PLAN_HANDLE) QP
WHERE R.STATUS NOT IN ('BACKGROUND','SLEEPING')
Details on all of the processes running in SQL Server
sys.dm_exec_requests DMV provides details on all of the processes running in SQL Server.
With the WHERE condition listed below, only blocked processes will be returned.
USE Master
GO
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO
How much task is completed in percentage?
SELECT A.SESSION_ID, DB_NAME(A.DATABASE_ID) AS DATABASE_NAME, A.START_TIME,A.COMMAND, A.CPU_TIME, A.PERCENT_COMPLETE, A.ESTIMATED_COMPLETION_TIME, B.TEXT
FROM SYS.DM_EXEC_REQUESTS A CROSS APPLY SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE)AS B
WHERE SESSION_ID > 50
Information about the tasks that are waiting on resources
The sys.dm_os_waiting_tasks DMV returns information about the tasks that are waiting on resources.
To view the data, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.
USE Master
GO
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0
GO
USE [master]
GO
SELECT w.session_id ,w.wait_duration_ms ,w.wait_type ,w.blocking_session_id ,w.resource_description
,s.program_name ,t.text ,t.dbid ,s.cpu_time ,s.memory_usage
FROM sys.dm_os_waiting_tasks w
INNER JOIN sys.dm_exec_sessions s
ON w.session_id = s.session_id
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
WHERE s.is_user_process = 1
GO
Displays the last statement sent from a client to an instance of Microsoft SQL Server
DBCC INPUTBUFFER(SPID)
Terminates a user process that is based on the session ID (or unit of work)
USE master;
GO
KILL spid
GO
Get information about SQL-Agent Job
DECLARE @JobName varchar(max)
SELECT @JobName = [name]
FROM msdb.dbo.sysjobs WHERE job_id = cast(0x788877F34636EB4786687E7C0A1F050A AS uniqueidentifier)
EXECUTE msdb..sp_help_job @job_name = @JobName
EXECUTE msdb..sp_help_jobstep @job_name = @JobName
Get information about Log file space
SELECT instance_name AS DatabaseName, [Data File(s) Size (KB)], [LOG File(s) Size (KB)], [Log File(s) Used Size (KB)], [Percent Log Used]
FROM
(
SELECT * FROM sys.dm_os_performance_counters WHERE counter_name IN (
'Data File(s) Size (KB)', 'Log File(s) Size (KB)', 'Log File(s) Used Size (KB)', 'Percent Log Used'
)
AND instance_name != '_Total'
) AS Src
PIVOT
(
MAX(cntr_value) FOR counter_name IN (
[Data File(s) Size (KB)], [LOG File(s) Size (KB)], [Log File(s) Used Size (KB)], [Percent Log Used]
)
) AS pvt
Get information about log file usage
DBCC SQLPERF (LOGSPACE)
go
State of the index in the tables
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc