November 10, 2008 at 4:56 am
I have a Log Shipping setup which I administer. There are two machines, SQL1 and REPORTS. The purpose is to copy the database from the primary server SQL1 to the reporting machine REPORTS. The setup is not ideal, however the customer is always right.
SQL1 is a big powerful box with hundreds of Gigabytes of free space.
REPORTS is a smaller box, with only 70GB of space in total.
The Database is around 14GB with a similarly sized log file.
When I initially set up the Log Shipping, i set it to backup the transaction log files to a share on SQL1. This is fine as there is plenty of space.
It was set to delete files after 72 hours (so that if there is a weekend problem we still have time to sort it out).
The trouble is that these files are copied to REPORTS which does not have a lot of free space. Every weekend there is a maintenance task which re-indexes various things and swells the transaction log backups to 12-13GB.
This leads to a problem where there is either not enough free space to restore the database, or there is not enough space to copy new files over.
Is there any way to have the REPORTS server delete files as soon as it uses them? That would alleviate problems in the short term.
November 10, 2008 at 4:58 am
I should point out that I am not a DBA, nor do I claim to be. However this is now my problem to deal with!
Cheers
November 10, 2008 at 7:56 am
There is not a built-in option for this, but here is another thought - did you check the recycle bin on the larger server? Make sure it is enabled for the drive that the files reside on. I think the file delete comes from the main server in your case (assuming it is handling distribution) so it may actually drop things into the recycle bin. If that is the case, you may be able to let it delete files right away and rely on them being recycled.
November 10, 2008 at 8:00 pm
I had the same problem. So I've wrote a stored procedure that you can schedule it to run every hour(?) to delte the used file. Hope it can help you.
CREATE PROCEDURE dbo.spLogShipping_DeleteFile_AfterRestore
@pTrnFilePath varchar(1000)
AS
-- Written By : Paul Song
-- Date : 11/11/2008
-- List of Transaction Files on Target Server
-- Mark if the file is already used
-- Delete the used files on Target Server
DECLARE @sqlString varchar(1000)
CREATE TABLE #TempLogFileList
(
Id int identity(1, 1) not null,
TrnFileName nvarchar(128) null,
SucceededFlag int null
)
-- List of Transaction Files on Target Server
SELECT @sqlString = 'dir /B ' + @pTrnFilePath + '*.TRN'
INSERT INTO #TempLogFileList (TrnFileName)
EXEC master..xp_cmdshell @sqlString
DELETE #TempLogFileList WHERE TrnFileName Not like '%.TRN'
-- Mark if the file is already used
UPDATE #TempLogFileList
SET SucceededFlag = 1
WHERE TrnFileName in
(
SELECT last_file FROM msdb.dbo.log_shipping_plan_history WHERE succeeded = 1 and activity = 1
)
-- Delete the used files on Target Server
DECLARE rsFiles CURSOR
READ_ONLY
FOR SELECT TrnFileName FROM #TempLogFileList WHERE SucceededFlag =1
DECLARE @filename varchar(64)
OPEN rsFiles
FETCH NEXT FROM rsFiles INTO @filename
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @sqlString = 'Del ' + @pTrnFilePath + @filename
Exec master..xp_cmdShell @sqlString
END
FETCH NEXT FROM rsFiles INTO @filename
END
CLOSE rsFiles
DEALLOCATE rsFiles
-- Drop Table #TempLogFileList
GO
November 11, 2008 at 3:43 am
are you using the wizard for this log shipping.? If so are you keeping 72 hours worth of logs as well on the reports server? this value is specified separately in the wizard so you could reduce that and keep less history on your reports server
---------------------------------------------------------------------
November 11, 2008 at 10:19 am
george sibbald (11/11/2008)
are you using the wizard for this log shipping.? If so are you keeping 72 hours worth of logs as well on the reports server? this value is specified separately in the wizard so you could reduce that and keep less history on your reports server
I've checked over my notes of the setup process. There is indeed two places where I specified 72 hours, one for the Primary and one for the Secondary. I must have not noticed it at the time since they look the same!
I have changed the primary to 5 days, and the secondary to 1 day. After leaving it to run for a bit the secondary machine now does not keep logs older than a day, but the primary still has the last three and a bit days. That's kind of what I was looking for. I may reduce it further in future.
Paul Song (11/10/2008)
So I've wrote a stored procedure...
That looks like a good idea. One of the problems I can foresee is where the system has to do two large restore operations. The first succeeds, but the second fails as there is not enough space to restore to. With this script it would clean up the first one's log file, leaving enough room to restore the second. I might give this a shot.
One problem though, I tried to run this part:
SELECT last_file FROM msdb.dbo.log_shipping_plan_history WHERE succeeded = 1 and activity = 1
however the msdb does not contain this table (view?). There are a bunch of other ones which I might be able to find the same information from. Does anyone know if this has been deprecated in a SP or anything?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply