Disconnect/Clear connections

I have a scheduled job where I'm attempting to restore a full backup of our
Production database, over our Test/Dev database.
However, it keeps failing.
When I try to run the Restore Database command manually, I get an error that
states "Exclusive access could not be obtained".
Right now, the only way I know how to clear the connections is to go into
the Detach Database task, and select 'Clear all connections'.
I tried using the 'Exec SQL Disconnect ALL' command, but that statement
won't parse.
Is there another way to clear all database connections? Preferebly in
Trasact-SQL language, that I could schedule to run immediately prior to the
Restore Database command?
Please advise.
Thanks!
YsandreYsandre wrote:
> I have a scheduled job where I'm attempting to restore a full backup of ou
r
> Production database, over our Test/Dev database.
> However, it keeps failing.
> When I try to run the Restore Database command manually, I get an error th
at
> states "Exclusive access could not be obtained".
> Right now, the only way I know how to clear the connections is to go into
> the Detach Database task, and select 'Clear all connections'.
> I tried using the 'Exec SQL Disconnect ALL' command, but that statement
> won't parse.
> Is there another way to clear all database connections? Preferebly in
> Trasact-SQL language, that I could schedule to run immediately prior to th
e
> Restore Database command?
> Please advise.
> Thanks!
> Ysandre
Read up on the ALTER DATABASE command
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I did, but I don't see anything in there regarding clearing connections to
the database.
Is there somewhere else I should look?
All I'm looking for is something along the lines of:
EXEC SQL DISCONNECT ALL
Or why the above doesn't parse.
Thanks!
Ysandre
"Tracy McKibben" wrote:

> Ysandre wrote:
> Read up on the ALTER DATABASE command
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||In short: When you go to Detach/Clear in the EMC to disconnect all the
users it is going to do a couple things.
First - It searches through the sysprocesses for the DB you want to
clear and finds the spid's
Then - set noexec off set parseonly off
Lastly - kill spid#
Hope this helps.
Cheers,
Ysandre wrote:[vbcol=seagreen]
> I did, but I don't see anything in there regarding clearing connections to
> the database.
> Is there somewhere else I should look?
> All I'm looking for is something along the lines of:
> EXEC SQL DISCONNECT ALL
> Or why the above doesn't parse.
> Thanks!
> Ysandre
> "Tracy McKibben" wrote:
>|||You could try something like this. Replace the DBID with the database
you are backing up:
DECLARE SpidsToKill CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE dbid = 9
DECLARE @.Spid int
DECLARE @.SQL varchar(255)
OPEN SpidsToKill
FETCH NEXT FROM SpidsToKill
INTO @.Spid
WHILE ( @.@.FETCH_STATUS = 0 )
BEGIN
SELECT @.SQL = 'KILL ' + CONVERT( varchar(10), @.Spid )
EXEC ( @.SQL )
FETCH NEXT FROM SpidsToKill
INTO @.Spid
END
CLOSE SpidsToKill
DEALLOCATE SpidsToKill|||Ysandre wrote:
> I did, but I don't see anything in there regarding clearing connections to
> the database.
You want to use ALTER DATABASE to put the database in SINGLE_USER mode,
WITH immediate ROLLBACK of transactions...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||That worked.
THANK YOU!!!
"10001110101" wrote:

> You could try something like this. Replace the DBID with the database
> you are backing up:
> DECLARE SpidsToKill CURSOR FOR
> SELECT spid
> FROM master..sysprocesses
> WHERE dbid = 9
> DECLARE @.Spid int
> DECLARE @.SQL varchar(255)
> OPEN SpidsToKill
> FETCH NEXT FROM SpidsToKill
> INTO @.Spid
> WHILE ( @.@.FETCH_STATUS = 0 )
> BEGIN
> SELECT @.SQL = 'KILL ' + CONVERT( varchar(10), @.Spid )
> EXEC ( @.SQL )
> FETCH NEXT FROM SpidsToKill
> INTO @.Spid
> END
> CLOSE SpidsToKill
> DEALLOCATE SpidsToKill
>|||Ysandre wrote:
> That worked.
> THANK YOU!!!
>
Ugh, it's too painful to watch... Drop that cursor nonsense, you can
accomplish this with ONE STATEMENT. Since you can't read documentation
and piece together the command yourself, here it is:
ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
That will put the database in single-user mode, allowing one connection
only, and will gracefully rollback any outstanding transactions.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The problem with the script above to 'kill' sessions, is that a session may
immediately reconnect.
The BEST solution, is as Tracy advised. Use
ALTER DATABASE MyDatabase
SET SINGLE_USER WTIH ROLLBACK IMMEDIATE
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:%23EmybC0rGHA.1592@.TK2MSFTNGP0
2.phx.gbl...
> Ysandre wrote:
>
> You want to use ALTER DATABASE to put the database in SINGLE_USER mode,
> WITH immediate ROLLBACK of transactions...
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com