Knowing how many connections are open?

How can I know how many connections are open at a given point of time while I am testing an ASP.Net application? The application uses SQL Server 2000 as its database.

I dont know whether this will do it, but go into query analyzer and type "exec sp_who2". This will show you what connections are open for the entire server, how much resources they are using and when a command was last run. Not completely worked out the information it throws back, but its a start. However I dont know how (if) .Net pools connections so I dont know if this is entirely accurate... anyone?

Killing timed out connections

I am having a problem with an application that does not kill timed out connections. This is normally not an issue, but when something causes the timed out connections to build up, it stops the frontend from working correctly. The frontend developers are trying to figure out how to change their code to check for and drop timed out connections at the application. Until then, I need a way to check for timed out connections at the database and drop them there via a job that will run every 10 minutes or so. I have to make sure that only timed out connections are dropped and not active ones. Any suggestions?

-SQLBill

Sorry, SQL Server simply does not know what that is. A time out occurs in your connection object - ODBC, OLEDB... SQL Server has no concept of a timeout, so it would not know if an application on the other side has simply given up waiting for a response. What your developers need to do within the code is to issue a reset connection when they go to grab a connection and use it. That ensures that any resources are released. They need to issue a reset anytime they issue a request, grab a connection from a pool, or return a connection to a pool.sql

Killing active connections before detaching a database

How can I kill all active connections to a database before detaching it?
I am calling sp_detach_db from an application but I keep getting the error
"Cannot detach because there are one or more active connections."
Thanks,
RonKill spid
Madhivanan|||2 ways
1
--loop through the sysprocesses
DECLARE @.sysDbName SYSNAME
SELECT @.sysDbName = 'northwind'
SELECT IDENTITY(int, 1,1)AS ID,spid
INTO #LoopProcess
FROM master..sysprocesses
WHERE dbid = DB_ID(@.sysDbName)
DECLARE @.SPID SMALLINT
DECLARE @.SQL VARCHAR(255)
DECLARE @.MaxID INT, @.LoopID INT
SELECT @.LoopID =1,@.MaxID = MAX(ID) FROM #LoopProcess
WHILE @.LoopID <= @.MaxID
BEGIN
SELECT @.SPID = spid FROM #LoopProcess WHERE ID = @.LoopID
SELECT @.SQL = 'KILL ' + CONVERT(VARCHAR, @.SPID)
EXEC( @.SQL )
SELECT @.LoopID = @.LoopID +1
END
DROP TABLE #LoopProcess
2
--alter the DB by making it single user (all transaction will be rolled
back)
ALTER DATABASE northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do your restore here
-- Make the DB multi user again
ALTER DATABASE northwind SET MULTI_USER
http://sqlservercode.blogspot.com/|||That won't stop them from "crawling" back. ;)
Use:
use master
alter database <database name>
set single_user
with rollback immediate
...to kill all users immediately, or:
alter database <database name>
set single_user
with rollback after <number> seconds
...to give them time to finish their work.
ML
http://milambda.blogspot.com/|||That's what I call the "nuclear option". ;-)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:15E4ACA2-392A-4986-B4A5-7E52EA9DDB33@.microsoft.com...
> That won't stop them from "crawling" back. ;)
> Use:
> use master
> alter database <database name>
> set single_user
> with rollback immediate
> ...to kill all users immediately, or:
> alter database <database name>
> set single_user
> with rollback after <number> seconds
> ...to give them time to finish their work.
>
> ML
> --
> http://milambda.blogspot.com/|||THANKS!
No problem, the databases that will be called by this procedure are only
used under controlled circustances.
Ron
"JT" <someone@.microsoft.com> wrote in message
news:OkmBFzUOGHA.3100@.TK2MSFTNGP11.phx.gbl...
> That's what I call the "nuclear option". ;-)
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:15E4ACA2-392A-4986-B4A5-7E52EA9DDB33@.microsoft.com...
>|||When you gotta nuke'em, you gotta nuke'em. :)
ML
http://milambda.blogspot.com/

kill user connections

