clear t-sql connections - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

clear t-sql connections

Author

Topic

cbeganesh
Posting Yak Master

105 Posts

Posted - 2004-08-19 : 16:03:36

I need to clear the all the db connections before detaching it through t-sql, Does anyone used any commands to clear the connections through t-sql
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-19 : 16:13:46

ALTER DATABASE DBNameGoesHere SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Tara

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-19 : 19:16:20

When you need to clear all db connections?
Thanks

mk_garg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-19 : 19:18:26

mk_garg20, maybe I don't understand the question, but you need to clear all connections when performing a restore or detaching the database.

Tara

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-19 : 21:16:06

Thanks Tara!

I never performed any DBA operations. Working as developer.
So when i don't understand anything. I prefer to ask you gurus.
I started reading on this site last month. I feel i am improving my skills.
Thanks to you all.

mk_garg

cbeganesh
Posting Yak Master

105 Posts

Posted - 2004-08-20 : 10:57:10

Sara

In which db i have to execute this statement. 'cos i tried running this in master and it ran sucess . But when i try to detach the db, it said "Cannot detach "db" beacuse it is currently being used". But i see no connections open
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-20 : 12:18:45

It's Tara.

And you run it from master, but you need to change the database name in the command to the one that you want to detach.

ALTER DATABASE ChangeThis SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Tara

cbeganesh
Posting Yak Master

105 Posts

Posted - 2004-08-23 : 15:08:55

Yes Tara
I tried running this from master and ran sp_detachdb to detach the db and gives error "cannot detach the db, currently in use"

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-23 : 15:12:00

USE master
GO

ALTER DATABASE ChangeThis SET SINGLE_USER WITH ROLLBACK IMMEDIATE

EXEC sp_detach_db @dbname = 'ChangeThis', @skipchecks = 'true'

Did you try it like this (above)?

Tara

cbeganesh
Posting Yak Master

105 Posts

Posted - 2004-08-23 : 15:32:45

i used
exec sp_detach_db 'dbname','true'
I also tried with your syntax gets the same error not sure why?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-23 : 16:18:17

The ROLLBACK IMMEDIATE must not be finished rolling back user connections then. You can run sp_who or sp_who2 to determine who is still connected, then run KILL <spidNo> to get rid of them manually.

Tara

gpl
Posting Yak Master

195 Posts

Posted - 2004-08-23 : 18:50:02

quote:
Originally posted by tduggan

The ROLLBACK IMMEDIATE must not be finished rolling back user connections then. You can run sp_who or sp_who2 to determine who is still connected, then run KILL <spidNo> to get rid of them manually.

Tara

We needed to do an automated restore on fail of a DTS job, we came up with this procedure
--yes I know it uses a cursor, but if its good enough for Microsoft ......
Graham


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Kill_All_spids]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Kill_All_spids]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

Create Procedure Kill_All_spids (
@WhichDb	Varchar(80) = ''
) As

/*

	created gpl / aj 08/11/2002

	This procedure uses code languishing in sp_who2
	to produce a cursor of spids of all processes 
	using the named database, except the current one;

	The cursor is processed and each spid is KILLed

*/

Begin
Declare @Unwanted_SPID	SmallInt
Declare @SQLStr		Varchar(10)

	If @WhichDb = '' GoTo ErrEnd --- crash out if no param

	Declare	All_Unwanted_SPIDS_Cursor Cursor FORWARD_ONLY READ_ONLY For
	Select	spid
	From	master..sysprocesses
	Where	spid != @@spid And
		Case dbid 
			When	0 Then '' 
			Else	db_name(dbid) 
		End = @WhichDb 

  
	Open All_Unwanted_SPIDS_Cursor 

	Fetch	Next 
		From	All_Unwanted_SPIDS_Cursor 
		Into	@Unwanted_SPID	

	While @@FETCH_STATUS = 0 Begin
		Set @SQLStr = 'Kill ' + Convert(Varchar(3), @Unwanted_SPID)
		Exec (@SQLStr)

---		now go back for the next one
		Fetch	Next 
			From	All_Unwanted_SPIDS_Cursor 
			Into	@Unwanted_SPID

	End --- Loop

	Close All_Unwanted_SPIDS_Cursor 
	Deallocate All_Unwanted_SPIDS_Cursor 

	Return 0

ErrEnd:

	RaisError ('No Database name provided',16,1)

	Return -1

End

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

cbeganesh
Posting Yak Master

105 Posts

Posted - 2004-08-24 : 09:04:57

thanks graham & tara
Gan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources