July 29, 2003 at 7:51 am
one of my databases change itself to single user like 3 or 4 times perday, nobody touch it, I don't change but it stop working because goes to Single User and the only way to fix it is restarting the server...
any ideas????
July 29, 2003 at 7:53 am
Are you getting any error when run SQL to make it 'single user' FALSE OR Changing from EM??
.
July 29, 2003 at 8:02 am
nope...no erros just invalid user sa
July 29, 2003 at 8:03 am
NelsonVere : What version of SQL and OS are you running?!
.
July 29, 2003 at 8:04 am
Yes I have that fixing some erros I will take a look at it...thanks
July 29, 2003 at 8:06 am
I have SQL 2000 with SP2
July 29, 2003 at 8:08 am
quote:
nope...no erros just invalid user sa
what do you mean by 'invalid user sa' ???
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 29, 2003 at 8:12 am
Did you check if the server is in Windows Authentication Mode?! If mixed, you could try to get in with Win Auth mode.
.
July 29, 2003 at 8:15 am
All,
I also have similar problem on one of my server running SQL Server 7.0 SP3 on Windows NT 4.0 Server Enterprise Edition (with 2.5GB RAM, 4 Proc). Sometimes during maintenance this used get changed to Single User. I used to change this back thru EM. I was thinking that this is because of the "FIX MINOR ERRORS" check mark in the Database Maintenance Plan.
Also when I checked the SQL Server Error Log, I could see DBCC running few times in a day... (I am dumping TLOGS hourly) Is there any possibility that DBCC is trying to fix some minor errors, and SQL Server changing to Single User Mode & not changing it back?! Just a Thought....
Please Help!!!! Thanks in Advance...
.
July 30, 2003 at 6:46 am
quote:
...Is there any possibility that DBCC is trying to fix some minor errors, and SQL Server changing to Single User Mode & not changing it back?! Just a Thought....
That is EXACTLY what is happening. When you check "Attempt to repair minor problems" under the Integrity tab, the maint plan job will try to change the database in single-user mode before the DBCC command. If it cannot change to single-user mode (because another user/process is logged in), the job fails. However, if it can change to single-user, but someone else logs in before the DBCC command starts, then the job fails, and the database does not change back from single-user mode.
Recommendation: Do not select "Attempt to repair minor problems" unless you are absolutely sure no one else, and no other process, will use the database at that time. Perhaps middle of the nite, before backups might be good. If database is used 24x7, do not use that feature at all.
Mark
Mark
July 31, 2003 at 1:23 am
Excuse me for buttin in, but:
"However, if it can change to single-user, but someone else logs in before the DBCC command starts, then the job fails, and the database does not change back from single-user mode."
How could somebody else log in after the DB was switched to single-user?
Mvg, MvG.
Mvg, MvG.
August 1, 2003 at 11:24 am
martinvg,
Whats happening in my case is, the processes which are coming authomatically (form applications) into the database are logging in. These processed are not supposed to come into the databse that time. But sometimes some from application support logs into the application --> connetion esatablishes to the database --> Job Fails.
.
August 1, 2003 at 11:31 am
mharr,
If I do uncheck the "Attempt to repair minor problems", can we run any command in a batch mode to see if there are any errors in the databases ?!
.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply