How can I kill bad sessions without rebooting or stopping SQL?

http://support.microsoft.com/kb/137983

If you suspect orphaned processes exist on your SQL Server, the following are steps you can take to troubleshoot the problem:
1.      Identify the orphaned processes using sp_who, which may tell you which applications were associated with these processes through the host names.
2.      After you identify these orphaned processes, you may choose to either ignore them if they are not holding any locks or using many connections, or kill them using the SQL Server KILL command.
3.      Check with the application users for any improper procedures of closing applications, such as warm or cold restart of workstations without exiting the applications first. Check whether there is any history of the workstation becoming unstable, such as a general protection fault, and so forth. Correct those improper procedures or stability problems if they do exist.
4.      Check whether the IPC session is still active on the Windows NT Server computer where SQL Server is running. Depending on the IPCs you are using, the commands are different. For example, if you are using named pipes, the command is "NET SESSION" or "NET FILES"; if it is a TCP/IP sockets connection, you can use "NETSTAT" to display active TCP sessions; in case of IPX/SPX, you may have to use the Performance Monitor to monitor the "Connections Open" for "NWLink SPX."
5.      If the IPC sessions are still active on the Windows NT Server computer, it is perfectly normal for SQL Server to keep those connection processes. When Windows NT clears up the IPC sessions, SQL Server will be notified and clear up the connection processes accordingly. You may be able to adjust certain Windows NT network parameters to shorten the time period that Windows NT has to wait before clearing up the dead sessions.

select * From sys.dm_exec_connections
where session_id > 50

find the processes you want to kill and pass them to the KILL statement

KILL 55

once I identify the process ID by running select * From sys.dm_exec_connections
where session_id > 50 against the database.. How do I then "pass them to the KILL statement?

Also.. When I tested this, by logging into the database myself, I see that 12 session were created when I logged in. This is not what I had expected. Do I need to kill all 12 sessions? And does this undo any work done??

If they are inside a transaction then it will be rolled back.

Also, you only create one connection (only one is possible) when you log into the server.

YOu can use this procedure to do it also...just when you load the temp table, make sure you set criteria on the processes you want to kill...

create procedure usp_KillDBProcesses(
	@dbname as varchar(20)
) 
as 
BEGIN
 
DECLARE @strSQL nvarchar(255)
DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
 
CREATE table #tmpProcesses
(
	spid int,
	eid int,
	status varchar(30),
	loginname varchar(50),
	hostname varchar(50),
	blk int,
	dbname varchar(50),
	cmd varchar(30)
)
 
INSERT INTO #tmpUsers EXEC SP_WHO
--check to see who is currently in the databases
 
DECLARE LoginCursor CURSOR READ_ONLY FOR 
 
SELECT spid, dbname FROM #tmpProcesses 
WHERE dbname = @dbname
--load cursor for values from table (processes)
 
OPEN LoginCursor
 
FETCH NEXT FROM LoginCursor INTO @spid
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		--kill processes
		SET @strSQL = 'KILL ' + @spid
		EXEC (@strSQL)
	END
	FETCH NEXT FROM LoginCursor INTO @spid
END
 
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpProcesses
END
GO

Select allOpen in new window

Management Studio...yuck.  
;)

Techo :D

Geez...you said you wanted to pass the connections to a kill statement...which is what my solution did.