January 31, 2011 at 12:36 pm
I just put it to the SQL2000 forum. Actually, my enviroment is SQL 2005. Sorry for it.
Hi all,
I set up log shiping for one production server. It is running for a long time.
Today when I checked the status of one database, this database was last restored three days ago. Furthur investigation, I found one transaction log file was missing, it is not in the log shipping folder.
In primary server, backup interval is one hour. I have Transaction log backups at 1:00PM,2:00PM, 4:00PM..., but no 3:00PM. I checked the history of backup, it said it was backuped successfully.
backup start time is 2011-01-28 20:00:03.000
backup finish time is 2011-01-28 20:00:04.000
backup size is 260K.
I check the Monitor server
select * from dbo.log_shipping_monitor_history_detail
where log_time<'2011/01/29' and message like '%mydatabase%' and log_time>'2011/01/28', I found
Log_time :2011-01-28 15:04:07.847
Message: Could not copy log backup file '\\Myserver\logshipping\mydatabase_20110128200010.trn'. The copy operation will be re-attempted on the next run of the Copy job. (The process cannot access the file '\\Myserver\logshipping\mydatabase_20110128200010.trn' because it is being used by another process.)
I could not find which process was using the log file at that time. And also I don't know the reason why the log file was removed from primary server even though the log file older than it are still there.
I know I have to reset the log shipping for this database. I still want to find the reason. Can somebody help this?
Thanks,
January 31, 2011 at 1:51 pm
This is usually caused by server times being slightly off or a huge transaction log taking time backing up.
January 31, 2011 at 2:10 pm
has someone taken a full backup of this database by any chance?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 1, 2011 at 9:12 am
AlexSQLForums (1/31/2011)
This is usually caused by server times being slightly off or a huge transaction log taking time backing up.
This log backup is 260k and took less than 10 seconds.
backup in primary and copy in secondary has 4 minutes gap.
The time difference between two servers is less than 50 milliseconds.
February 1, 2011 at 9:14 am
Perry Whittle (1/31/2011)
has someone taken a full backup of this database by any chance?
No, I have checked backup logs, no full backup happened.
February 2, 2011 at 6:18 am
unless you have a windows file system auditing tool in place (rare) you have absolutely no way of knowing how/why/when this happened. Sorry.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 2, 2011 at 6:18 am
Oh, and may I suggest you set up some task to check this system more frequently than every 3 days??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 2, 2011 at 6:19 am
one last thing - did you check the Recycle bin on the backup server just in case?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 3, 2011 at 11:49 am
TheSQLGuru (2/2/2011)
Oh, and may I suggest you set up some task to check this system more frequently than every 3 days??
I am sorry to reply late.
I am afraid even though I check more often, I still need to reset up log shipping for the database if the COPY job delete the transaction log. From the symptom, the Transaction log backups before the missing one are still in the log shipping folder.
February 3, 2011 at 11:53 am
TheSQLGuru (2/2/2011)
one last thing - did you check the Recycle bin on the backup server just in case?
Sorry to reply it late.
I did check Recycle bin both on Primary and Secondary server. Nothing is there. And also there is nobody to login to Logshipping server in that period except SQL service account.
February 20, 2011 at 11:31 pm
This is the problm when some body had restarted ur secondary server, becoz for the duration of restart the job will not so the copy job will not run which skips that transaction. so check the error log file for las restart of ur server.
I think this may be the problem.
Kranthi
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply