September 24, 2010 at 5:07 am
Hi all,
I have a maintenacne plan that is doing backups of the logs (TRN) of the user databases every 30 minutes from 00:00:00 to 23:59:59 (24 hours).
The maintenance plan performs the following steps in this order:
1) Make a backup of the databases logs.
2) Compress all the logs (with 7z compressor) in the same location as the copies of log (using 'xp_cmdshell' to invoke the compressor program).
3) Move all compressed files to another drive with the procedure 'xp_cmdshell' running the next command:
move "C:\SQLLogBackup\*.trn.7z" "\\SRV1\backups" 1>> c:\stdout.txt 2>> c:\stderr.txt "
In last days the maintenance job is failing three times in a day and gives me the following error in job history:
"Error executing move "C:\SQLLogBackup\*.trn.7z" "\\SRV1\backups" 1>> c:\stdout.txt 2>> c:\stderr.txt".
Also i have reviewed the log 'sdterr.txt' generated by the 'move' command, sql server logs, ... but can not identify what is the problem.
Errors always occur the three times i commented, just near the time also are doing the full database (user and systema database) backups.
How I can know what's the problem? Why the plan works fine during all day and only fails in those three executions?
Thanks in advance,
September 28, 2010 at 2:04 am
The copy process (move) is a slow process.
Is possible that maintenance plan is failing for a timeout reason? Have the maintenance plain any timeout?
Many thanks in advance.
September 29, 2010 at 1:46 am
Hi,
I can see in the SQL Server log file the next error message:
"...
Execute TSQL on Local server connection
Execution time out: 0
Task start: 2010-09-29T00:00:21.
Task end: 2010-09-29T00:00:21.
Failed:(-1073548784) Executing the query
EXEC @result = xp_cmdshell 'move "C:\SQLLogBackup\*.trn.7z" "\\SRV1\backups" 1>> c:\stdout.txt 2>> c:\stderr.txt'
...
"
I have been looking in internet and it is very general error message.
Somebody can help me why i can identify the exact error to solve it? Comment that my SQL Server edition is SQL Server 2008 SP1. I don´t know if is a bug o not ...
Many thanks in advance.
September 30, 2010 at 2:58 am
Hi,
I've identified the problem but i dont know how solve it.
If I open two MS-DOS window and run parallely on each of them the next commmands:
move "SQLLogBackup\F1.trn.7z" "\\SRV1\backups"
move "SQLLogBackup\F2.trn.7z" "\\SRV1\backups"
The files are moving correctly.
But on the other hands if i move this files from two SQL Server jobs running the following commands in each of them:
xp_cmdshell 'move "SQLLogBackup\F1.trn.7z" "\\SRV1\backups" '
xp_cmdshell 'move "SQLLogBackup\F2.trn.7z" "\\SRV1\backups" '
Only one of this jobs run correctly (the firts that begin the execution) because the other is failing.
Why is this happening? I Can't have two jobs working in parallel and running 'xp_cmdshell' move ... ' command? This is a SQL Server bug? Someone could tell me how I could solve this issue?
Thank you very much.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply