November 27, 2006 at 10:45 pm
Hi,
I have logshipping set up and running, but my Log Shipping copy jobs fail every time with this error message:
Executed as user: mydomain\servsqlacct. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
After I digged in msdb database tables, I found some more information:
last_file message
--------------------------- ---------------------------------------------------------------------------
first_file_000000000000.trn The process cannot access the file because it is being used by another process.
My restore jobs are fine however. I did check the version of the database on the destination server and found it is up to date. I think this is because I copied and restored the database manually (due to slow link) and this caused some file history mix up. This does not cause any problems, but the fact that all my copy jobs are in red. Just wondering what logshipping tables should I look at to fix the problem?
Thanks.
November 28, 2006 at 7:57 am
The file on the primary data server is being written or in use by another program. I would look and see if the tran log backup job is still writing or AV or something else has the file in use.
November 28, 2006 at 2:50 pm
first_file_000000000000.trn
There is no such file anywhere. This name is used when copy job has nothing to copy, but it does, as all backup jobs are running fine as well as all restore jobs do. All the databases are in sync. The copy jobs all fail, but they still work, as files are being copied and restored. I would not even bother about it, but I hate having some jobs with fail status.
November 28, 2006 at 3:52 pm
quote: I think this is because I copied and restored the database manually
If you did that, the database is in a restored state and cannot accept log shipping.
I believe the database must be in loading or standby state for log shipping to work.
(Disclaimer: I could be wrong.)
-SQLBill
November 28, 2006 at 4:09 pm
Of course I restored it in standby state and then configured the log shipping to use existing database without reinitialization. The log shipping definitely works as all the databases are up to date.
November 29, 2006 at 7:27 am
When the log shipping job was built it would of comaplined should the db not be in stand by mode. The "first_file_000000000000.trn" file is just a place holder in the job tables and as it states is under column last_file. It is trying to copy the files on the primary side and will update the last_file column to this file name when complete.
Is this issue still occuring? On the primary side have transaction logs backups taken place and are they present on the file system?
November 29, 2006 at 11:20 am
It could be that you have network latency, so the time it takes to copy a tranlog file could exceed the interval that you have set for the copies.
Try increasing the interval. You don't need to change the interval for tranlog dumps on the primary, as long as you are happy with it, or with the restore (if there's nothing to restore, then nothing will fail). The copy will "catch up" each time it is executed if there are one or more .trn's to pull.
Regards, Melissa
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply