mjf
Well Known Member
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.
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.