June 18, 2008 at 5:03 pm
I am trying to use Maintetnance Cleanup Task to delete bak files in the folder but looks like it does not deletes.
No idea what's going on.
Any help will be appreciated.
June 18, 2008 at 5:27 pm
Wow, Wierd. When I open SSIS package on other machine , and double cliick on Maintenance Cleanup Task and there is one additional check box called :Include first level subfolders ". If I check that box SSIS package able to delete files in the folder.
So, at one machine check box appreas and other machine it does not. May be version difference.
June 19, 2008 at 12:32 am
Does sound odd....
Check the fixpacks that you have installed
~PD
June 19, 2008 at 1:11 am
Hi
I have an issue with Maintenance Plans that I can not save with my client tools. The error message is:
"No description found" - and a red X
But if I open the same Maintenance Plan on another server it saves fine.
The 2 versions of client tools and service packs are identical.
The only difference is that I have installed a lot of new software onto my client computer that may have affected the client tools for this operation.
With regards to your issue - maybe service packs should be re-applied to the client tools and also check the version - under Help --> About
Mine is:
Microsoft SQL Server Management Studio - 9.00.3042.00 (this is after applying the Service Pack of 3175)
Thanks
Kevin
June 22, 2008 at 2:23 pm
balbirsinghsodhi,
I have had this problem in 2005 maint plans too. it is a known issue.
Make certain SQL Server 2005 has Service Pack 2 installed. If it already has SP2, search microsoft for a special update for this issue.
Hope this helps. John
July 11, 2008 at 9:25 am
I'm replying here since I haven't seen this solution anywhere else. I had been receiving and error in my "Maintenance Cleanup Task" log regarding xp_delete_file. I'm running SQL Server 2005 SP2 (9.00.3054.00)
xp_delete_file() returned error 2, 'The system cannot find the file specified.'
The folder path I used was similar to the following UNC, where the ServerName is a server other than the SQL Server
\\ServerName\D$\SQL Backups
giving a command similar to
EXECUTE master.dbo.xp_delete_file 0,N'\\ServerName\D$\SQL Backups',N'bak',N'2008-07-04T06:00:24'
The SQL Service Account is the owner of the job, SysAdmin, and Administrator on both the SQL Server and the File server in the UNC.
What I discovered is that the "administrative" share D$ isn't recognized. I had to make a share on 'SQL Backups' and make sure the service account had full control via the Administrator group local to the file server, obviously not the only solution for permissions. I don't know if I should have known better regarding the administrative share, but I didnt' think it would be a problem.
So the new path, which was successfully used was
\\ServerName\SQL Backups
One caveat that should be mentioned is that the drive is on a SAN, which may have configuration issues.
July 11, 2008 at 1:54 pm
Hi,
I ahd a same problem too..We had a IP switch over and I could not edit them either. So, I created new plans and tried delete old ones, we had this weird message. So, I followed these steps to delete them:
Step 1:
Execute the below query to obtain the Maintenance plan name and Id
SELECT NAME, ID FROM MSDB..SYSMAINTPLAN_PLANS
Step 2:
Replace the Id obtained from Step 1 into the below query and delete the entry from log table
DELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID=' '
Step 3:
Replace the Id obtained from Step 1 into the below query and delete the entry from subplans table as shown below,
DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = ' '
Step 4:
Finally delete the maintenance plan using the below query where ID is obtained from Step1
DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = ' '
Step 5:
Check and delete the jobs from SSMS if it exists.
-V
July 13, 2008 at 5:26 am
Hi guys,
take a look at this article. Maybe it could throw some light on ur issue. Carefully read the note highlighted in red.
Courtesy: http://www.mssqltips.com/tip.asp?tip=1324
Regards,
Rajesh
Problem
In two previous tips we discussed how to automate full backups and transaction log backups by creating scripts that iterate through each of your databases and then execute the backup commands. A reader requested information about how to automate the process of deleting older backup files, so this tip explains one approach for getting rid of older backup files that are generated.
Solution
In previous tip we took a look at using Windows Scripting (Simple way to find errors in SQL Server error log) to read through the error log files and generate a slimmer error file with just the error messages and the related messages that were created at the same time. In this tip, we will also be using Windows Scripting to go through each of the subfolders to find files older than a certain timeframe and then delete these files.
Here is the VBScript code. This was pulled together from a few different code snippets found on the internet.
There are two parameters that need to be adjusted:
iDaysOld - specify how many days old the files need to be for the script to delete them
strPath - this is the folder where the backups are created.
iDaysOld = 7
strPath = "C:\BACKUP"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
Set colSubfolders = objFolder.Subfolders
Set colFiles = objFolder.Files
For Each objFile in colFiles
If objFile.DateLastModified < (Date() - iDaysOld) Then
MsgBox "Dir: " & objFolder.Name & vbCrLf & "File: " & objFile.Name
'objFile.Delete
End If
Next
For Each objSubfolder in colSubfolders
Set colFiles = objSubfolder.Files
For Each objFile in colFiles
If objFile.DateLastModified < (Date() - iDaysOld) Then
MsgBox "Dir: " & objSubfolder.Name & vbCrLf & "File: " & objFile.Name
'objFile.Delete
End If
Next
Next
Setup
To use this script first create a new text file and copy and paste the above code. Save this new file as C:\DeleteBackupFiles.vbs or whatever you would like to call it.
Create another new text file and copy and paste the code below. .(If you rename the file or place it in another folder use this instead of the info below.) Save this new file as C:\DeleteBackupFiles.bat.
C:\DeleteBackupFiles.vbs
Note: As a safeguard the script just displays a message box with the folder and file name. To actually delete the files you will need to remove the single quote ' before the two delete lines.:
'objFile.Delete
At this point you can just run the BAT file and this will delete any files in the subfolders.
The way this works is it will delete any files that it finds in the subfolders after the starting point. It does not care about what kind of files they are, it will delete all files that are older than the timeframe specified. The script also will delete files in the root folder and any files one subfolder level deep. It does not go beyond the first subfolder level.
So if you specify your backups to be in "C:\Backup" this script will delete all files in the "C:\Backup" folder as well as any files in the first level subfolders as long as the date of the file is older than specified.
This can now be setup as a scheduled job by calling the BAT file. SQL Server Agent doesn't like running VBScript files directly, so by using a BAT file you can set this up as a scheduled job.
Next Steps
Use this script as is or modify this to have additional options that meet your needs such as specifying certain types of files.
The script uses the DateLastModified property, but this could also be changed to the DateCreated property.
Modify the script to add some logging, so you can see what has been deleted.
The maintenance plans use XP_DELETE_FILE (2005) and SQLMaint (2000) to delete the older files. You could look at these options as well.
Take a look at the other backup scripts to automate your entire backup process.
http://www.mssqltips.com/tip.asp?tip=1070
http://www.mssqltips.com/tip.asp?tip=1318
Related Tips
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply