Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
 Posted - 2008-03-28 : 02:29:33
|
Tonight's mystery... We rebooted one of our SQL Servers (SQL 2000, SP3) this evening and when it came back up, one of the application databases was in Single User mode (the others were all normal). I was not present for the reboot, although I had given my blessing for it, so I cannot state what the precise condition of the database was prior to reboot, but this I do know:- The application which uses that database was functioning fine earlier in the evening. It is a high use, mission critical application, so I would have known if anyone was having trouble getting in.
- The reboot occurred during a normally inactive period.
- The only possible concerns that I see in the SQL Server logs are listed below:
- Two occurrences of ERROR 14152, Severity 10, State 1 (Apparently related to Replication. We do Replication on a couple of databases here but not the one that went into Single User mode. Also, Replication Monitor shows both publications working fine.)
- One occurrence of ERROR 1073759806, Severity 1, State 0 (Apparently related to SQL Mail being started)
- One occurrence of ERROR 1073759778, Severity 1, State 0 (Apparently related to SQL Mail being started)
- The last thing that happened in the logs before I was notified of the problem is that SQL Reporting Services set DBCC TRACEON 3604, then set it off within 3/10s of a second and did that again a couple of seconds later.
So, the error messages in the log appear to be red herrings. I was able to set the database back into MULTI_USER mode and everything appears to be working fine, but I hate this type of unexplained mystery. Anybody got any ideas of other things to look at that might explain why this database suddenly got put into Single User mode all by itself?Thanks in advance!---------------------------EmeraldCityDomains.com |
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2008-03-28 : 15:04:37
|
| My sql server freaked out just in the past day or two. After the 4th BSOD and me subsequently fixing the issue, sql started doing its "own thing" checking out databases and all sorts of stuff I didnt understand. But I disconnected the network just to make sure it was not being hammared by people who were ignoring my instruction to leave it along until i was finished fixing it.Happily sql figured itself out, but perhaps SQL did it on its own in order to perform a check of sometype. (which it couldnt run due to the mission critical people harassing it)I apologize for my terrible terrible SQL'less guess. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-28 : 23:12:16
|
| Any unusual message before rebooting? |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2008-03-31 : 18:42:08
|
quote:
Originally posted by rmiao
Any unusual message before rebooting?
Nope, nothing unusual in the logs before or after the reboot.Can anyone else confirm that they have had mysterious, apparently automatic setting of a database into Singler User mode?---------------------------EmeraldCityDomains.com |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-31 : 23:06:29
|
| Ensure sql service account has full permission on those db files. |
|
|
|
arunsqladmin
Yak Posting Veteran
74 Posts |
Posted - 2008-04-01 : 06:40:02
|
| u get this problem if u have maintance plan set with an option called repair if any minor problems which would make the db in single user mode. ....check out this link..... u will get to knowhttp://support.microsoft.com/kb/259551 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2008-04-02 : 14:49:07
|
quote:
Originally posted by arunsqladmin
u get this problem if u have maintance plan set with an option called repair if any minor problems which would make the db in single user mode. ....check out this link..... u will get to knowhttp://support.microsoft.com/kb/259551
This is close enough to relieve about half of my headache. Unfortunately that article indicates that it was fixed in SP3 of SQL 7.0, and this database is on SQL 2000 (in SQL 2000 compatibility mode).However, we do have maintenance plans with that option selected, and evne though MS claims it is fixed and I can find no other MS article countering that, I am a little more comfortable. In a funny twist of fate, just days before that problem arose I instructed our part-time DBA to get rid of all uses of Maintenance Plans because they are too much of a black box and to replace them with Jobs with explicit commands so that we know EXACTLY what is going on. He has not yet completed that task, but it is now an even higher priority.---------------------------EmeraldCityDomains.com |
|
|
|
|
|
|
|
|
One DB in Single User Mode after server reboot