July 24, 2003 at 7:29 am
1. Integrity Checks
I found that my integrity checks were failing because of the bug mentioned in KB Q290622 when system databases included. The KB article does not mention any workaround other than not running integrity checks against the system databases.
Should I be concerned that I can not check the integrity of master and msdb? Or should I be finding another way of doing this?
2. Simple / Full backups
Saw in another thread recommendation to use simple rather than full recover model if log files get very large.
Do I understand correctly that if I take a database backup every night and a transaction log backup every hour then I would have to use full recovery model because simple only recovers from the last full backup.
July 24, 2003 at 6:00 pm
I think what it says is that you shouldnt check the repair problems option. No reason not to run DBCC anytime, it doesn't require single user mode unless you're fixing something. If you want the option checked, just set up two plans, one for system db's, one for everything else.
Large log files aren't a good reason to change your recovery model, set the recovery model that meets your needs. There are cases where simple is enough and/or appropriate, but it should be the exception rather than the rule. Yes, if you use simple you give up the ability to restore changes since the last full backup.
Andy
July 25, 2003 at 4:10 am
Thank you for your reply. Log files clear.
regarding the repair option. I have now set up two maintenance plans:
System databases - repair unchecked
User databases - repair checked
I still sometimes get an integrity check failure on the user databases, see error below:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'Kb2'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[2] Database Kb2: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
July 25, 2003 at 6:05 am
That will happen if anything/anyone is holding a connection. Could be a user session left open, replication log reader, anything. Only way to guarantee is to run a job to kill all the connections before running it and make sure they don't reconnect. In my opinion not worth the effort, I'd rather just get notified when it finds an error and deal with the repair myself. Errors are so infrequent this strategy works well.
Andy
July 25, 2003 at 6:40 am
Sorry if I am being thick but could you tell me what strategy you would follow.
The error message does not tell me what exactly the problem is. So how do I know what to fix?
Do I just run the job with the repair option checked. then if it fails put the database into single user mode. Then run the job manually?
Or am I missing someother log which will tell me what the error is?
I also tried running DBCC CHECKDB from the Query Analyzer but no errors were reported!
July 25, 2003 at 5:51 pm
The error is only because single user mode cant be set, not because anything is wrong with the db. In 3 years I've seen one error on a DBCC. My advice is uncheck the button to repair errors, have it just report them. If it happens, fix manually. Nothing more to it.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply