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 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!
> 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:
> > 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!
> > Ysandre
> 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:
> 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:
> > > 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!
> > > Ysandre
> >
> > Read up on the ALTER DATABASE command
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com
> >|||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|||This is a multi-part message in MIME format.
--=_NextPart_000_141C_01C6AF0A.34C3D1A0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
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@.TK2MSFTNGP02.phx.gbl...
> 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
--=_NextPart_000_141C_01C6AF0A.34C3D1A0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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 RowlandMost good =judgment comes from experience. Most experience comes from bad judgment. =- Anonymous
"Tracy McKibben" =wrote in message news:%23EmybC0rGHA.1592@.TK2MSFTNGP02.phx.gbl...> =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</FONT></A></BODY></HTML>
--=_NextPart_000_141C_01C6AF0A.34C3D1A0--