can someone point me to a good site / article on how to go abt checking for
current user connections and kill these connections,
using Enterprise Mge & T-SQL.
try searching Microsoft, didn't find what I am looking for.
tksTry BooksOnLine. Specifically you might want to look at sp_who and KILL
commands.
Andrew J. Kelly SQL MVP
"pk" <pk@.> wrote in message news:e#XJeQtEEHA.3576@.tk2msftngp13.phx.gbl...
> can someone point me to a good site / article on how to go abt checking
for
> current user connections and kill these connections,
> using Enterprise Mge & T-SQL.
> try searching Microsoft, didn't find what I am looking for.
>
> tks
>|||Hi,
IN Query Analyzer
Execute
sp_who (To get all the connected process)
Kill <Process id> (to kill a process)
Enterprise manager
1. Expand the Management option
2. Expand - CUrrent Activity
3.Click the process info - this will show all process connected
4. Doule click the process to get the statement running (Use DBCC
INPUTBUFFER(process id) in Query Anlyzer
5. Right click and kill (if you need to kill the process)
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:O42p##tEEHA.696@.TK2MSFTNGP12.phx.gbl...
> Try BooksOnLine. Specifically you might want to look at sp_who and KILL
> commands.
> --
> Andrew J. Kelly SQL MVP
>
> "pk" <pk@.> wrote in message news:e#XJeQtEEHA.3576@.tk2msftngp13.phx.gbl...
> for
>|||tks.
i also found this, although it uses a script,
http://www.sqlservercentral.com/scr...ibutions/30.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eToleOuEEHA.2308@.tk2msftngp13.phx.gbl...
> Hi,
> IN Query Analyzer
> Execute
> sp_who (To get all the connected process)
> Kill <Process id> (to kill a process)
> Enterprise manager
> 1. Expand the Management option
> 2. Expand - CUrrent Activity
> 3.Click the process info - this will show all process connected
> 4. Doule click the process to get the statement running (Use DBCC
> INPUTBUFFER(process id) in Query Anlyzer
> 5. Right click and kill (if you need to kill the process)
> Thanks
> Hari
> MCDBA
>
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:O42p##tEEHA.696@.TK2MSFTNGP12.phx.gbl...
news:e#XJeQtEEHA.3576@.tk2msftngp13.phx.gbl...
checking
>|||Also Tibor Karaszi has a stored procedure that I always use.. It allows you
to kill all of the connections on a particular database... It can be found
under the procedures section of his web site www.dbmaint.com
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pk" <pk@.> wrote in message news:e%23XJeQtEEHA.3576@.tk2msftngp13.phx.gbl...
> can someone point me to a good site / article on how to go abt checking
for
> current user connections and kill these connections,
> using Enterprise Mge & T-SQL.
> try searching Microsoft, didn't find what I am looking for.
>
> tks
>

kill unnecessary connections

how to determine which connections are unwanted...
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

Kill SPs then sqlserveragent stop working

We had a lot user connections with 'sleeping' mode in sysprocesses. I killed them with the status='sleeping', I realized that i might also killed some connections are not supposed to be killed.
After that, I checked event viewer, I saw 'Unable to read local eventlog (reason: The data area passed to a system call is too small).
When I restarted the sqlserveragent service, I got'Could not start SQLserverAgent Service on local computer. The service did not return an error. This could be an internal Windows error or an internal service error. If this error persists, contact your system administrator. ' and also the following message appears in Application log
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).1) Reboot your server.
2) Don't kill processes you don't know for sure are safe. NEVER kill a
process with a SPID under 50. Those are system processes and need to be
there. Sleeping processes do not harm SQL and typically take very little
memory.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Shannon" <anonymous@.discussions.microsoft.com> wrote in message
news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> We had a lot user connections with 'sleeping' mode in sysprocesses. I
killed them with the status='sleeping', I realized that i might also killed
some connections are not supposed to be killed.
> After that, I checked event viewer, I saw 'Unable to read local eventlog
(reason: The data area passed to a system call is too small). '
> When I restarted the sqlserveragent service, I got'Could not start
SQLserverAgent Service on local computer. The service did not return an
error. This could be an internal Windows error or an internal service error.
If this error persists, contact your system administrator. ' and also the
following message appears in Application log '
> SQLServerAgent could not be started (reason: SQLServerAgent must be able
to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the
SysAdmin role). '
>|||If you want to kill off all your user connections, you might try a query
like this:
select 'kill '+convert(varchar(10),spid) from sysprocesses where dbid not in
(select dbid from sysdatabases where name in ('master','msdb') union all
select 0 as dbid) order by sysprocesses.spid
"Shannon" <anonymous@.discussions.microsoft.com> wrote in message
news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> We had a lot user connections with 'sleeping' mode in sysprocesses. I
killed them with the status='sleeping', I realized that i might also killed
some connections are not supposed to be killed.
> After that, I checked event viewer, I saw 'Unable to read local eventlog
(reason: The data area passed to a system call is too small). '
> When I restarted the sqlserveragent service, I got'Could not start
SQLserverAgent Service on local computer. The service did not return an
error. This could be an internal Windows error or an internal service error.
If this error persists, contact your system administrator. ' and also the
following message appears in Application log '
> SQLServerAgent could not be started (reason: SQLServerAgent must be able
to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the
SysAdmin role). '
>|||Sorry, I am not recommending you try that. I accidentally hit a
key-combination that posted that note while I was composing it and still
thinking about it.
I didn't test it and the thought process was incomplete. One of the
processes killed would be your own. Well... I take that back. If you were
in master at the time, your own process would not be listed and if you were
not in master, this query couldn't resolve sysprocesses - unless it was an
SP_ stored procedure in master. Still, adding an "and spid <> @.@.spid"
clause might be a good idea.
Of course, the select statement I listed is no threat by itself, you'd have
to execute the results.
"DHatheway" <dlhatheway@.mmm.com.nospam> wrote in message
news:c5k11b$pfh$1@.tuvok3.mmm.com...
> If you want to kill off all your user connections, you might try a query
> like this:
> select 'kill '+convert(varchar(10),spid) from sysprocesses where dbid not
in
> (select dbid from sysdatabases where name in ('master','msdb') union all
> select 0 as dbid) order by sysprocesses.spid
> "Shannon" <anonymous@.discussions.microsoft.com> wrote in message
> news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> > We had a lot user connections with 'sleeping' mode in sysprocesses. I
> killed them with the status='sleeping', I realized that i might also
killed
> some connections are not supposed to be killed.
> >
> > After that, I checked event viewer, I saw 'Unable to read local eventlog
> (reason: The data area passed to a system call is too small). '
> >
> > When I restarted the sqlserveragent service, I got'Could not start
> SQLserverAgent Service on local computer. The service did not return an
> error. This could be an internal Windows error or an internal service
error.
> If this error persists, contact your system administrator. ' and also the
> following message appears in Application log '
> >
> > SQLServerAgent could not be started (reason: SQLServerAgent must be able
> to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of
the
> SysAdmin role). '
> >
> >
>|||clever! <g>
"DHatheway" <dlhatheway@.mmm.com.nospam> wrote in message
news:c5k11b$pfh$1@.tuvok3.mmm.com...
> If you want to kill off all your user connections, you might try a query
> like this:
> select 'kill '+convert(varchar(10),spid) from sysprocesses where dbid not
in
> (select dbid from sysdatabases where name in ('master','msdb') union all
> select 0 as dbid) order by sysprocesses.spid
> "Shannon" <anonymous@.discussions.microsoft.com> wrote in message
> news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> > We had a lot user connections with 'sleeping' mode in sysprocesses. I
> killed them with the status='sleeping', I realized that i might also
killed
> some connections are not supposed to be killed.
> >
> > After that, I checked event viewer, I saw 'Unable to read local eventlog
> (reason: The data area passed to a system call is too small). '
> >
> > When I restarted the sqlserveragent service, I got'Could not start
> SQLserverAgent Service on local computer. The service did not return an
> error. This could be an internal Windows error or an internal service
error.
> If this error persists, contact your system administrator. ' and also the
> following message appears in Application log '
> >
> > SQLServerAgent could not be started (reason: SQLServerAgent must be able
> to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of
the
> SysAdmin role). '
> >
> >
>sql

Kill SPs then sqlserveragent stop working

We had a lot user connections with 'sleeping' mode in sysprocesses. I killed them with the status='sleeping', I realized that i might also killed some connections are not supposed to be killed.
After that, I checked event viewer, I saw 'Unable to read local eventlog (reason: The data area passed to a system call is too small). '
When I restarted the sqlserveragent service, I got'Could not start SQLserverAgent Service on local computer. The service did not return an error. This could be an internal Windows error or an internal service error. If this error persists, contact your sy
stem administrator. ' and also the following message appears in Application log '
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role). '
1) Reboot your server.
2) Don't kill processes you don't know for sure are safe. NEVER kill a
process with a SPID under 50. Those are system processes and need to be
there. Sleeping processes do not harm SQL and typically take very little
memory.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Shannon" <anonymous@.discussions.microsoft.com> wrote in message
news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> We had a lot user connections with 'sleeping' mode in sysprocesses. I
killed them with the status='sleeping', I realized that i might also killed
some connections are not supposed to be killed.
> After that, I checked event viewer, I saw 'Unable to read local eventlog
(reason: The data area passed to a system call is too small). '
> When I restarted the sqlserveragent service, I got'Could not start
SQLserverAgent Service on local computer. The service did not return an
error. This could be an internal Windows error or an internal service error.
If this error persists, contact your system administrator. ' and also the
following message appears in Application log '
> SQLServerAgent could not be started (reason: SQLServerAgent must be able
to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the
SysAdmin role). '
>
|||If you want to kill off all your user connections, you might try a query
like this:
select 'kill '+convert(varchar(10),spid) from sysprocesses where dbid not in
(select dbid from sysdatabases where name in ('master','msdb') union all
select 0 as dbid) order by sysprocesses.spid
"Shannon" <anonymous@.discussions.microsoft.com> wrote in message
news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> We had a lot user connections with 'sleeping' mode in sysprocesses. I
killed them with the status='sleeping', I realized that i might also killed
some connections are not supposed to be killed.
> After that, I checked event viewer, I saw 'Unable to read local eventlog
(reason: The data area passed to a system call is too small). '
> When I restarted the sqlserveragent service, I got'Could not start
SQLserverAgent Service on local computer. The service did not return an
error. This could be an internal Windows error or an internal service error.
If this error persists, contact your system administrator. ' and also the
following message appears in Application log '
> SQLServerAgent could not be started (reason: SQLServerAgent must be able
to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the
SysAdmin role). '
>
|||clever! <g>
"DHatheway" <dlhatheway@.mmm.com.nospam> wrote in message
news:c5k11b$pfh$1@.tuvok3.mmm.com...
> If you want to kill off all your user connections, you might try a query
> like this:
> select 'kill '+convert(varchar(10),spid) from sysprocesses where dbid not
in
> (select dbid from sysdatabases where name in ('master','msdb') union all
> select 0 as dbid) order by sysprocesses.spid
> "Shannon" <anonymous@.discussions.microsoft.com> wrote in message
> news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> killed them with the status='sleeping', I realized that i might also
killed
> some connections are not supposed to be killed.
> (reason: The data area passed to a system call is too small). '
> SQLserverAgent Service on local computer. The service did not return an
> error. This could be an internal Windows error or an internal service
error.
> If this error persists, contact your system administrator. ' and also the
> following message appears in Application log '
> to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of
the
> SysAdmin role). '
>

Kill SPs then sqlserveragent stop working

We had a lot user connections with 'sleeping' mode in sysprocesses. I killed
them with the status='sleeping', I realized that i might also killed some c
onnections are not supposed to be killed.
After that, I checked event viewer, I saw 'Unable to read local eventlog (re
ason: The data area passed to a system call is too small). '
When I restarted the sqlserveragent service, I got'Could not start SQLserver
Agent Service on local computer. The service did not return an error. This c
ould be an internal Windows error or an internal service error. If this erro
r persists, contact your sy
stem administrator. ' and also the following message appears in Application
log '
SQLServerAgent could not be started (reason: SQLServerAgent must be able to
connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the Sys
Admin role). '1) Reboot your server.
2) Don't kill processes you don't know for sure are safe. NEVER kill a
process with a SPID under 50. Those are system processes and need to be
there. Sleeping processes do not harm SQL and typically take very little
memory.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Shannon" <anonymous@.discussions.microsoft.com> wrote in message
news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> We had a lot user connections with 'sleeping' mode in sysprocesses. I
killed them with the status='sleeping', I realized that i might also killed
some connections are not supposed to be killed.
> After that, I checked event viewer, I saw 'Unable to read local eventlog
(reason: The data area passed to a system call is too small). '
> When I restarted the sqlserveragent service, I got'Could not start
SQLserverAgent Service on local computer. The service did not return an
error. This could be an internal Windows error or an internal service error.
If this error persists, contact your system administrator. ' and also the
following message appears in Application log '
> SQLServerAgent could not be started (reason: SQLServerAgent must be able
to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the
SysAdmin role). '
>|||If you want to kill off all your user connections, you might try a query
like this:
select 'kill '+convert(varchar(10),spid) from sysprocesses where dbid not in
(select dbid from sysdatabases where name in ('master','msdb') union all
select 0 as dbid) order by sysprocesses.spid
"Shannon" <anonymous@.discussions.microsoft.com> wrote in message
news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> We had a lot user connections with 'sleeping' mode in sysprocesses. I
killed them with the status='sleeping', I realized that i might also killed
some connections are not supposed to be killed.
> After that, I checked event viewer, I saw 'Unable to read local eventlog
(reason: The data area passed to a system call is too small). '
> When I restarted the sqlserveragent service, I got'Could not start
SQLserverAgent Service on local computer. The service did not return an
error. This could be an internal Windows error or an internal service error.
If this error persists, contact your system administrator. ' and also the
following message appears in Application log '
> SQLServerAgent could not be started (reason: SQLServerAgent must be able
to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the
SysAdmin role). '
>|||clever! <g>
"DHatheway" <dlhatheway@.mmm.com.nospam> wrote in message
news:c5k11b$pfh$1@.tuvok3.mmm.com...
> If you want to kill off all your user connections, you might try a query
> like this:
> select 'kill '+convert(varchar(10),spid) from sysprocesses where dbid not
in
> (select dbid from sysdatabases where name in ('master','msdb') union all
> select 0 as dbid) order by sysprocesses.spid
> "Shannon" <anonymous@.discussions.microsoft.com> wrote in message
> news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> killed them with the status='sleeping', I realized that i might also
killed
> some connections are not supposed to be killed.
> (reason: The data area passed to a system call is too small). '
> SQLserverAgent Service on local computer. The service did not return an
> error. This could be an internal Windows error or an internal service
error.
> If this error persists, contact your system administrator. ' and also the
> following message appears in Application log '
> to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of
the
> SysAdmin role). '
>

kill inactive connections

My company has a software designed in Visual Studio 6 using DAO and
ODBC, this software is opening too much connections to SQL Server and
maintaining them openned during too much time.
So, how can I kill the inactive connections using T-SQL?Hi
You should be calling the close method on the ADO connection object.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthclose.asp
John
"Juliano.net" wrote:
> My company has a software designed in Visual Studio 6 using DAO and
> ODBC, this software is opening too much connections to SQL Server and
> maintaining them openned during too much time.
> So, how can I kill the inactive connections using T-SQL?
>|||John, as I told you, I'm using DAO and as someone told me, connections
to ODBC are not closed when the close method of DAO classes are called.
Thanks.
Any help will be appreciated.
John Bell wrote:
> Hi
> You should be calling the close method on the ADO connection object.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthclose.asp
> John
> "Juliano.net" wrote:
> > My company has a software designed in Visual Studio 6 using DAO and
> > ODBC, this software is opening too much connections to SQL Server and
> > maintaining them openned during too much time.
> >
> > So, how can I kill the inactive connections using T-SQL?
> >
> >|||Hi
I don't remember any issue with DAO connections!
Have you checked that you are actually executing the close method and
verified that the connection is not closed?
John
"Juliano.net" wrote:
> John, as I told you, I'm using DAO and as someone told me, connections
> to ODBC are not closed when the close method of DAO classes are called.
> Thanks.
> Any help will be appreciated.
>
> John Bell wrote:
> > Hi
> >
> > You should be calling the close method on the ADO connection object.
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthclose.asp
> >
> > John
> >
> > "Juliano.net" wrote:
> >
> > > My company has a software designed in Visual Studio 6 using DAO and
> > > ODBC, this software is opening too much connections to SQL Server and
> > > maintaining them openned during too much time.
> > >
> > > So, how can I kill the inactive connections using T-SQL?
> > >
> > >
>|||John, every data access that I do on my code, I call a method to close
database connection.
John Bell wrote:
> Hi
> I don't remember any issue with DAO connections!
> Have you checked that you are actually executing the close method and
> verified that the connection is not closed?
> John
> "Juliano.net" wrote:
> > John, as I told you, I'm using DAO and as someone told me, connections
> > to ODBC are not closed when the close method of DAO classes are called.
> >
> > Thanks.
> >
> > Any help will be appreciated.
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > You should be calling the close method on the ADO connection object.
> > >
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthclose.asp
> > >
> > > John
> > >
> > > "Juliano.net" wrote:
> > >
> > > > My company has a software designed in Visual Studio 6 using DAO and
> > > > ODBC, this software is opening too much connections to SQL Server and
> > > > maintaining them openned during too much time.
> > > >
> > > > So, how can I kill the inactive connections using T-SQL?
> > > >
> > > >
> >
> >|||Hi
If you are closing the connection after each command then you will be
creating un-necessary overhead. The session could stay open for the duration
of the users session! Also step through your code to make sure that the close
method is being executed and make sure there are traps for any exception
handling.
John
"Juliano.net" wrote:
> John, every data access that I do on my code, I call a method to close
> database connection.
>
> John Bell wrote:
> > Hi
> >
> > I don't remember any issue with DAO connections!
> >
> > Have you checked that you are actually executing the close method and
> > verified that the connection is not closed?
> >
> > John
> >
> > "Juliano.net" wrote:
> >
> > > John, as I told you, I'm using DAO and as someone told me, connections
> > > to ODBC are not closed when the close method of DAO classes are called.
> > >
> > > Thanks.
> > >
> > > Any help will be appreciated.
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > You should be calling the close method on the ADO connection object.
> > > >
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthclose.asp
> > > >
> > > > John
> > > >
> > > > "Juliano.net" wrote:
> > > >
> > > > > My company has a software designed in Visual Studio 6 using DAO and
> > > > > ODBC, this software is opening too much connections to SQL Server and
> > > > > maintaining them openned during too much time.
> > > > >
> > > > > So, how can I kill the inactive connections using T-SQL?
> > > > >
> > > > >
> > >
> > >
>

Kill connection

Hello,
I do maintenance on the Back end
and have like 10 - 20 connections open...specialized scrips i run and they
dont need to be stored proc's

is there a way to kill the connection when the script is finished...from my
client side....
not just disconnect...cause server still has the pool of the connection...i
want to kill the pool'd connection also

thanks
Dave PDaveP (analizer1@.yahoo.com) writes:

Quote:

Originally Posted by

I do maintenance on the Back end
and have like 10 - 20 connections open...specialized scrips i run and they
dont need to be stored proc's
>
is there a way to kill the connection when the script is finished...from
my client side.... not just disconnect...cause server still has the
pool of the connection...i want to kill the pool'd connection also

I'm not sure that I follow. You have a maintenance job that runs from
a client that opens multiple connections?

When the client exits, all pooled connections will go away, since the
pools lives in the process space of the client, not of SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Keep a few connections open all the time or open/close connections on the fly?

Just a quick question about connection management. My application will
never need more than 1 or 2 connections about at any given time. Also, I do
not expect many users to be connected at any given time. For efficiency, I
would like to keep connections alive throughout the lifetime of the objects
requiring them, rather than opening a new connection, executing code and
then closing it again. What is the most efficient way of doing this?
Should I perform the open/close or just one open when I create the object
and a close when I dispose of it?Robin Tucker (r.tucker@.thermoteknix.com) writes:
> Just a quick question about connection management. My application will
> never need more than 1 or 2 connections about at any given time. Also,
> I do not expect many users to be connected at any given time. For
> efficiency, I would like to keep connections alive throughout the
> lifetime of the objects requiring them, rather than opening a new
> connection, executing code and then closing it again. What is the most
> efficient way of doing this? Should I perform the open/close or just one
> open when I create the object and a close when I dispose of it?

First of all, what client library are you using?

Some client libraries, ADO and ADO.Net employs connection pooling, so
when you officially disconnects, the client library actually keeps the
connection around for some 60 seconds, in case you like to reuse it.
Connection pooling can be quite essential in web applications.

If you are writing a VB application and you know you will never have
any users connected, I don't see anything wrong in creating a connection
and keep the connection object alive as a global variable.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp