Hi all,
I have a test DB that when suspect on a box that had a disk I/O problem. Got that repaired, no I am looking at trying to bring up the DB. We need this db back, and yes, no backups. Win2k box with SQL2k SP3
Problem I am having is getting DBCC to do any repairs. I can do a DBCC CHECKDB and of course see the errors. When I try to run any type of DBCC repair I get the old Repair statement not processed. Database needs to be in single user mode.
I have confirmed that the DB is in fact in single user mode, both in the Manager and QA, Even went so far as to start the entire server in single user mode yet still get the error.
I have two dbs that I am trying to recover and they both bark out the same errors.
Any pointers as to where I am going wrong?
Thanks,
Chris
PS Commands I am using.
use master
go
dbcc checkdb ('dbname', 'repair_rebuild')
*******************
Use master
go
sp_dboption dbname, single, true
*******************
UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='dbname'
I am using SharePoint Services 3.0 (SP1) with default configuration options, which installs the Microsoft##SSEE instance of SQL to my local C: drive.
While attempting to relocate the files to another drive, I set one of the databases (as recommended) to Single User by using the SQL Server Management Express tool.
I cannot now reset that database to Multi User, even by executing the query
I have written a VB/SQL Server based application. The application uses stored procs and the SQL Server security model to handle logins and permissions. My partner and I want to sell the applicaiton to specific businesses and offer a rental program for smaller companies that would like to use the application to connect to an internet based database. The application uses TCP/IP with a specific port number to connect to the database so it can be used in or out of LAN.
If we rent the application, maintenance is simple. I just create a user in the database with the login/password the user would like and viola they have access to the data.
When a company wants to purchase the program to use in-house, we would like to offer two alternatives: #1 a single user or #2 a multiple user SQL Server database.
My question is if a company purchases the single-user version, how can we keep them from adding users to their database. In other words, since we have no control over the database at the client's location, how can we ensure that it remains a single-user database?
this is not making a whole lot of sense to me. I am trying to do this very simple query:
SELECT ID FROM Equipment WHERE CMMS_ID=@EqNum
This is inside a cursor where I select from an oracle datasource. @EqNum is declared as an nvarchar(15), after selecting into it from the cursor I print the variable and it reads:
000000000015552
perfect. But when it executes against the above query I get no results. However, if I execute the following query manually
SELECT ID FROM Equipment WHERE CMMS_ID='000000000015552'
it returns results. Aren't these supposed to be exactly the same??? Why am I not getting results when I use the variable? Also CMMS_ID column is an nvarchar(15) column.
Is this weird or what? Is oracle really returning something different than is what is printing??
I try to attach a database mdf file to Microsoft SQL server 2014 on Amazon Elastic Computing Cloud, EC2, but fail with the following message, "User 'guest' does not have permission to run DBCC checkprimaryfile. (Microsoft SQL Server, Error: 2571)" The ID I use to REMOTE login has administrator rights and I have chosen to "run as administrator"
Dear all, to restore one perticular backup set, i've made the database to single user mode, now it is not accepting me to connect to that again... how can i bring the database to multiuser mode?
thanks in advance
Vinod Even you learn 1%, Learn it with 100% confidence.
I have a table that is made up of the sum of medical, mental health and pharmacy claims. I would like to query that to find instances when the sum of the three claims types are greater than a predetermined threshold.
For example: Patient 1 Medical = 10,000 (could be 10 records at 1,000 each) Patient 1 Mental Health = 5,000 Patient 1 Pharmacy = 15,000 Patient 2 Medical = 1,000 Patient 2 Mental Health = 0 Patient 2 Pharmacy = 500
Threshold is 25,000
If I queried the above sample table I would get one record: Patient 1 30,000 - because 10,000+5,000+15,000 = 30,000 and is greater than the threshold.
I am not sure that a having clause would work though.
This doesn't make any sense. I am trying to install SQL Server 2005 on SBS 2003 with Service Pack 1. According to Windows Update, there is nothing left for me to install. However, I am getting this error (while installing SQL Server Express and the Developer Edition):
"Your operating system does not meet Service Pack level requirements for this SQL Server release. Install the Service Pack from the Microsoft Download Center at http://go.microsoft.com/fwlink/?LinkId=50380, and then run SQL Server setup again."
When I go to the link, there is nothing there to download (it takes me to the main Microsoft download page). I meet all the requirements that I have found. What gives?
I tried to run the SQL in single user mode by executing sqlservr.exe -m. The execution starts in a DOS window but apparently stops at one point. The SQL Server will run in single user mode but when i tried to restore the master db, the restoration process stops and the SQL server stops. Is there any process I left out? I think the execution of single user mode is incomplete. Please help.
Actually, I found exactly the same problem was posted year ago, but there was no solution. Any information would be greatly appreciated.
I use SQL 6.5 and Service Pack 5a NT4 with a CLuster and Raid.
One DB (DB1) is in SIngle User and Read Only Modus. If I use the Enterprise Manager to máke a new Login and/or to change the rights of another DB, I get the message, that DB1 is in single user Mode:
Error 924: ... DB is in Single User MOde....
The same message was sent, if I use I-SQL and ask DB1 with a DBCC or SP ():
dbcc checkdb (db1): Nachr.-Nr. 924, Schweregrad 14, Status 1 Database 'DBPLW02' is already open and can only have one user at a time. DBCC execution completed. If DBCC printed error messages, see your System Administrator.
or with sp_dboption dbplw02, offline, true:
Nachr.-Nr. 5160, Schweregrad 16, Status 1 Cannot take 'dbplw02' offline because usecount=1. Nachr.-Nr. 15245, Schweregrad 16, Status 1 'DBCC DBCONTROL' error. Database not placed offline.
I canīt change the DB1 in multi user mode and I canīt find the person who has opened DB1 because I get every time this message. Technet only tells problems with the Enterprise Manager and not with EM and I-SQL!
At the moment some of the databases on my systems drop themseleves into single user mode over the weekend. Has any one expierienced this or does any one know how I could monitor it to find out how it is doing it.
Hello, I have an SMS 2.0 Site in which we ae using SQL Server 6.5. I am having major problems setting server in Single-User Moder and running automated SQL Utilities because of multiple connections at one time. Does anyone know how to FORCE SQL into Single-User Mode. Any help would be greatly appriciated. Thank You, George Aviles gaviles@na2.us.ml.com
I have been trying with no success to start my SQL Server in single user mode and login.
Here's the scenario:
I've got a one node cluster with SQL Server 2000 installed. I changed the startup parameters of SQL Server to reflect the following:
-c -f -m
I have disabled SQL Server Agent so it doesn't start when I bring SQL Server online. But when I do bring SQL Server online, I'm not able to connect at all. I keep getting the "only one administrator can log on at one time".
There is no one else trying to log on and SQL Agent is disabled.
What is the scoop? Has anyone seen this? And if so, how did you address it? I'm trying to restore master and need to get this donesooner rather than later.
Hi I have heard abt starting the SQL Server in Single User Mode,
1) Can any one tell my why would I have to do that , Can some one please give me real time examples as to why he/she started the SQL Server in Single User Mode
2) I have currently got 20 users online,howe can I start the SQL in single user mode.
I know it's completely not advised, but I have to do this: I need to make some updates to a system table. In short, I have some databases that were secondary databases in a log shipping scenario. I needed to recover those, so now they're not in STANDBY. All I want to do is reset those back to STANDBY so I don't have to completely start from scratch. One of the databases is 500GB and I do not feel like having to wait for another full restore of that database.
I have DAC enabled on the server in question. Whenever I try to connect, it says DAC is NOT enabled. Wrong!!! When I run this query: Code Snippetsp_configure 'remote admin connections', 1;GORECONFIGURE;GO The first statement runs fine. It says to RUN RECONFIGURE. I run reconfigure. I then get an error saying ad-hoc updates to system catalogs are not accepted. Huh?????? So I start the SQL Server in Single User Mode. I closed every single connection. Nothing else is trying to connect. When I try to connect to the instance via SSMS using ADMIN:<instance name>, I get an error saying I can't connect because only one adminstrator is allowed to connect while in single user mode. I do not get what the H-E-double-hockey-sticks is going on! I'm set up as a sysadmin on this box... I don't get it...
I have been trying with no success to start my SQL Server in single user mode and login.
Here's the scenario:
I've got a one node cluster with SQL Server 2000 installed. I changed the startup parameters of SQL Server to reflect the following:
-c -f -m
I have disabled SQL Server Agent so it doesn't start when I bring SQL Server online. But when I do bring SQL Server online, I'm not able to connect at all. I keep getting the "only one administrator can log on at one time".
There is no one else trying to log on and SQL Agent is disabled.
What is the scoop? Has anyone seen this? And if so, how did you address it? I'm trying to restore master and need to get this done sooner rather than later.
Ever since installing SQL service pack2 I've run into a very strange error.
Just about every day, 1 or more databases just switch themselves into Single-User Mode. Since these databases form the back-end of websites, this is obviously a serious problem. No-one can get in. I've checked out the MS knowledge base, and books online, but I can't find a thing about this behavior.
Has anybody else run into this? - Does anybody know where I could begin to look to trace this error down? Its been going on for more than a week and I'm still at square one.
Any help would be apppreciated.
------------------------ Windows NT 4 - SP6a SQL 7 - Sp2 ------------------------
Dear All, I have some user databases that go into single user mode for no apparent reason. Is there any reason that SQL Server 7 would set a database in single user mode by itself? NB They are not being set single user by anyone else here, they would not know how.
I been reading that DBCC checkdb should be run in single user mode. While microsoft says to run DBCC checkalloc in single user mode. Does running checkdb in single user mode matter, and should I be running checkdb in single user mode?
In order to automate regular maintenance on SQL Server (6.5 SP3) I'm trying to automate the process of putting the database into single user mode without having to stop/start the process.
The sp_dboption won't allow single user mode while users are still connected to the database, so I was trying the following script:
DECLARE @to_kill int
/* Declare Cursor */ DECLARE spid_cursor CURSOR FOR SELECT spid FROM sysprocesses WHERE dbid = 6
/* Open Cursor */ OPEN spid_cursor
/* Loop Through All Table Names */ FETCH NEXT FROM spid_cursor INTO @to_kill WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN KILL @to_kill END FETCH NEXT FROM spid_cursor INTO @to_kill END
This, however gives a sytax error for the KILL command (it seems that KILL will not allow a parameter to be passed). I've tried making the @to_kill variable smallint, int, and varchar without any success.
Is there any automated way way to kill user connections without cycling the server?
I have a database in development in SQL Server 6.5 that needs to be occasionally deleted and rebuilt from a script when table structures are changed. I found that when very complex queries were performed, the 2 MB default size of tempdb filled up and returned errors, so I went to the Enterprise Manager to expand tempdb, learned that I had to first expand a device to expand tempdb into, and foolishly chose to expand tempdb into the same device space used by my application, instead of into one of the system databases. Then when I tried to delete the device in preparation for its rebuild, the Enterprise Manager responded with an error message saying the device can't be deleted because it contains system tables. The answer (apparently) is to move tempdb into RAM, but when I tried to do that, I got an error message saying I couldn't unless I was in single-user mode. So I put it in single-user mode, and now I can't get to the server at all through the Enterprise Manager because an error message pops up saying "A connection could not be established to SERVER3 - [SQL Server]. Database 'tempdb' is already open and can only have one user at a time."
So now I'm really stuck. There are no other connections or users accessing the database. How can I get back in? No command-line switches seem to make any difference (-c, -f).
i have a scheduled job which shrinks the database every day once at 12 am
today the job got failed
In the view Job history its showing the below contents ------------------------------------------------------- Database 'ecatalog' is already open and can only have one user at a time. [SQLSTATE 42000] (Error 924) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528). The step failed.
And in Application Log of event viewer its showing the below contents ------------------------------------------------------------------------------ The description for Event ID ( 208 ) in Source ( SQLServerAgent$ABCSQL ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Shrink Database - ecatalog, 0xA0C4F8157A744244A61A4ECABE8C6056, Failed, 4/19/2004 12:00:03 AM, The job failed. The Job was invoked by Schedule 27 (Shrink Database - ecatalog). The last step to run was step 1 (Shrink Database - ecatalog)..
I ran the job manually it worked fine
but now when i go and see the database ecatalog in my Enterprise Manager it is showing ecatalog(Single User) What is the meaning of this, will this make any problem to my database
Hello FacultiesWhen doing recovery after loss of data files, one would like tostart SQL Server in single user mode. Is this possible?Thanks in advanceDebashish
I get an error in my ASP.Net application which states: "The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor."
I checked out the database properties, it shows that SQL is set for 'MultiUser' mode. What else do I need to check and how can I fix this?
