Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql 2008 locally

Posted on 2011-04-20
13
Medium Priority
?
629 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)
0
Comment
Question by:shanj
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35431290
 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.

0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35431351
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

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35431414
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.
0
 
LVL 15

Expert Comment

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

Author Comment

by:shanj
ID: 35431576
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.
0
 

Author Comment

by:shanj
ID: 35431577
sorry guys i do not have a problem with my DB...but ther server
0
 
LVL 15

Expert Comment

by:Daniel_PL
ID: 35431588
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
0
 
LVL 15

Expert Comment

by:Daniel_PL
ID: 35431596
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 ;)
0
 

Author Comment

by:shanj
ID: 35431604
-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
0
 
LVL 15

Accepted Solution

by:
Daniel_PL earned 2000 total points
ID: 35431616
Please remove -m; and restart service.
It should look like:
-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
0
 

Author Closing Comment

by:shanj
ID: 35431661
Excellent Daniel Thanks again
0
 
LVL 15

Expert Comment

by:Daniel_PL
ID: 35431666
You're welcome ;)
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question