July 24, 2003 at 7:00 am
hi, could someone please help me out in following:
we have 10 databases in our SQL server 2000.
because of some reason one database called Development change itself to single user mode. i close down the Enterprise Manager and restart again, after couple of try Development database back to normal but an hour later database change itself to single mode again, and it was keep doing until I did a roll back by using backup files then its ok.
does any one has any idea why that was happen and how to fix it or doing roll back is the only option I have??
Thank you
Khalid
July 24, 2003 at 7:10 am
Hi Khalid,
You can do the following to turn single user mode on/off
USE master
EXEC sp_dboption 'yourdb', 'single user', 'TRUE/FALSE'
To why this is automatically happening I'm not sure, maybe you could post some of the log details.
Laters
*I didn't do anything it just got complicated*
"I didn't do anything it just got complicated" - M Edwards
July 24, 2003 at 8:53 am
Users with DBO rights/Sysadmin rights can put the DB into single user mode. Check with other dbo's. Alternatively run the profile and check next time your db turns in to singleuser mode. If you are running sql 7.0 use the following one
Exec master..xp_sqltrace Trace, @Fulltext = 1, @EventFilter = 115
Shas3
July 25, 2003 at 3:13 am
Do you have any database maintenance plans set up that check database integrity? I can't remember the exact details, but there may be a problem here where this plan puts a database into single user mode?
Sorry about the vagueness!!
Andy
July 25, 2003 at 5:55 am
To followup Andy's comment;
If in your database maintenance plan, you check "Check Database Integrity", and also check "Attempt to repair minor problems", the database will set into single-user mode.
However, if there is something else that attempts to use the database in between the time the maintplan sets to single-user mode, and when the maintplan executes the command to fix (DBCC something), then the DBCC fails, and the job step fails, and the database does not get reset back from single-user mode.
Mark
Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply