IBM Support

DP SQL schedule backup fails with Return code is: 1

Technote (troubleshooting)


Problem(Abstract)

A Data Protection for SQL full backup is initiated from the command line and it works fine but the *.cmd file when run from the scheduler causes the schedule to fail with return code 1.

Symptom

Backup fails with the following:


Finished command. Return code is: 1
ANS1909E The scheduled command failed
ANS1512E Scheduled event 'MYSCHED' failed. Return code =1
Results sent to server for scheduled event 'MYSCHED'


Cause

More then one scheduler is using the same dsmsched.log.


Diagnosing the problem

To find out more about the cause of return code 1, it is necessary to start the client scheduler in the foreground. To start the scheduler in the foreground, change directory to :


c:\program files\tivoli\tsm\baclient

From this directory, run the following command :

dsmc sched -optfile=<path to dsm.opt used by scheduler service>

For example :

dsmc sched -optfile=c:\progra~1\tivoli\tsm\tdpsql\dsm.opt

The messages in the foreground may show the following messages :

Executing scheduled command now.
Executing Operating System command or script:
c:\progra~1\tivoli\tsm\tdpsql\sqlfull.cmd
The process cannot access the file because it is being used by another process.
The process cannot access the file because it is being used by another process.
IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server
Version 5, Release 2,Level 1.04
6/08/2006 10:27:24 Total SQL backups selected: 5
06/08/2006 10:27:24 Total SQL backups attempted: 5
06/08/2006 10:27:24 Total SQL backups completed: 5
06/08/2006 10:27:24 Total SQL backups excluded: 0
06/08/2006 10:27:24 Total SQL backups inactivated: 0
06/08/2006 10:27:24 Throughput rate: 41,984.82 Kb/Sec
06/08/2006 10:27:24 Total bytes transferred: 1,031,904,944
06/08/2006 10:27:24 Elapsed processing time: 24.00 Secs
The process cannot access the file because it is being used by another process.
Finished command. Return code is: 1
ANS1909E The scheduled command failed
ANS1512E Scheduled event 'MYSCHED' failed. Return code =1
Results sent to server for scheduled event 'MYSCHED'

The above shows that the MYSCHED schedule failed because the following error was received 3 times in the foreground :

The process cannot access the file because it is being used by another process.

It is then necessary to examine the command file executed by the schedule. In this case, this is the "c:\progra~1\tivoli\tsm\tdpsql\sqlfull.cmd". Following this example, this file showed :

set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql
C:
cd %sql_dir%
date < NUL >> %sql_dir%\sqlsched.log
time < NUL >> %sql_dir%\sqlsched.log
%sql_dir%\tdpsqlc backup * full /sqlserver=MYSERVER
/tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >>
%sql_dir%\sqlsched.log

The above shows that commands outputs are redirected to %sql_dir%\sqlsched.log.
This is done 3 times in the above command file, which corresponds to the error message being issued 3 times as well in the scheduler foreground.
This shows that the %sql_dir%\sqlsched.log file is already in use.
Use the "dsmcutil query /name:"DP scheduler service" to query the options used by the Data Protection scheduler and verify the name of the schedule log used by the scheduler.
For example :

>dsmcutil query /name:"TSM Scheduler"

TSM Windows NT Client Service Configuration Utility
Command Line Interface - Version 5, Release 3, Level 4.0
(C) Copyright IBM Corporation, 1990, 2006, All Rights Reserved.
Last Updated Apr 19 2006
TSM Api Verison 5.3.4

Command: Query TSM Client Service Parameters
Machine: xxx(Local Machine)

Connecting to service 'DP scheduler service' ...

Service Configuration/Status:

Service Name : DP scheduler service
...
TSM Client Service Registry Settings:
...
Schedule Log = C:\Progra~1\Tivoli\TSM\TDPSql\sqlsched.log

In the above example, the file used for the schedule log for the scheduler service is the same file used by the batch file (sqlfull.cmd) to redirect the output which is what caused the error.


Resolving the problem

To solve this, either update the scheduler service and use a unique file name for the Schedule Log or update the sqlfull.cmd command file and use a unique file name when redirecting output of commands.

For example, in the above example, edit the sqlfull.cmd file and modify
%sql_dir%\sqlsched.log
to
%sql_dir%\sqlcmd.log
This should solve the error and the schedule should succeed.

If the schedule failure is not caused by this type of error, review technote 1284034 for instructions on further diagnosing the error.

Document information

More support for: Tivoli Storage Manager for Databases
Data Protection for MS SQL

Software version: Version Independent

Operating system(s): Windows

Reference #: 1239995

Modified date: 20 July 2011