We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

sql 2008 locally

shanj
shanj asked
on
Medium Priority
648 Views
Last Modified: 2012-05-11
stuck ,
I have an sql 2008 local on my pc.

i start management studio and  connect to "ABC\SQLEXPRESS"   result OK
when i connect to query i get en error
"Server is in single mode. Only one administrator can connect at this time.(microsoft sql server error : 18461)
Comment
Watch Question

 1.  Use SP_Who to find the session ID of the active connection to the database
  2.   Use KILL <session id> to terminate the connection.
  3.  Then do  ALTER DATABASE DBNAME SET MULTI_USER

where DBNAME is the name of database you are trying to connect to.

if the above method does not work you may try:-

USE MASTER

Alter Database DBNAME
SET SINGLE_USER With ROLLBACK IMMEDIATE
 
 RESTORE DATABASE DBNAME
 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DBNAME
.bak'

GO

ALTER DATABASE DBNAME
SET MULTI_USER

GO

Open in new window

sorry the restore part is not required .following will do:-

USE MASTER
GO

Alter Database DBNAME
SET SINGLE_USER With ROLLBACK IMMEDIATE

ALTER DATABASE DBNAME
SET MULTI_USER

GO

Open in new window


where DBNAME is the Database name you are trying to connect to.
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Your server seems to be running in single user mode. Please follow steps and post results from my comment ID:35374033 from following thread:
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26949688.html

Author

Commented:
hello Daniel PL:

this is the error i get

2011-04-20 10:37:55 - ! [298] SQLServer Error: 15247, User does not have permission to perform this action. [SQLSTATE 42000] (DisableAgentXPs)
2011-04-20 10:37:55 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_has_server_access', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2011-04-20 10:37:55 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_get_startup_info', database 'msdb', schema 'dbo'. [SQLSTATE 42000]
2011-04-20 10:37:55 - ! [298] SQLServer Error: 229, The UPDATE permission was denied on the object 'sysalerts', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DumpAndCheckServerVersion)
2011-04-20 10:37:55 - ! [298] SQLServer Error: 229, The INSERT permission was denied on the object 'syssessions', database 'msdb', schema 'dbo'. [SQLSTATE 42000]
2011-04-20 10:37:55 - ! [000] Error creating a new session
2011-04-20 10:37:55 - ? [098] SQLServerAgent terminated (normally)


But i have sysadmin acess.

Author

Commented:
sorry guys i do not have a problem with my DB...but ther server
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
I wanted you to check in SQL Server configuration manager SQLEXPRESS start parameters:

-open sql server configuration manager (e.g. by start->run-> (type) sqlservermanager10.msc) then select services
-right click SQL Server (SQLEXPRESS) -> properties
-select the advanced tab from the services properties window open dropdown list - the startup parameters
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
sorry guys i do not have a problem with my DB...but ther server

Yes, indeed, please follow instructions provided and we'll go thru this problem ;)

Author

Commented:
-m;-dc:\Programfiler\Microsoft SQL Server\shan\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\master.mdf;-ec:\Programfiler\Microsoft SQL Server\shan\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG;-lc:\Programfiler\Microsoft SQL Server\shan\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mastlog.ldf
DB Expert/Architect
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Excellent Daniel Thanks again
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
You're welcome ;)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.