i just ran the sp_who sp and heres the results i got...now how can i know unwanted connections form this list..
10background sa0 NULLLAZY WRITER
20sleeping sa0 NULLLOG WRITER
30background sa0 masterSIGNAL HANDLER
40background sa0 NULLLOCK MONITOR
50background sa0 masterTASK MANAGER
60background sa0 masterTASK MANAGER
70sleeping sa0 NULLCHECKPOINT SLEEP
80background sa0 masterTASK MANAGER
90background sa0 masterTASK MANAGER
100background sa0 masterTASK MANAGER
110background sa0 masterTASK MANAGER
120background sa0 masterTASK MANAGER
130background sa0 masterTASK MANAGER
510sleeping NT AUTHORITY\SYSTEM 0 ReportServerAWAITING COMMAND
520runnable CENTCOM\Dinakar 0 BBMiniSELECT
550sleeping CENTCOM\ASPNET 0 BBMiniAWAITING COMMAND
560sleeping CENTCOM\ASPNET 0 BBMiniAWAITING COMMAND
thanksDon't know what you mean by "unwanted connections", but this script will kill all users:
CREATE PROCEDURE usp_KillUsers @.dbname varchar(50) as
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
PRINT 'Killing Users'
PRINT '------'
CREATE table #tmpUsers(
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
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @.dbname
DECLARE @.spid varchar(10)
DECLARE @.dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @.spid
SET @.strSQL = 'KILL ' + @.spid
EXEC (@.strSQL)
END
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
go|||no i dont want to kill all the connections...from the list above i'd like to know how many connections are genuine ones how many are system connections and how many are user created (by asp.net etc) ...is it ok to kill the connections with loginname ="sa".. since there are too many connections with that name...
thanks|||there seem to be a lot of connections to the master db...and am not using the db at all...so can i kill all those connections...how did they get activated in the first place...or is this normal..anyone has any idea...
thanks
No comments:
Post a Comment