Killing Active SQL Connections

So there are many times we are waiting to take a database offline or delete a database. I finally decided there had to be a better way than to wait … specially when this is my development environment. So here are two queries we can use.

First, find the active connection for all the databases:

  db_name(dbid) as [DB Name],
  count(dbid) as [Active Connections],
  loginame as [Login Name]
from sys.sysprocesses
where dbid > 0
group by dbid, loginame;

Then from that, we can determine which database needs the connections terminated. Or you can use this SQL directly if you already know which database you want to kill the active connections on:

set nocount on;
declare @databasename varchar(100);
declare @qry varchar(max);
set @qry = '';
set @databasename = '[Database name]';
if db_id(@databasename) < 4 begin print 'Hey, system database connections cannot be killed!!!'; return end select @qry=coalesce(@qry,',' )+'kill '+convert(varchar, spid)+ '; ' from master..sysprocesses where dbid=db_id(@databasename); if len(@qry) > 0
  print @qry;

Hope you all find this as useful as I have. Cheers!

Finding duplicate values in a SQL table

Even though this is simple, I keep needing this every once in a while. This is for SQL Server.

To Find the list of fields for which there are duplicates (or multiple records), use:

SELECT [field1],[field2],[field3], count(*) as DUPE_COUNT INTO #DupeList FROM table_name GROUP BY [field1],[field2],[field3] HAVING count(*) > 1

Now to see the dupes:


Now, I prepare my delete … and verify I see only what I want to clean up:

SELECT * FROM table_name
WHERE [field1] in (SELECT [field1] FROM #DupeList)
ORDER BY [field1],[field2][field3]

Here we delete the dupes … be sure the query matches the select query to not delete unexpected data/values: (I commented this deliberated to avoid accidents)

–Clean up duplicate
–DELETE FROM table_name WHERE [field1] in (SELECT [field1] FROM #DupeList)

And we always clean up temp tables:

–Clean up the temp table

For more details: