October 25, 2005 at 5:38 pm
We are running SQL2000 with SQL SP3a on a Windows 2003, SP1 system. The database maintenance routine scheduled to run against the databases seems to execute correctly without any errors in the event viewer, but it does not delete the old transaction files. We have it set to keep the last three transaction logs and to delete the rest.
Has anyone run across this issue previously? If so, what was done to correct it?
Jerry
October 25, 2005 at 5:46 pm
Did you check in the msdb.dbo.sysdbmaintplan_history table to see if the delete old transaction logs job had an entry? Please define the maint plan you have set up for transaction logs.
Example: Tran log backup hourly with the deletion of the tran logs older than 3 days?
With that give me the directory listing of the oldest transaction log backup:
d:\mssql\MSSQL$PROD_01\Backup\db_name\200510250900100.TRN
October 26, 2005 at 9:38 am
The directory is f:\Data\Microsoft SQL Server\MSSQL\Backup. The maintenance plan is for all databases on the server and its settings are as follows:
Optimization - Reorganize data and index pages (selected); Change free space per page percentage to (10%) selected. Scheduled to run every 1 day at 6:00 PM.
Integrity - Check database integrity including indexes (selected); Perform these tests before backing up the database or transaction log (selected). Scheduled to run every day at 6:15 PM.
Complete Backup - Back up the database as part of the maintenance plan and verify the integrity of the backup completion and use default directory (Selected), back up is to disk with Remove files older than 3 days (selected). Scheduled to run every day at 6:30 PM.
Transaction Log Backup - Back up the transaction log of the database as part of the maintenance plan to disk and use default directory (selected). Remove files older than 3 days is (selected). Scheduled to run every day at 6:45 PM.
Reporting - Write report a text file in directory is (selected); Delete text report files older than 4 days is (selected); email report to specified email account is (selected). Write history to the table msdb.dbo.sysdbmaintplan_history is (selected) with Limit rows in table to (1000) rows for this plan (enabled).
When we encountered the problem we manually went into the directory and deleted the older files to free up disk space so the oldest file is dated 22 Oct 2005.
October 26, 2005 at 9:52 am
I have never trusted the default directory and always use a path instead (f:\Data\Microsoft SQL Server\MSSQL\Backup). You can alsoccheck and see if the job has actualy executed the delete old files task.
Select *
From msdb.dbo.sysdbmaintplan_history
Where activity = 'Delete old db backup files'
Order By start_time Desc
Was there entries?
October 26, 2005 at 12:37 pm
Reveiw of the Plan History file shows 1 file, activity Delete old text reports, successfully deleted and it shows that supposedly the backup files for the various databases were deleted successfully as well. However, when we manually go to the directory the files that were supposedly deleted are still there. The activity log does not specify which file was "deleted" in terms of actual file name.
October 26, 2005 at 12:49 pm
The only thing I can think of is change the path from default to the actual path to the backup dir and I would also check the box for use sub-dirs for each db and re-run the job. Hope it works!
October 28, 2005 at 3:41 pm
Hi Jerry --
There's a bug in SQL Server which can cause tlog deletion to fail if the maintenance plan backs up more than one db, and the dbs have different restore modes. Here's a KB on the subject:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
Hope that helps!
Regards,
Bruce
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply