Solved

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

Posted on 2008-06-11
9
631 Views
Last Modified: 2010-08-05
I have a client running a program using SQL2005. They are getting sporadic error msgs that the software vendor is actively working on. In the meantime they are getting these errors which cause users to be kicked out of the program. Problem is, they cannot log back into the program because the database still thinks they are in. The only way the client can get these users back in is to restart SQL or reboot the server. This is a major inconvienience for the other 15 or so users who are working in the program. Is there a utility which allows him to see active connections to the database and kill them??
0
Comment
Question by:wdabbs
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 3

Expert Comment

by:tsorensen55
Comment Utility
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.
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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
0
 

Author Comment

by:wdabbs
Comment Utility
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?
0
 

Author Comment

by:wdabbs
Comment Utility
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??
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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

Open in new window

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
You can also use the SQL Server Management Studio...

In object explorer, go down to management, then activity monitor, and the Activity Monitor screen will display. on the right hand side, you can right click on a process and "kill Process". You can see who and what it is doing, so is a bit more interactive. And yes, it is process by process... You can apply filters to help with the "noise"
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
Management Studio...yuck.  
;)
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Techo :D
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
Geez...you said you wanted to pass the connections to a kill statement...which is what my solution did.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now