Ms Sql Server Error 924
Contents |
SQL database from Single to Multi User Mode. The error we received when trying to do this was. An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SQLServer.Connectioninfo) Database ‘databasename’ is already open sql database stuck in single user mode and can only have one user at a time. (Microsoft SQL Server, Error: 924 database is already open and can only have one user at a time sql server Basically this is due to a connection open on the database as specified in the above error. So the first step sql error 924 was to find out what was connected to do this. To do this we ran the following SQL query (add your database name in the query below) select spid from master..sysprocesses where dbid =
Msg 5064, Level 16, State 1, Line 1
db_id(‘databasename‘) and spid <> @@spid This will display the SPID name in our case it was 60. Now we need to kill the SPID so that we are allowed to connect via running the below query (amend to your SPID Value) Kill 60 We were then able to change the database to multi user mode. By Daniel Davies|2011-03-07T10:22:14+00:00March 2nd, 2011|Uncategorized|1 Comment Share This Story, Choose Your Platform! About the sql server single user mode startup parameters Author: Daniel Davies One Comment balakrishnan.S June 29, 2011 at 8:38 am Thanks, I faced the same issue and fixed. Leave A Comment Cancel reply TagsActive Directory AD RMS BitLocker Cloud CRM 2011 DFS DFSR Disk Space DPM DPM 2010 Exchange 2007 Exchange 2010 Forefront HP Hyper-V IE IIS Lync Lync 2010 Lync Server 2010 Office 365 Office 2010 Outlook Outlook 2010 OWA Powershell RDP SCCM SCOM SCOM 2007 R2 SCVMM Service Manager Sharepoint SP1 SQL SQL 2005 SQL 2008 support TMG TMG 2010 Windows 7 Windows 2008 R2 Windows Server 2003 Windows Server 2008 Windows Update Featured product from risual UCStore Buy Sennheiser SP 20 ML Blogroll biz blog consulting blog risual Categories Uncategorized Recent Posts Find mailbox move history SCCM Update Pack Right Click Options Greyed Out Enable Single Sign On for ADFS for Microsoft Edge Powershell one-liner to find all users in an OU not logged in for a time span Grant Temporary Membership to AD Groups in Windows Server 2016 Blogroll biz blog consulting blog risual Meta Log in Entries RSS Comments RSS WordPress.org Contact Info Risual House, 12 Parker Court, Staffordshire Technology Park, Stafford ST18 0WP Phone: 0845 680 0077 Email: enquries@risual.com Web: http://www.risual.com/ Copyright 2015 risual | All Rights Reserved
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2005 » Administering » Getting server is in single user mode. only one administrator can connect at this time SQL Server 2005 Database out of... 20 posts,Page 1 of 212»» Getting SQL
Alter Database Set Multi_user
Server 2005 Database out of Single-User Mode Rate Topic Display Mode Topic Options Author Message webrunnerwebrunner Posted Thursday, December 21,
Auto_update_statistics_async
2006 7:26 AM SSCrazy Group: General Forum Members Last Login: Today @ 8:19 AM Points: 2,919, Visits: 3,663 Hello,I put a SQL Server 2005 database into single-user mode using Management Studio in an http://support.risualblogs.com/blog/2011/03/02/issue-changing-sql-database-from-single-to-multi-user-mode/ attempt to force a restore over the existing database.But now I can't access the database. I get these errors:When first clicking the database in the tree: "The database ___ is not accessible (Object Explorer)"Then if I try to right-click on the database and view Properties, I get a longer message ending with: "Database '__' is already open and can only have one user at a time. (Microsoft http://www.sqlservercentral.com/Forums/Topic332162-146-1.aspx SQL Server, Error: 924)."But I was the user who put the database into single-user mode, and that's the same user that comes up in the '1 Active Connection' box when I go to Detach the database (not to really detach but to see what the connection is). So I don't know why it won't let me perform any actions on the database.Finally, I tried:ALTER DATABASE ___SET MULTI_USERBut I get the error:"Msg 5064, Level 16, State 1, Line 1Changes to the state or options of database '___' 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, Line 1ALTER DATABASE statement failed."Does anyone know how to revert a database to Multi-User mode in SQL Server 2005? Also, this DB is a back end to a .NET application -- is that application the user that is using up the single-user connection? If so, how can I stop that connection or otherwise set the database back to Multi-User mode?Thanks in advance for any help! -------------------"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.Ref.: http://www.adminarsenal.com/admin-
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers http://stackoverflow.com/questions/14652923/set-database-from-single-user-mode-to-multi-user or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Set database from SINGLE USER mode to MULTI USER up vote 76 down vote favorite 10 I need help with setting a database that was restored in SINGLE USER mode to MULTI USER. Every time sql server I run ALTER DATABASE BARDABARD SET MULTI_USER; GO I get this error: Changes to the state or options of database 'BARDABARD' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. It needs to be in non SINGLE USER mode to set it to another mode, but I can’t set the database in any another mode while it is SINGLE USER mode. sql sql-server share|improve this question edited Jun 21 single user mode at 6:10 dakab 2,20561740 asked Feb 1 '13 at 18:39 Rahna1970418 417147 add a comment| 15 Answers 15 active oldest votes up vote 55 down vote accepted The “user is currently connected to it” might be SQL Server Management Studio window itself. Try selecting the master database and running the ALTER query again. share|improve this answer edited Jun 21 at 6:11 dakab 2,20561740 answered Feb 1 '13 at 18:45 paul 14.4k2135 add a comment| up vote 39 down vote That error message generally means there are other processes connected to the DB. Try running this to see which are connected: exec sp_who That will return you the process and then you should be able to run: kill XXX Then you can run your above statement. Good luck. share|improve this answer answered Feb 1 '13 at 18:46 sgeddes 47.6k42643 add a comment| up vote 22 down vote SQL Server 2012: right-click on the DB > Properties > Options > [Scroll down] State > RestrictAccess > select Multi_user and click OK. Voila! share|improve this answer edited Apr 9 '14 at 12:50 veljasije 2,94132854 answered Apr 9 '14 at 12:41 Div2504 22122 2 Not really, this is what I received when tried your solution :/ "Database 'XXX' is already open and can only have one user at a time. (Microsoft SQL Server, Error: 924)" –Krzysztof Wolny Apr 10 '15 at 7:13 Additionally confirmed in SQL 2008R2. –codo-sapien May 26 '15