December 1, 2008 at 6:49 am
In two of our system databases , we have created Maintenance plans for each of them to back up and check integrity status . No other functionalities are done over these two databases . What happens is that from time to time these two databases are switched from multi user mode to single access mode knowing that no procedures are executed to do such step . I have monitored Application log viewer and noticed that Maintenance plan execution took place around 3:03 am and errors due to single user access were recorded around (3:10 am). After reading some articles , i noticed that setting option "repair minor problems" at Integrity tab turns mode to single mode and redirect internally it to multi mode after finishing .However, incase other DB connections started it will keep the DB in single mode . Based on my analysis i think i found the problem , but what confuses me is why such error occurs only in one server? Isn't supposed to appear on other servers if DBs are having same implementation of Maintenance plan? Shall I check integrity differences between this copy of Database on our server with other one on another server ??
December 1, 2008 at 7:10 am
Please post in the appropriate forum.
In my opinion, the 'repair minor errors' option should not be checked. Minor corruptions may indicate larger problems in the future. If they get repaired automatically, and hence go unnoticed, the first warning of a major problem coming may be when it happens.
The DB will only go into single user mode if there are corruptions, so most likely, the server that you're having the issue on is getting small corruptions in the DBs and the others aren't.
Uncheck that option (on all servers) and examine the output of the integrity check carefully. (or run checkdb manually)
If you're seeing corruptions often, check your hardware. It's usually the fault of the IO system.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2008 at 7:39 am
Thanks for your reply..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply