Database Integrity Issues - Single User Mode

[adam3291]

Hi All,

I get errors when I try to run Integrity checks against my system DB's (master,model,msdc). The error states the process can not be ran until the DB's are in single user mode. Anyone have any ideas about this??? I am doing this process thru a DB Maintenance task.

Thanks!

 

[SheetsERR]

Adam,

A db shouldn't have to be in Single User to run DBCC's. Open Query Analyzer and run...

DBCC CHECKDB ('database name')
or
DBCC NEWALLOC ('database name')
or similar command

See what happens.

SheetsERR

 

[adam3291]

DBCC's seem to run fine thru the Analyzer. I've included the error message from my log.

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'WEBTREND' as 'NTDOMAIN\ntadmin' (trusted)
Starting maintenance plan 'DB Maintenance Plan1' on 7/10/2002 3:01:54 PM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC SQL Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database 'MASTER'.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database master: 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.
** Execution Time: 0 hrs, 0 mins, 1 secs **

[2] Database model: Check Data and Index Linkage...

** Execution Time: 0 hrs, 0 mins, 1 secs **

[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 'msdb'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[3] Database msdb: 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.
** Execution Time: 0 hrs, 0 mins, 1 secs **

Deleting old text reports... 0 file(s) deleted.

End of maintenance plan 'DB Maintenance Plan1' on 7/10/2002 3:01:55 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)

 

[SheetsERR]

Adam,

You might want to check SQL Server Books Online. In versions earlier than 2000, I think you have to ensure that the db is in Single User Mode prior to running integrity checks. However, in SQL Server 2000, you should be able to run the commands while other users are accessing the database.

What exactly is your plan set to do?

SheetsERR

 

[adam3291]

I'm definitely going to check there. This is a SQL 2000 (upgrade from 7.0) server running on Windows 2000. All my plan does is integritity checks on the system DB's. I'll keep digging.

 

[SheetsERR]

Was your plan in place before the upgrade? I'm not sure if those will even carry over. Just a thought...

 

[SheetsERR]

Adam,

I created a plan and ran it several times. Each time, I set it to run checks on master, msdb and a user database. The checks ran without a hitch on the user database each time.

I ran the thing twice with the Repair option selected. Both times, the plan did not run checks on master and msdb -same Single User Mode error. The time I ran checks without the Repair option selected, the job completed for all three databases. I'm pretty certain that selecting the Repair option is a kin to doing a DBCC CHECKDB WITH_REPAIR, which, I believe, requires the database to be in Single User Mode.

The Database Maintenance Wizard is supposed to put the db's in Single User Mode. I guess it trips up on some of the system databases. Come to think of it, can you even put master and msdb in Single User Mode? How would the Maintenance Plan even run if the msdb was in Single User Mode?

Let me know what you think!
SheetsERR

 

[adam3291]

Hey,
I do have repair mode "ON". I will definitely uncheck it tomorrow and see what happens. I don't think you can put msdb in Single User mode, model you can. I'll also compare this to my 7.0 DB's and see if repair is even an option when doing system db maintenance. This may even be a glitch... GREAT WORK!!! I contact you tomorrow. I'm also going to search microsoft.com for this....

thanks.

 

[SheetsERR]

Let me know, Adam. I'm very interested in the outcome.

Thanks,
SheetsERR

 

[adam3291]

Well, I removed the repair option on the system db's and it finished normally. Go figure? I also searched Microsoft last night and found no mention of this. My MSQL is on SP2 as is my W2K server. Not sure if this is a bug or what...

 

[SheetsERR]

Adam,

So, I guess it's best that you create a separate plan for your user db's in order to utilize the Repair option.

I've heard that the Maintenance Wizard "doesn't always work". What that means, I don't know. I didn't get specifics.

SheetsERR