Problem current activity connection sql server | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem current activity connection sql server

Hello,<br /><br />I need your help me, please. I working with Sql Server 2000, IIS 5.0 end web page (Asp) besides I working with Visual basic 6 for Dll the Connection for DataBase. <br />I have one web site in my enterprise, but i have one problem with connections per users in SqlServer 2000 (Current Activity-Proccess Info).<br />EveryOne the users have 1 or 2 or 3 or n open connection when enter the site web, my problem is close all this connection for my dll in the site web.<br /><br />My question are:<br />1-How I can close the all connection in SqlServer for user?<br />2-Where I can find information on (Current Activity-Proccess Info) the Sql Server 2000.<br /><br />Thanks Friend<br /><br />You can read in Spanish here.<br /><br />hola, <br /><br />Necesito ayuda de un Dba, estoy trabajando con Sql server 2000 y con IIS 5 y Con paginas Asp. <br />Tengo un sitio web donde se realizan actividades de fondos de pensión y tengo una Dll que se conecta a la base de datos, pero el problema es que no cierra las conexiones, por lo tal aparece el sql server con muchas conexiones para un usuario que entro al sitio web. <br /><br />Quien sabe como desconectar las conexiones a Sql server? <br />Cual es la mejor tecnica para crear un dll para el uso de conexiones a base de datos? <br /><br />Nota: yo veo las conexiones del sql server en current activity -&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />rocess info <br />&lt;/druz&gt; <br /><br />
May this help you: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1883&SearchTerms=connections Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
1-How I can close the all connection in SqlServer for user? Right click on the connection you want to close, under Process Info and click on Kill Process. 2-Where I can find information on (Current Activity-Proccess Info) the Sql Server 2000. Check the topic ‘Monitoring with SQL Server Enterprise Manager’ under Books on line.

Using Query Analyzer would be easy in this regard: Run SP_WHO and take a note of all SPIDs for that user and use KILL statement to get rid of them. Again using QA run sp_WHO and SP_WHO2 for details for current activity. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

tHIS WILL CLOSE ALL CONNECTION OF USER THAT YOU LIKE.
But pay attention to Last_batch column .
What i am doing i am executing this stored precedure nightly
but in where clause insted of loginame=” i put this:
last_batch < dateADD(dd,-1,getdate())
and SPID>50 –1-50 spid is system
here is sp to kill connections of specific user:
CREATE PROCEDURE dbo.mnt_killOldProcesses AS DECLARE @SPID INT DECLARE CONNECTIONS CURSOR FOR
select sPid
from sysprocesses
where
loginame=’LOGINNAME’
OPEN CONNECTIONS FETCH NEXT FROM CONNECTIONS
INTO @SPID WHILE @@FETCH_STATUS=0
BEGIN EXEC (‘KILL ‘ + @SPID) FETCH NEXT FROM CONNECTIONS
INTO @SPID
END
CLOSE CONNECTIONS
DEALLOCATE CONNECTIONS
GO

]]>