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...