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
>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?
>
>.
>|||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...
> 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
> >
> >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?
> >
> >
> >.
> >|||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
>community of SQL Server professionals.
>www.sqlpass.org
><anonymous@.discussions.microsoft.com> wrote in message
>news:27fb401c46419$10b11fe0$a501280a@.phx.gbl...
>> 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
>> >
>> >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?
>> >
>> >
>> >.
>> >
>
>.
>|||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:
> 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
>>community of SQL Server professionals.
>>www.sqlpass.org
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:27fb401c46419$10b11fe0$a501280a@.phx.gbl...
>>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
>>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?
>>
>>.
>>
>>.|||status is 12
BTW I am using SQL 7.0
>--Original Message--
>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:
>> 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
>>community of SQL Server professionals.
>>www.sqlpass.org
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:27fb401c46419$10b11fe0$a501280a@.phx.gbl...
>>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
>>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?
>>
>>.
>>
>>.
>.
>|||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:
> status is 12
> BTW I am using SQL 7.0
>>--Original Message--
>>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:
>>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
>>
>>community of SQL Server professionals.
>>www.sqlpass.org
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:27fb401c46419$10b11fe0$a501280a@.phx.gbl...
>>
>>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
>>
>>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?
>>
>>.
>>
>>.
>>
>>.|||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?
>--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:
>> status is 12
>> BTW I am using SQL 7.0
>>--Original Message--
>>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:
>>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
>>
>>community of SQL Server professionals.
>>www.sqlpass.org
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:27fb401c46419$10b11fe0$a501280a@.phx.gbl...
>>
>>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
>>
>>>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?
>>>
>>>
>>>.
>>>
>>
>>.
>>
>>.
>.
>|||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,
>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?
>>
>.
>|||Hi,
It seems the database is corrupted. You have to restore from the last known
good backup file.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:2859d01c4643c$cbcb4c10$a401280a@.phx.gbl...
> 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,
> >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?
> >>
> >>
> >.
> >