Running Small Business Server 2003 premium which includes SQL Server 2000. All fully patched.
Maintenance plans are new to me. As you experts know you can build a plan to check database integrity. (see report below). The process failed because, it seems, the Maintenance Plan (mp) cannot put some databases into single user mode. Why not? No one is using the databases at 3:30 am.
My question: What do you experts do?
- Do you ignore mp and use your own code to check database integrity?
- If not, how do you tell SQL Server to put certain databases into single user mode before performing the integrity check? I didnt see an option to do this when I built the plan.
- If you build you own, could you kindly share some code?
Thank you.
====================== The Maintenance report ==============
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'SBS2003SERVER\INVEST' as 'DONHOME\SQLServerAdmin' (trusted)
Starting maintenance plan 'INVEST_plan' on 12/26/2007 3:30:01 AM
[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 **
[4] Database StockData: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 33 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'INVEST_plan' on 12/26/2007 3:30:34 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Start Free Trial
