Help! sp_dboption

I was trying to set a database to single user only but it
was taking some time (about a minute) so I cancelled the
query.
It is still attempting to cancel 6 mintes later...
any ideas?
Hi ,
If it is sql 2000, execute:-
Alter database <dbname> set single_user with rollback immediate
If it is SQL 7 or older version:-
Kill all the users connected to the database first then use sp_dboption
sp_dboption 'dbname','single user',true
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:2810701c46417$0328d6d0$a401280a@.phx.gbl...
> I was trying to set a database to single user only but it
> was taking some time (about a minute) so I cancelled the
> query.
> It is still attempting to cancel 6 mintes later...
> any ideas?
|||i eventually got a connection broken error message.
problem is I can't access the database now either through
query analyser or enterprise manager.

>--Original Message--
>Hi ,
>If it is sql 2000, execute:-
>Alter database <dbname> set single_user with rollback
immediate
>If it is SQL 7 or older version:-
>Kill all the users connected to the database first then
use sp_dboption[vbcol=seagreen]
>sp_dboption 'dbname','single user',true
>--
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:2810701c46417$0328d6d0$a401280a@.phx.gbl...
it
>
>.
>
|||There may be someone else, who is the single user... Do an sp_who and see
who that is and perhaps kill them
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:27fb401c46419$10b11fe0$a501280a@.phx.gbl...[vbcol=seagreen]
> i eventually got a connection broken error message.
> problem is I can't access the database now either through
> query analyser or enterprise manager.
> immediate
> use sp_dboption
> it
|||no other users except me...i can;t access the database at
all now.
>--Original Message--
>There may be someone else, who is the single user... Do
an sp_who and see
>who that is and perhaps kill them
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's[vbcol=seagreen]
>community of SQL Server professionals.
>www.sqlpass.org
><anonymous@.discussions.microsoft.com> wrote in message
>news:27fb401c46419$10b11fe0$a501280a@.phx.gbl...
through[vbcol=seagreen]
but[vbcol=seagreen]
the
>
>.
>
|||anon,
What error do you get? What status is the database in? Run this in master:
select status from sysdatabases where name = <yourdbname>
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
anonymous@.discussions.microsoft.com wrote:[vbcol=seagreen]
> no other users except me...i can;t access the database at
> all now.
>
> an sp_who and see
>
> (PASS) and it's
>
> through
>
> but
>
> the
|||OK, now what error do you get when you type in
use <mydb>
in Query Analyzer?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
anonymous@.discussions.microsoft.com wrote:[vbcol=seagreen]
> status is 12
> BTW I am using SQL 7.0
>
> Run this in master:
>
> at
>
> then
|||takes a while to run this, so i stopped it - the database
is a test db, but it is sitting on the production server.
i don't want to run anything that might crash the server.
what is the 'safest' thing to do?
[vbcol=seagreen]
>--Original Message--
>OK, now what error do you get when you type in
>use <mydb>
>in Query Analyzer?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>anonymous@.discussions.microsoft.com wrote:
<yourdbname>[vbcol=seagreen]
Do[vbcol=seagreen]
rollback[vbcol=seagreen]
message[vbcol=seagreen]
cancelled
>.
>
|||Can you try detaching and attaching the database? You can do this in EM,
or using sp_detach_db, sp_attach_db - look them up in BOL.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
anonymous@.discussions.microsoft.com wrote:
> takes a while to run this, so i stopped it - the database
> is a test db, but it is sitting on the production server.
> i don't want to run anything that might crash the server.
> what is the 'safest' thing to do?
>
|||just checked the SQL logs, and the following error
appeared first:
Could not open FCB for invalid file ID 49154 in database
<dbname>. Table or database may be corrupted..
Since then, getting the following error:
Time out occurred while waiting for buffer latch type 2,
bp 0x14b7be40, page (49154:-1325224958), stat 0x405,
object ID 10:641697634:0, waittime 5000. Continuing to
wait.

>--Original Message--
>Can you try detaching and attaching the database? You can
do this in EM,[vbcol=seagreen]
>or using sp_detach_db, sp_attach_db - look them up in BOL.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>anonymous@.discussions.microsoft.com wrote:
database[vbcol=seagreen]
server.[vbcol=seagreen]
server.
>.
>