locking a database and then kill those process's (I want to do this as a
precursor to running a database restore, my problem is that sometimes
someone has the database locked running a query and the restore fails as a
result).before restore:
alter database <db> set SINGLE_USER with ROLLBACK AFTER <seconds>
(to view all connected users on db use
select * from sysprocesses where dbid=db_id('db')
kill users with kill <spid> )
"quiglepops" wrote:
> Is it possible to create some Transact SQL which will find all the process
's
> locking a database and then kill those process's (I want to do this as a
> precursor to running a database restore, my problem is that sometimes
> someone has the database locked running a query and the restore fails as a
> result).
>
>|||Here's a script I use... replace <your db> <password> and <server> with your
info.
Let me know if it's useful. Note that it shells out using xp_cmdshell so
you'll need
sy
min rights.use master
go
declare @.dbname sysname, @.spid int, @.sqlstring varchar(8000)
set @.dbname = <your db>
Create Table #spid
(
SPID int,
Status varchar(130),
Login varchar(50),
HostName varchar(50),
BlkBy char(5),
DBName varchar(30),
Command varchar(16),
CPUTime int,
DiskIO bigint,
LastBatch varchar(20),
ProgramName varchar(128),
SPID2 int
)
Insert Into #spid
Exec master..sp_who2
Delete From #spid
Where dbname <> @.dbname
Or dbname is null
/************************/
/* Kill all connections */
/************************/
While Exists (Select * From #spid)
Begin
Select @.spid = SPID
From #spid
If @.spid is null
break
-- Clean up the table so it's not processed again
Delete From #spid
Where spid = @.spid
Set @.sqlstring = 'isql /Usa /n /dmaster /Q"kill ' + cast(@.spid as varchar) +
'" /h-1 /P<password> /S<server>'
Exec master..xp_cmdshell @.sqlstring
End
"quiglepops" <dave_quigley@.hotmail.com> wrote in message
news:uTsbBmuWFHA.1508@.tk2msftngp13.phx.gbl...
> Is it possible to create some Transact SQL which will find all the
process's
> locking a database and then kill those process's (I want to do this as a
> precursor to running a database restore, my problem is that sometimes
> someone has the database locked running a query and the restore fails as a
> result).
>|||In SQL Server 2000, you do not have to enumerate and kill all connections
individually. Instead, you can issue the following which will set the
database to single user mode. The option "with rollback immediate" should
automatically terminate all connections and rollback active transactions.
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
"quiglepops" <dave_quigley@.hotmail.com> wrote in message
news:uTsbBmuWFHA.1508@.tk2msftngp13.phx.gbl...
> Is it possible to create some Transact SQL which will find all the
process's
> locking a database and then kill those process's (I want to do this as a
> precursor to running a database restore, my problem is that sometimes
> someone has the database locked running a query and the restore fails as a
> result).
>|||Thanks for that.
Setting it to Single User mode sounds good, but will it do that when there
are users already in locking the database. Also, will that command on its
own do the job, without me having to find and kill the SPIDS ?
What do I then have to do after the restore to make it available to everyone
again ?
"Aleksandar Grbic" <AleksandarGrbic@.discussions.microsoft.com> wrote in
message news:72376937-9C88-4A0D-972C-558C687FC21F@.microsoft.com...
> before restore:
> alter database <db> set SINGLE_USER with ROLLBACK AFTER <seconds>
> (to view all connected users on db use
> select * from sysprocesses where dbid=db_id('db')
> kill users with kill <spid> )
>
> "quiglepops" wrote:
>
process's
a|||Yes, the IMMEDIATE option should automatically kill all processes (locked or
not). Now that I think about it, the option RESTRICTED_USER may be more
appropriate than SINGLE_USER. This will allow only users with DBO or SA
rights to subsequently connect.
Look up the ALTER DATABASE command in SQL Server Books Online for more
details. If you want, perform a test of this process on another
non-production database like Pubs or Northwind.
After the restore is complete, perform the same command, but with the option
MULTI_USER to allow multiple connections.
"quiglepops" <dave_quigley@.hotmail.com> wrote in message
news:uLQmG0uWFHA.4036@.TK2MSFTNGP10.phx.gbl...
> Thanks for that.
> Setting it to Single User mode sounds good, but will it do that when there
> are users already in locking the database. Also, will that command on its
> own do the job, without me having to find and kill the SPIDS ?
> What do I then have to do after the restore to make it available to
everyone
> again ?
>
> "Aleksandar Grbic" <AleksandarGrbic@.discussions.microsoft.com> wrote in
> message news:72376937-9C88-4A0D-972C-558C687FC21F@.microsoft.com...
> process's
a
as
> a
>|||Thanks for your help guys. The ALTER DATABASE command is perfect. The
scripts are also useful for some of my SQL 7 DB's, so thanks for your help
everyone.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:uxfiDyuWFHA.4036@.TK2MSFTNGP10.phx.gbl...
> Here's a script I use... replace <your db> <password> and <server> with
your
> info.
> Let me know if it's useful. Note that it shells out using xp_cmdshell so
> you'll need
> sy
min rights.> use master
> go
> declare @.dbname sysname, @.spid int, @.sqlstring varchar(8000)
> set @.dbname = <your db>
> Create Table #spid
> (
> SPID int,
> Status varchar(130),
> Login varchar(50),
> HostName varchar(50),
> BlkBy char(5),
> DBName varchar(30),
> Command varchar(16),
> CPUTime int,
> DiskIO bigint,
> LastBatch varchar(20),
> ProgramName varchar(128),
> SPID2 int
> )
> Insert Into #spid
> Exec master..sp_who2
> Delete From #spid
> Where dbname <> @.dbname
> Or dbname is null
>
> /************************/
> /* Kill all connections */
> /************************/
> While Exists (Select * From #spid)
> Begin
> Select @.spid = SPID
> From #spid
> If @.spid is null
> break
> -- Clean up the table so it's not processed again
> Delete From #spid
> Where spid = @.spid
> Set @.sqlstring = 'isql /Usa /n /dmaster /Q"kill ' + cast(@.spid as varchar)
+
> '" /h-1 /P<password> /S<server>'
> Exec master..xp_cmdshell @.sqlstring
> End
>
>
> "quiglepops" <dave_quigley@.hotmail.com> wrote in message
> news:uTsbBmuWFHA.1508@.tk2msftngp13.phx.gbl...
> process's
a
>|||Actually another question. The ALTER DATABASE SET SINGLE_USER WITH ROLLBACK
IMMEDIATE
kills all connections and rolls back straight away.
I want to run this command in a job, then on success in the next step of the
job run the database restore command.
Would it be true to say that some particularly long transactions that would
take a long time to roll back would still be running when the restore
attempts to run,
thus causing a failure, or would it be the case that the job would wait for
the first step to complete (i.e. wait until all transactions, including long
running ones were complete, before proceeding to step 2 and restoring the DB
?
"quiglepops" <dave_quigley@.hotmail.com> wrote in message
news:ekVB5PvWFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Thanks for your help guys. The ALTER DATABASE command is perfect. The
> scripts are also useful for some of my SQL 7 DB's, so thanks for your help
> everyone.
>
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:uxfiDyuWFHA.4036@.TK2MSFTNGP10.phx.gbl...
> your
so
varchar)
> +
a
as
> a
>sql
