Does LSRestore should delete old TRN files?
-
Hello all,
I'm setting Log Shipping on MSSQL 2005 to one of my customers.
Everything is OK the jobs are running.
The Backup job is running and copy the trn file to the secondary server (to the share location).
The Copy job is running and copy the trn file from the share to the DB LOG directory.
The Restore job is running and successfully finished.
I can see that the Backup or the Copy job are deleting the old TRN files from the share location, but the old copied TRN files on the DB LOG directory does not. they still exist even after a week.
In addition after the LSRestore job is running I cannot see any changes in the time stamp of the DB.LDF or DB.MDF files.
I created a test DB on the primary server backup it, restore with norecovery on the secondary server and then setup a Log shipping.
I ran the backup, copy and restore job manually and then I manually delete the TRN file from the DB LOG directory. After that I ran "RESTORE DATABASE <DBNAME> WITH RECOVERY;" and check if the data I entered before log shipping jobs ran exits and they are exits.
So.... What's going on? Should I delete the TRN files manually on the DB LOG directory? What the LSRestore jobs update?
Thanks,
Miki
-
I created a test DB on the primary server backup it, restore with norecovery on the secondary server and then setup a Log shipping.
I ran the backup, copy and restore job manually and then I manually delete the TRN file from the DB LOG directory. After that I ran "RESTORE DATABASE <DBNAME> WITH RECOVERY;" and check if the data I entered before log shipping jobs ran exits and they are exits.
Could you please clarify what you meant by this. I don't really understand what you are trying to say. Did you delete some data and then restore the log?
-
Thanks for your reply.
I'll repeat all 🙂
I already have several Databases on the primary server.
I set up a Log Shiiping to those Databases. for each database there is three jobs, right. When I setup the log shipping on the primary server I configure it to delete files that are older than 72 hours (default).
The log shipping jobs are running well. the TRN files is created on the share directory on the secondary server (by LSBackup). Then the TRN files copied from the shared location to the database log location (by LSCopy). Then the LSRestore is running and successfully finished.
The files that was copied from the shared location to the Log locaction is never delete as I thought it should be. The old TRN files on the shared location is deleted as I think it should be.
My question is: Does the files on the database log directory should be deleted? if so why it is not?
In addition after the LSrestore is running I see that the time stamp of the LDF files or MDF is changed. It should change?
So... I created a test env.
1. I created a test database on the primary server.
2. Create a backup of this database
3. On the secondary server I restore the database using the backup from step 2 with NORECOVERY option.
4. On the primary server I setup a log shipping to this test database which copy the trn files to a shared location on the secondary server.
Then I made some changed to some table on the primary server.
5. I manually ran the LRBackup job on the primary server and it created a trn files on the shared location.
6. on the secondary server I ran the LSCopy job and the trn file was copied from the shared to the database log directory.
7. I manually ran the LSRestore job on the secondary server and the job successfully finished. the time stamp of the LDF and MDF files didn't changed.
8. I manually deleted the trn file(s) from the database log location (of the test database on the secondary server).
9. I ran "RESTORE DATABASE test WITH RECOVERY;" on the secondary server and the changed data from step 4 exist on the table in the secondary server.
I'm not MSSQL GURU, I prefer Linux 🙂 http://www.jaya.co.il
Please advise.
Thanks,
Miki
-
Ok, my guess will be that your log shipping is working fine. Maybe your files are not being deleted because the account you use for logshipping does not have enough rights to delete files on the share. When you do it manually you are using your account, but when log shipping is doing it, it is using the sql agent account. Make sure your sql agent account has enough rights.
-
Hi again,
Thanks for your reply.
The issue is that the logs are deleted from the shared location bot not from the Database log location.
I'll describe is:
1. "ServerA" database "TEST" is running LSBackup, a new TRN file is creating on \\ServerB\logship and the 72 hours old files are deleted as needed (from \\ServerB\logship.
2. My "TEST" database on the secondary server "ServerB" have one MDF file on C:\DBDATA\TEST.MDF and one log file C:\DBLOG\TEST.LDF. When the LSCopy is running (on Server B) it's copy the TRN file from the shared \\ServerB\logship to C:\DBLOG\ directory (as I configured it).
3. The third job LSRestore is running on the secondary server too (Server B) and successfully finished.
After all this I cannot see that the times tamp of the MDF or LDF was changed, even them size didn't change. (if it should be changed).
The Copied trn files on the C:\DBLOG\ is never deleted so this cause my disk to blow up.
Why the copied trn files still exist on the disks after weeks?
Thanks,
Miki
-
Haven't you specified the time under 'Delete Files Older Than' under "Transaction Log Backup Settings" window?
http://sqlserveruniverse.com/content/ADMN0100111132007LogShipping.aspx
MJ
-
Hi MJ,
I'm not talking about the backup files those are deleted (as I described) from the shared location by LSBackup job.
The issue is with the secondary servers (Server B). The files that are copies from the shared location to the database's log directory (C:\DBLOG) are never deleted as I thought it should be.
Any idea?
Thanks,
Miki
-
I think it has to do with the rights of the sqlagent account on that server. Try to login in that server with the sqlagent account and then delete the files, if you are able to do it, it means the sql agen account has enough rights, otherwise it does not have enough rights.
-
Haven't you specified the time under 'Delete Copied Files After' under "Copy Files" tab on "Secondary Database Settings". window?
http://sqlserveruniverse.com/content/ADMN0100111132007LogShipping.aspx
MJ
-
OK, I changed all LSCopy... job to run with ServerB\admin (the user is in the administrators group).
But nothing changed, the old files still exist on the target location C:\DBLOG\....
I ran manually the LSCopy... job on server B, here is the results:
C:\>"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqllogship.exe" -
Copy AA5ABEEB-30C7-40D1-A6EA-15DF1C2F56A4 -server ServerB
Microsoft (R) SQL Server Log Shipping Agent
[Assembly Version = 9.0.242.0, File Version = 9.00.1399.00]
c Microsoft Corp. All rights reserved.
2009-11-13 00:12:51.36 ----- START OF TRANSACTION LOG COPY -----
2009-11-13 00:12:51.43 Starting transaction log copy. Secondary ID: 'aa5abeeb-3
0c7-40d1-a6ea-15df1c2f56a4'
2009-11-13 00:12:51.45 Retrieving copy settings. Secondary ID: 'aa5abeeb-30c7-4
0d1-a6ea-15df1c2f56a4'
2009-11-13 00:12:51.45 Retrieved copy settings. Primary Server: 'ServerA', Pr
imary Database: 'TESTDB', Backup Source Directory: '\\serverB\sqllogship', Backup De
stination Directory: 'C:\DBLOG\TESTDB', Last Copied File: 'C:\DBLOG\TESTDB\TESTDB
_20091112221049.trn'
2009-11-13 00:12:51.45 Copying log backup files. Primary Server: 'ServerA', P
rimary Database: 'TESTDB', Backup Source Directory: '\\serverB\sqllogship', Backup D
estination Directory: 'C:\DBLOG\TESTDB'
2009-11-13 00:12:51.51 Checking to see if any previously copied log backup file
s that are required by the restore operation are missing. Secondary ID: 'aa5abee
b-30c7-40d1-a6ea-15df1c2f56a4'
2009-11-13 00:12:51.51 The copy operation was successful. Secondary ID: 'aa5abe
eb-30c7-40d1-a6ea-15df1c2f56a4', Number of log backup files copied: 0
2009-11-13 00:12:51.51 ----- END OF TRANSACTION LOG COPY -----
Exit Status: 0 (Success)
C:\>
I'm attaching a screen shut (URL) of the LSCopy job configuration. As you can see I configured it to delete files that older than 1 day.
http: / / http://www.uploadgeek.com/share-1914_4AFC8340.html
Any idea?
Thanks,
Miki
-
Hey miki,
Were you able to resolve it? I notice that in your log at the end it says...
"Number of log backup files copied: 0". Maybe your job can't delete it because it doesn't think there were files copied? I'm just guessing here.
I have a similar problem where my log shipping job will not delete the old files. But my log says the following..
Message
2009-11-24 09:00:01.66Deleting old log backup files. Primary Database: 'ALM_Images'
2009-11-24 09:00:01.68Deleting log backup file. File: '\\SRVDR01\E$\Backups\Images\Replication\Images_20091122110001.trn', Last Write Time (UTC): '11/22/2009 11:00:01 AM'
It looks like it's trying to delete but never actually deletes the file as I see old ones in the folder.
-
I think the job will always run under the account that starts the SqlServerAgent service!
-
Give the sql server Agent service account full permissions to the logshipping folder in the standby server and the logs will be deleted.
If the log files have "Archive" attribute, you will need to reset that using Attrib -A in the command line. this can be scheduled as a job.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply