A couple of very useful and heavy used T-SQL scripts... Finally, I have decided to post both in order to find them every time I need with out having to re-author:
1. Kill all existing connections to a certain database:
DECLARE @DataBaseName varchar(255) = '<YOUR_DATABASE_NAME>'
DECLARE @spid INT, @cnt INT, @sql VARCHAR(255)SELECT @spid = MIN(spid), @cnt = COUNT(*) FROM master..sysprocesses WHERE dbid = DB_ID(@DataBaseName) AND spid != @@SPID
PRINT 'Cleaning up process...'PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'
WHILE @spid IS NOT NULL BEGIN PRINT 'About to KILL '+RTRIM(@spid) SET @sql = 'KILL '+RTRIM(@spid) EXEC(@sql) SELECT @spid = MIN(spid), @cnt = COUNT(*) FROM master..sysprocesses WHERE dbid = DB_ID(@DataBaseName) AND spid != @@SPID PRINT RTRIM(@cnt)+' processes remain.' END
2. The "What's going on" query: Which are the queries are currently executed?, How much resources are using?. Very useful to understand where are the server resources spent.
SELECT master.dbo.fn_varbintohexstr(sql_handle) + '|' + master.dbo.fn_varbintohexstr(plan_handle) + convert(varchar(30),statement_start_offset) + '|' + convert(varchar(30),statement_end_offset) as uniqueid, creation_time, last_execution_time as last_run, execution_count as execs, total_logical_reads as logical_reads, total_elapsed_time as elapsed, total_physical_reads as phys_reads, total_worker_time as CPU, total_rows as rows, total_clr_time as clr, total_logical_writes as log_writes, last_worker_time, min_worker_time, max_worker_time, last_physical_reads, min_physical_reads, max_physical_reads, last_logical_reads, min_logical_reads, max_logical_reads, last_logical_writes, min_logical_writes, max_logical_writes, last_clr_time, min_clr_time, max_clr_time, last_elapsed_time, min_elapsed_time, max_elapsed_time, last_rows, min_rows, max_rows, current_timestamp, master.dbo.fn_varbintohexstr(sql_handle) as sql_handle, master.dbo.fn_varbintohexstr(plan_handle) as plan_handle, statement_start_offset,statement_end_offset,plan_generation_num, replace(replace(SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1),char(10),''),char(13),'') as statement_textFROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qtORDER BY 5 desc
Hope this helps!
Here's what I get when I run the second query:
Msg 207, Level 16, State 1, Line 10
Invalid column name 'total_rows'.
Msg 207, Level 16, State 1, Line 31
Invalid column name 'last_rows'.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'min_rows'.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'max_rows'.
Mattie, the problem is related to your version of SQL Server; Those columns are only available form SQL Server 2008 R2 (msdn.microsoft.com/.../ms189741(v=sql.105).aspx)