Log Shipping-Users in DB - 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

SQL Server Administration (2000)

Log Shipping-Users in DB

Author

Topic

mfemenel
Professor Frink

1421 Posts

Posted - 2004-09-07 : 20:20:18

we're just getting log shipping set up and I had a question about something I saw happen. In the destination database, one of us had query analzyer open on the database doing a select and the restore job failed. This seems a little weird to me. On the maintenance job I checked off the option to kick out users. What we'd wanted to do was use this destination database for reporting but if there's a user in there and the restore fails, we're going to have some issues. Anyone ever seen this? Any way around it?

Mike
"oh, that monkey is going to pay"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-09-07 : 20:35:04

Here's a little ditty I use to kick users out en masse:

CREATE procedure sp_murder @name varchar(128)=null, @doit bit=0 AS
set nocount on
declare @sql varchar(8000)
set @sql=''
IF @name IS NOT NULL
select @sql=@sql + 'KILL ' + cast(spid as varchar) + '; ' +char(13) + char(10) from sysprocesses
where loginame LIKE '%' + @name + '%' or db_name(dbid) LIKE '%' + @name + '%' 
ELSE
select @sql=@sql + 'KILL ' + cast(spid as varchar) + '; ' +char(13) + char(10) from sysprocesses
where (status='sleeping' and spid>8 AND dbid>1 AND nt_username<>'SYSTEM' and login_time<getdate()-1 and last_batch<getdate()-.25)

if @doit=0 select @sql ELSE exec(@sql)

Usage:

exec sp_murder --lists spids that are killable cause they're old
exec sp_murder null, 1 --actually kill the spids returned from above
exec sp_murder 'myDB', 1 --kill users in myDB
exec sp_murder 'username', 1 --kill all connections made by user "username"

Run that before you have to restore the log and it should take care of any user connected to the database.

mfemenel
Professor Frink

1421 Posts

Posted - 2004-09-07 : 20:36:58

Very nice sir. That's a good last resort if I need it. I'd like to figure out if this is normal behavior or not. Seems wrong to me.

Mike
"oh, that monkey is going to pay"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-07 : 22:45:16

sp_murder? lolz, that's very descriptive...

you should kill the spids before restoring. it's a must.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources