Booting "users" from a database

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)

Booting "users" from a database

Author

Topic

toddhd
Yak Posting Veteran

55 Posts

Posted - 2004-11-02 : 13:13:00

I am a webmaster/programmer. I keep a local copy of my application and database on my laptop, and sometimes need to "synch" that info up to the main IIS and SQL Server.

Enterprise Managers "export" capability is a tricky thing - if I don't do everything perfectly, then I wind up with duplicate records, missing records, errors - it's a mess. So I've found it much easier just to backup the database on my laptop, then restore it on the server. Quick and easy, no mistakes.

But lately I have a problem. When I go to restore the backup on the server, it gives a message that says something like "sql database state cannot be changed while other users are using the database". I'm not sure how this happens, as I'm the only person using this database, even on the main server. If I right click on the database and try to take it offline, I get the same message.

The only way I seem to be able to get around this message is to stop/start SQL server, but that's a feasable solution, as there are many users that use other databases on this server.

Is there a way to convince Enterprise Manager to let me take the database offline without restarting the SQL server?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-02 : 13:20:48

try

select spid from master..sysprocesses where dbid = db_id('mydb')
then you can kill those spids.

You will probably find that it's one of your processes that's doing it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

toddhd
Yak Posting Veteran

55 Posts

Posted - 2004-11-02 : 13:27:22

quote:
Originally posted by nr

try

select spid from master..sysprocesses where dbid = db_id('mydb')
then you can kill those spids.

You will probably find that it's one of your processes that's doing it.

Assume I'm fairly SQL stupid from an Admin POV, cause I am. :)

I ran the statement above (changing mydb to my database name of course) and it spat back about nine numbers. What are those, and how do I shut them down?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-02 : 13:32:56

This should help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40077

The 3rd example will do what you're looking for.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-02 : 13:40:56

sp_murder.... Nice!!

Some alternative sp names (According to Websters)
If you want to make it your own :)


sp_annihilation
sp_assassination
sp_big_chill
sp_blood
sp_bloodshed
sp_bump_off
sp_butchery
sp_capital_murder
sp_carnage
sp_crime
sp_death
sp_destruction
sp_dispatching
sp_dust_off
sp_felony
sp_foul_play
sp_hit
sp_homicne
sp_kiss_off
sp_knifing
sp_liqunation
sp_lynching
sp_manslaughter
sp_massacre
sp_off
sp_offing
sp_one_way_ticket
sp_shooting
sp_slaying
sp_taking_out
sp_terrorism
sp_the_business
sp_the_works
sp_wasting

toddhd
Yak Posting Veteran

55 Posts

Posted - 2004-11-02 : 13:54:18

quote:
Originally posted by robvolk

This should help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40077

The 3rd example will do what you're looking for.

Yup, that was EXACTLY what I was looking for. Thank you very much!!!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-02 : 22:32:40

, i think he has a point, coz MS named it KILL.

quote:

Originally posted by ehorn

sp_murder.... Nice!!

Some alternative sp names (According to Websters)
If you want to make it your own :)


sp_annihilation
sp_assassination
sp_big_chill
sp_blood
sp_bloodshed
sp_bump_off
sp_butchery
sp_capital_murder
sp_carnage
sp_crime
sp_death
sp_destruction
sp_dispatching
sp_dust_off
sp_felony
sp_foul_play
sp_hit
sp_homicne
sp_kiss_off
sp_knifing
sp_liqunation
sp_lynching
sp_manslaughter
sp_massacre
sp_off
sp_offing
sp_one_way_ticket
sp_shooting
sp_slaying
sp_taking_out
sp_terrorism
sp_the_business
sp_the_works
sp_wasting

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources