Firstly I find this forum to be an excellent source of information. Unfortunately I was unable to find an answer to the cause of my problem.
For some unknown reason the .trn files that should be copied over to the secondary server stops overnight.
2 Windows 2003 servers (primary and secondary)
2 SQL ent Ed.
both servers on a w2k domain
SQL agent running as administrator on domain account
log shipping setup at about 5pm, .trn files copied over to secondary up until 1:45am the next day (everything working fine as backup and restore works)
copy/lod frequency is 15 mins
leave secondary in standby mode and terminate users set
On Secondary server:
Both log shipping copying and restore SQL agent jobs says the job was sucessful
On Primary server:
Transaction log backup Job for DB maintenance plan "DB Maintenance Plan1" last run suceeded
Log shipping alert Job- Backup Succeeded
Log shipping alert Job- Restore FAILED*
Monitoring server is the primary server**
* It always says that it failed from the start but I know that the secondary is being updated and in sync until sometime in the night (1:45am last time)
** Says it's out of sync from the begining. I know it's not true in the beginning as I make a change on the primary DB and xx mins later, I can query the secondary DB and see that the change as taken place.
If I change the monitoring server to the secondary server, the log shipping alert for backup fails and restore alert is fine (switches around)???
When I came back to the office in the morning I looked at the shared folder on the secondary server and noticed that the .trn files stoped coming in at 1:45am. (they are created fine on the primary).
The secondary database was not being updated. (confirmed)
I manually copied all the missing .trn files over to the secondary from the primary and waited 15+ minutes. The secondary DB was updated sucessfully.
Currently I must manually copy the files over to keep the DB's in sync. Why does SQL agent stop coping the files over after a few hours???
Thanks in advance for any hints or suggestions you may have. If you need more info, please let me know. This problem is driving me crazy.