Hello,
In enterprise manager when detaching DB, there is an option to clear open
connections without actually detaching a database.
I'm looking for the sp with the ability of clearing cconnections.
I failed looking it up in books on line.
I'm using SQL 2000
I would be grateful for any help
Thanks,
GaryGary
KILL command
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
> Hello,
> In enterprise manager when detaching DB, there is an option to clear open
> connections without actually detaching a database.
> I'm looking for the sp with the ability of clearing cconnections.
> I failed looking it up in books on line.
> I'm using SQL 2000
> I would be grateful for any help
> Thanks,
> Gary|||If the suggested KILL is too time-consuming as you have to kill all the
connections you can use:
ALTER DATABASE <Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
HTH, jens Suessmeyer.|||Gary,
if you want to drop all the connections and prevent new ones:
"alter database YOURDB SET single_user with rollback immediate"
If you just want to drop all the existing connections without preventing new
ones, then you can cursor through master.dbo.sysprocesses, filter on
database and use exec to run the KILL command. If you want to go down this
route, have a go at the cursor and post it up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Hello Uri,
Kill terminates process, I need to terminate all connections to the specific
databse.
Thanks
Gary
"Uri Dimant" wrote:
> Gary
> KILL command
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
> > Hello,
> >
> > In enterprise manager when detaching DB, there is an option to clear open
> > connections without actually detaching a database.
> > I'm looking for the sp with the ability of clearing cconnections.
> > I failed looking it up in books on line.
> >
> > I'm using SQL 2000
> >
> > I would be grateful for any help
> >
> > Thanks,
> > Gary
>
>|||Gary
KILL can be used to terminate a normal connection, which internally
terminates the transactions associated with the given SPID
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:B9D5EA96-C63D-452B-8082-62CFDF5CBC96@.microsoft.com...
> Hello Uri,
> Kill terminates process, I need to terminate all connections to the
> specific
> databse.
> Thanks
> Gary
> "Uri Dimant" wrote:
>> Gary
>> KILL command
>>
>> "Gary" <Gary@.discussions.microsoft.com> wrote in message
>> news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
>> > Hello,
>> >
>> > In enterprise manager when detaching DB, there is an option to clear
>> > open
>> > connections without actually detaching a database.
>> > I'm looking for the sp with the ability of clearing cconnections.
>> > I failed looking it up in books on line.
>> >
>> > I'm using SQL 2000
>> >
>> > I would be grateful for any help
>> >
>> > Thanks,
>> > Gary
>>
Showing posts with label connections. Show all posts
Showing posts with label connections. Show all posts
Tuesday, March 27, 2012
Clear connections to DB
Hello,
In enterprise manager when detaching DB, there is an option to clear open
connections without actually detaching a database.
I'm looking for the sp with the ability of clearing cconnections.
I failed looking it up in books on line.
I'm using SQL 2000
I would be grateful for any help
Thanks,
Gary
Gary
KILL command
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
> Hello,
> In enterprise manager when detaching DB, there is an option to clear open
> connections without actually detaching a database.
> I'm looking for the sp with the ability of clearing cconnections.
> I failed looking it up in books on line.
> I'm using SQL 2000
> I would be grateful for any help
> Thanks,
> Gary
|||If the suggested KILL is too time-consuming as you have to kill all the
connections you can use:
ALTER DATABASE <Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
HTH, jens Suessmeyer.
|||Gary,
if you want to drop all the connections and prevent new ones:
"alter database YOURDB SET single_user with rollback immediate"
If you just want to drop all the existing connections without preventing new
ones, then you can cursor through master.dbo.sysprocesses, filter on
database and use exec to run the KILL command. If you want to go down this
route, have a go at the cursor and post it up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hello Uri,
Kill terminates process, I need to terminate all connections to the specific
databse.
Thanks
Gary
"Uri Dimant" wrote:
> Gary
> KILL command
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
>
>
|||Gary
KILL can be used to terminate a normal connection, which internally
terminates the transactions associated with the given SPID
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:B9D5EA96-C63D-452B-8082-62CFDF5CBC96@.microsoft.com...[vbcol=seagreen]
> Hello Uri,
> Kill terminates process, I need to terminate all connections to the
> specific
> databse.
> Thanks
> Gary
> "Uri Dimant" wrote:
In enterprise manager when detaching DB, there is an option to clear open
connections without actually detaching a database.
I'm looking for the sp with the ability of clearing cconnections.
I failed looking it up in books on line.
I'm using SQL 2000
I would be grateful for any help
Thanks,
Gary
Gary
KILL command
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
> Hello,
> In enterprise manager when detaching DB, there is an option to clear open
> connections without actually detaching a database.
> I'm looking for the sp with the ability of clearing cconnections.
> I failed looking it up in books on line.
> I'm using SQL 2000
> I would be grateful for any help
> Thanks,
> Gary
|||If the suggested KILL is too time-consuming as you have to kill all the
connections you can use:
ALTER DATABASE <Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
HTH, jens Suessmeyer.
|||Gary,
if you want to drop all the connections and prevent new ones:
"alter database YOURDB SET single_user with rollback immediate"
If you just want to drop all the existing connections without preventing new
ones, then you can cursor through master.dbo.sysprocesses, filter on
database and use exec to run the KILL command. If you want to go down this
route, have a go at the cursor and post it up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hello Uri,
Kill terminates process, I need to terminate all connections to the specific
databse.
Thanks
Gary
"Uri Dimant" wrote:
> Gary
> KILL command
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
>
>
|||Gary
KILL can be used to terminate a normal connection, which internally
terminates the transactions associated with the given SPID
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:B9D5EA96-C63D-452B-8082-62CFDF5CBC96@.microsoft.com...[vbcol=seagreen]
> Hello Uri,
> Kill terminates process, I need to terminate all connections to the
> specific
> databse.
> Thanks
> Gary
> "Uri Dimant" wrote:
Clear connections to DB
Hello,
In enterprise manager when detaching DB, there is an option to clear open
connections without actually detaching a database.
I'm looking for the sp with the ability of clearing cconnections.
I failed looking it up in books on line.
I'm using SQL 2000
I would be grateful for any help
Thanks,
GaryGary
KILL command
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
> Hello,
> In enterprise manager when detaching DB, there is an option to clear open
> connections without actually detaching a database.
> I'm looking for the sp with the ability of clearing cconnections.
> I failed looking it up in books on line.
> I'm using SQL 2000
> I would be grateful for any help
> Thanks,
> Gary|||If the suggested KILL is too time-consuming as you have to kill all the
connections you can use:
ALTER DATABASE <Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
HTH, jens Suessmeyer.|||Gary,
if you want to drop all the connections and prevent new ones:
"alter database YOURDB SET single_user with rollback immediate"
If you just want to drop all the existing connections without preventing new
ones, then you can cursor through master.dbo.sysprocesses, filter on
database and use exec to run the KILL command. If you want to go down this
route, have a go at the cursor and post it up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Hello Uri,
Kill terminates process, I need to terminate all connections to the specific
databse.
Thanks
Gary
"Uri Dimant" wrote:
> Gary
> KILL command
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
>
>|||Gary
KILL can be used to terminate a normal connection, which internally
terminates the transactions associated with the given SPID
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:B9D5EA96-C63D-452B-8082-62CFDF5CBC96@.microsoft.com...[vbcol=seagreen]
> Hello Uri,
> Kill terminates process, I need to terminate all connections to the
> specific
> databse.
> Thanks
> Gary
> "Uri Dimant" wrote:
>
In enterprise manager when detaching DB, there is an option to clear open
connections without actually detaching a database.
I'm looking for the sp with the ability of clearing cconnections.
I failed looking it up in books on line.
I'm using SQL 2000
I would be grateful for any help
Thanks,
GaryGary
KILL command
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
> Hello,
> In enterprise manager when detaching DB, there is an option to clear open
> connections without actually detaching a database.
> I'm looking for the sp with the ability of clearing cconnections.
> I failed looking it up in books on line.
> I'm using SQL 2000
> I would be grateful for any help
> Thanks,
> Gary|||If the suggested KILL is too time-consuming as you have to kill all the
connections you can use:
ALTER DATABASE <Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
HTH, jens Suessmeyer.|||Gary,
if you want to drop all the connections and prevent new ones:
"alter database YOURDB SET single_user with rollback immediate"
If you just want to drop all the existing connections without preventing new
ones, then you can cursor through master.dbo.sysprocesses, filter on
database and use exec to run the KILL command. If you want to go down this
route, have a go at the cursor and post it up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Hello Uri,
Kill terminates process, I need to terminate all connections to the specific
databse.
Thanks
Gary
"Uri Dimant" wrote:
> Gary
> KILL command
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
>
>|||Gary
KILL can be used to terminate a normal connection, which internally
terminates the transactions associated with the given SPID
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:B9D5EA96-C63D-452B-8082-62CFDF5CBC96@.microsoft.com...[vbcol=seagreen]
> Hello Uri,
> Kill terminates process, I need to terminate all connections to the
> specific
> databse.
> Thanks
> Gary
> "Uri Dimant" wrote:
>
Clear Connections command
Scenario: You have a database that is in use and you want to detach it. You
use enterprise manager to Detach Database. A dialog box appears that lists
the Database status. It says there are 3 Connections using this database.
Next to the number of connections is a Clear button that will drop the active
connections so that you can continue the process of detaching the database.
Does anyone know the command equivalent to pressing the Clear button?
I don't want to use the graphical interface and I assume there must be some
way to issue a command that would do the same thing as pressing the Clear
button in the GUI.
I don't actually want to detach the database - I am just trying to clear all
connections other than my own. Once I finish clearing connections, I want to
issue a command to temporarily put the database into single user mode.
Alter Database FOO set SINGLE_USER with Rollback Immediate
Everything you want in one command. See the ALTER DATABASE command in BOL
for details.
To set it back to 'normal'
Alter Database FOO set MULTI_USER
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:7EA72915-DF9B-437B-9E08-AE54D210AB9C@.microsoft.com...
> Scenario: You have a database that is in use and you want to detach it.
You
> use enterprise manager to Detach Database. A dialog box appears that
lists
> the Database status. It says there are 3 Connections using this database.
> Next to the number of connections is a Clear button that will drop the
active
> connections so that you can continue the process of detaching the
database.
> Does anyone know the command equivalent to pressing the Clear button?
> I don't want to use the graphical interface and I assume there must be
some
> way to issue a command that would do the same thing as pressing the Clear
> button in the GUI.
> I don't actually want to detach the database - I am just trying to clear
all
> connections other than my own. Once I finish clearing connections, I want
to
> issue a command to temporarily put the database into single user mode.
sqlsql
use enterprise manager to Detach Database. A dialog box appears that lists
the Database status. It says there are 3 Connections using this database.
Next to the number of connections is a Clear button that will drop the active
connections so that you can continue the process of detaching the database.
Does anyone know the command equivalent to pressing the Clear button?
I don't want to use the graphical interface and I assume there must be some
way to issue a command that would do the same thing as pressing the Clear
button in the GUI.
I don't actually want to detach the database - I am just trying to clear all
connections other than my own. Once I finish clearing connections, I want to
issue a command to temporarily put the database into single user mode.
Alter Database FOO set SINGLE_USER with Rollback Immediate
Everything you want in one command. See the ALTER DATABASE command in BOL
for details.
To set it back to 'normal'
Alter Database FOO set MULTI_USER
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:7EA72915-DF9B-437B-9E08-AE54D210AB9C@.microsoft.com...
> Scenario: You have a database that is in use and you want to detach it.
You
> use enterprise manager to Detach Database. A dialog box appears that
lists
> the Database status. It says there are 3 Connections using this database.
> Next to the number of connections is a Clear button that will drop the
active
> connections so that you can continue the process of detaching the
database.
> Does anyone know the command equivalent to pressing the Clear button?
> I don't want to use the graphical interface and I assume there must be
some
> way to issue a command that would do the same thing as pressing the Clear
> button in the GUI.
> I don't actually want to detach the database - I am just trying to clear
all
> connections other than my own. Once I finish clearing connections, I want
to
> issue a command to temporarily put the database into single user mode.
sqlsql
Clear Connections command
Scenario: You have a database that is in use and you want to detach it. You
use enterprise manager to Detach Database. A dialog box appears that lists
the Database status. It says there are 3 Connections using this database.
Next to the number of connections is a Clear button that will drop the active
connections so that you can continue the process of detaching the database.
Does anyone know the command equivalent to pressing the Clear button?
I don't want to use the graphical interface and I assume there must be some
way to issue a command that would do the same thing as pressing the Clear
button in the GUI.
I don't actually want to detach the database - I am just trying to clear all
connections other than my own. Once I finish clearing connections, I want to
issue a command to temporarily put the database into single user mode.Alter Database FOO set SINGLE_USER with Rollback Immediate
Everything you want in one command. See the ALTER DATABASE command in BOL
for details.
To set it back to 'normal'
Alter Database FOO set MULTI_USER
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:7EA72915-DF9B-437B-9E08-AE54D210AB9C@.microsoft.com...
> Scenario: You have a database that is in use and you want to detach it.
You
> use enterprise manager to Detach Database. A dialog box appears that
lists
> the Database status. It says there are 3 Connections using this database.
> Next to the number of connections is a Clear button that will drop the
active
> connections so that you can continue the process of detaching the
database.
> Does anyone know the command equivalent to pressing the Clear button?
> I don't want to use the graphical interface and I assume there must be
some
> way to issue a command that would do the same thing as pressing the Clear
> button in the GUI.
> I don't actually want to detach the database - I am just trying to clear
all
> connections other than my own. Once I finish clearing connections, I want
to
> issue a command to temporarily put the database into single user mode.
use enterprise manager to Detach Database. A dialog box appears that lists
the Database status. It says there are 3 Connections using this database.
Next to the number of connections is a Clear button that will drop the active
connections so that you can continue the process of detaching the database.
Does anyone know the command equivalent to pressing the Clear button?
I don't want to use the graphical interface and I assume there must be some
way to issue a command that would do the same thing as pressing the Clear
button in the GUI.
I don't actually want to detach the database - I am just trying to clear all
connections other than my own. Once I finish clearing connections, I want to
issue a command to temporarily put the database into single user mode.Alter Database FOO set SINGLE_USER with Rollback Immediate
Everything you want in one command. See the ALTER DATABASE command in BOL
for details.
To set it back to 'normal'
Alter Database FOO set MULTI_USER
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:7EA72915-DF9B-437B-9E08-AE54D210AB9C@.microsoft.com...
> Scenario: You have a database that is in use and you want to detach it.
You
> use enterprise manager to Detach Database. A dialog box appears that
lists
> the Database status. It says there are 3 Connections using this database.
> Next to the number of connections is a Clear button that will drop the
active
> connections so that you can continue the process of detaching the
database.
> Does anyone know the command equivalent to pressing the Clear button?
> I don't want to use the graphical interface and I assume there must be
some
> way to issue a command that would do the same thing as pressing the Clear
> button in the GUI.
> I don't actually want to detach the database - I am just trying to clear
all
> connections other than my own. Once I finish clearing connections, I want
to
> issue a command to temporarily put the database into single user mode.
Subscribe to:
Posts (Atom)