SQL Server Maintenance Plans

Hi

This is a little off the subject, but I hope that there will be many of you using SQL Server who may be able to help.

We are mainly an Oracle site, but have installed SQL Server 2000 to handle inventory transactions coming from scanners into OneWorld via DC Link. We have set up a maintenance plan to run nightly which should do some DBCC checks then back up the databases. The backups are failing with the following log being created:

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'RFSCANNERNT' as 'ONEWORLD\Administrator' (trusted)
Starting maintenance plan 'DC Link Maintenance' on 2/09/2002 12:00:02 a.m.
[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 'dcLINK4.1'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database dcLINK4.1: 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 dcLINK4.1: Transaction Log Backup...

The backup was not performed since data verification errors were found.

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

End of maintenance plan 'DC Link Maintenance' on 2/09/2002 12:00:02 a.m.
SQLMAINT.EXE Process Exit Code: 1 (Failed)

In the maintenance plan, we have set it up as follows:

On Sundays at 1.00am - Reorganise data and index pages (change free space per page percentage to 10%). Remove unused space from database files (shrink database when it grows beyond 50Mb. Amount of free space to remain after shrink: 10%)

Integrity: Occurs Sunday at 12:00am Check database integrity with "attempt to repair any minor problems". Perform these tasks before backing up database or transaction log

Complete backup on Sunday at 2.00am (to disk)

Transaction log backup every m,t,w,t,f,s at 12.00am (to disk)


The log suggests that everything is failing because the database needs to be in single user mode. I would have thought that the maintenance plan would have booted everybody out and set it in single user mode if it needed to. I cannot find a setting to tell the plan to do this.

I would appreciate it if somebody could throw any light on where we shoudl look. Many thanks.

I think the problem is the result of you setting the 'Attempt to fix minor errors' flag. The database will need to be in single user mode to acheive this and obviuosly you are not in Single User Mode. I am sure that this will be the problem Uncheck the flag and it will run ok.

Thanks, Antipodes

We, turned off the flag to do the checks before the backup as you suggested.
What was happening was that if the checks failed (because it was not in
single user mode), then the job aborted before doing the backup. We managed
to get a clean backup last night and successful transaction log backups
today.

I still think we are going to have a problem if we do weekly DBCC checks. As
I said in my email, I would have thought that SQL Server would have been
smart enough to kill active processes and put the database into single user
mode in order to do the DBCC. How do we achieve this via a script?

Thanks for the help.

Regards

Marty Fleming
Business Analyst
Richmond Limited

Phone: +64 +6 8786464 Ext 8168
Fax : +64 +6 8780959
Email: mailto:[email protected]

OneWorld: Xe SP16.1
Database: Oracle 8i
Enterprise Server: Compaq Proliant 8500R W2K






OneWorld: Xe SP16.1
Database: Oracle 8i
Enterprise Server: Compaq Proliant 8500R W2K

Hi

Simple enough in theory. You need to get a statement 'ALTER DATABASE <dbname> SET SINGLE_USER' inserted into your job which you have generated in the SQL AGent \ JObs section. I think this will be in the Integrity Checks Job.

Put the above statement in just before your first auto-generated statement.

Best of luck

Ed

Bypass the SQL Maintenance plan wizard and create a new job with the following steps in the SQL Jobs screen
Step 1. Alter database <databasename> set SINGLE_USER
Step 2. DBCC CHECK ('databasename') REPAIR_FAST
Step 3 Alter Database <databasename> set MULTI_USER

or something similar - not sure on the exact syntax, but it should be like this. You will then need to assign it a schedule and it should be ok

HTH