gotaquestion
asked on
single user integrity
In sql 7.0 I was able to run integrity checks no problem. Now with sql 2000 they fail and tell me that
the database has to be in single user mode. We have clients all around the world. They are always
using our database. Isn't there some way to run an integrity check with out taking down the application
which is what I'd be doing if I put the database in single user mode? I just want to run an automatic
integrity check every coulple days without taking down the database. If there is no other way, then
how would I do that? The wizard (which is what I use) doesn't provide for this option.
the database has to be in single user mode. We have clients all around the world. They are always
using our database. Isn't there some way to run an integrity check with out taking down the application
which is what I'd be doing if I put the database in single user mode? I just want to run an automatic
integrity check every coulple days without taking down the database. If there is no other way, then
how would I do that? The wizard (which is what I use) doesn't provide for this option.
ASKER
s-
i'll give you points, but I have a questions. To do your solution do you still have to close down the databases?
i'll give you points, but I have a questions. To do your solution do you still have to close down the databases?
I don't close down the databases, it is done internally by MSSQL. It only tries to close the databases down when the attempt to repair minor errors is checked.
Don't quote me on this, it was a while back that I did the reading on this bug. You can find the explanation on Microsoft's website.
Don't quote me on this, it was a while back that I did the reading on this bug. You can find the explanation on Microsoft's website.
ASKER
so when it tries to repair minor errors, even with your 2 step method, it will still fail because there will be users connected to the db and it will not be able get it into single user mode. Right?
You've got it. That is why I have to schedule to the best of my ability for times when no one is on. It is my theory with my customers that minor errors rarely occur, in fact I have not seen any, so I'm not worried if I see an occasional failed integrity check. If they fail regularly, I try to find another time to schedule it.
Actually, it tries to go into single user mode even when it doesn't have any errors to repair from what I understand, just the mere fact that it might have to repair minor errors causes it to try to go into single user mode.
Actually, it tries to go into single user mode even when it doesn't have any errors to repair from what I understand, just the mere fact that it might have to repair minor errors causes it to try to go into single user mode.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks! Hope we can collaborate again sometime! Perhaps next time you can answer my question.
The solution that I came up with is to do 2 different Integrity checks:
1. Does msdb and master with WITH NO attempts to repair minor errors.
2. Does all of my databases WITH attempts to repair minor errors.
I try to find the most likely time that the customer will not be logged in and schedule for that time.