Showing posts with label detach. Show all posts
Showing posts with label detach. Show all posts

Friday, February 10, 2012

Clearing Single User Mode/lock

What is the best way to clear Single User mode from SQL Server 2000?

I have tried going to all tasks and selecting detach and clear Connections using this database. I then click cancel and go into the properties and options and clear the Restrict access check box. This seems to work some times but not all the times. I think it is because something else connects to it to it while I am going through the above steps. As a last resort I have detached the database and re-attach it.

What is causing the single user mode to happen in the first place?

I am a little confused, the system is going into single user mode of its own accord and you want to get it back out again? Have you looked at the error log to find out why it is going into Single User?

This will set a database into single user and then back out again;

ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

|||

The error log has the following

DBCC CHECKDB (ANS, repair_fast) executed by zz101zz found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 9 seconds.

I don't see any other entrys or errors.

|||
Does script I gave you bring it out?|||I will try it next time it goes into single user mode. Thanks!|||

One of my coworkers tried the script and batch file but said that he got a message that it couldn't be done when database is already open.

ALTER DATABASE ANS
SET MULTI_USER;
GO

echo off
Echo *** This bat will attempt to Clear Single User Mode on ANS Database ***
echo *** This hasn't been tested yet ***
echo *** If you are unsure CTRL C will get you out ***
Pause
osql -S Server -U -P -i ClearSingleUserModeOn-ANS_Database.sql
Pause

|||Please post the exact error message|||

Exact Message is

Msg 5064, Level 16, State 1, Server OHP03028, Line 1
Changes to the state or options of database 'ANS' cannot be made at this time.
The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Server OHP03028, Line 1
ALTER DATABASE statement failed.

|||So, you should find this user's spid and kill his connection by KILL command, and then you will be able to change the database state.|||

I'm having the same problem... and tried to kill any connection to the database from the activity monitor... but did not find any connection to it...

Clearing Single User Mode/lock

What is the best way to clear Single User mode from SQL Server 2000?

I have tried going to all tasks and selecting detach and clear Connections using this database. I then click cancel and go into the properties and options and clear the Restrict access check box. This seems to work some times but not all the times. I think it is because something else connects to it to it while I am going through the above steps. As a last resort I have detached the database and re-attach it.

What is causing the single user mode to happen in the first place?

I am a little confused, the system is going into single user mode of its own accord and you want to get it back out again? Have you looked at the error log to find out why it is going into Single User?

This will set a database into single user and then back out again;

ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

|||

The error log has the following

DBCC CHECKDB (ANS, repair_fast) executed by zz101zz found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 9 seconds.

I don't see any other entrys or errors.

|||
Does script I gave you bring it out?|||I will try it next time it goes into single user mode. Thanks!|||

One of my coworkers tried the script and batch file but said that he got a message that it couldn't be done when database is already open.

ALTER DATABASE ANS
SET MULTI_USER;
GO

echo off
Echo *** This bat will attempt to Clear Single User Mode on ANS Database ***
echo *** This hasn't been tested yet ***
echo *** If you are unsure CTRL C will get you out ***
Pause
osql -S Server -U -P -i ClearSingleUserModeOn-ANS_Database.sql
Pause

|||Please post the exact error message|||

Exact Message is

Msg 5064, Level 16, State 1, Server OHP03028, Line 1
Changes to the state or options of database 'ANS' cannot be made at this time.
The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Server OHP03028, Line 1
ALTER DATABASE statement failed.

|||So, you should find this user's spid and kill his connection by KILL command, and then you will be able to change the database state.|||

I'm having the same problem... and tried to kill any connection to the database from the activity monitor... but did not find any connection to it...

Clearing Connections

I use the below code to detach my database. Sometimes it fails because there
is an active connection. Is there any command that can overide that
protection or clear the connection without me having to manually detach
inside Ent. Manager?
CODE **********
use master
go
sp_detach_db 'mySQLdatabase'
goHave a look at
http://msdn2.microsoft.com/en-us/library/ms188031(SQL.90).aspx. There's a
section on Obtaining Exclusive Access that tells you how to use ALTER
DATABASE to accomplish what you're trying to do.
Sincerely,
Steve Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"scott" <sbailey@.mileslumber.com> wrote in message
news:uoQm8bgdGHA.1276@.TK2MSFTNGP03.phx.gbl...
>I use the below code to detach my database. Sometimes it fails because
>there is an active connection. Is there any command that can overide that
>protection or clear the connection without me having to manually detach
>inside Ent. Manager?
> CODE **********
> use master
> go
> sp_detach_db 'mySQLdatabase'
> go
>