August 9, 2011 at 7:18 am
Hi, I'm new to setting up maintenance plans and am struggling to get something that works. At present, I'm getting the following message in the Maintenance Cleanup Task section:
"EXECUTE master.dbo.xp_delete_file 0,N'',N'bak',N'2..." failed with the following error:
"Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Anyone able to help point me in the right direction?
Thx
August 9, 2011 at 7:54 am
Here are some suggestions from around the web, not sure whether they apply as you don't have a whole lot of information to go on.
Also I believe this is a SQL 2000 command, is that what version of SQL you are using? If not there is probably a better way of doing it.
August 9, 2011 at 8:19 am
Thx. Here's a bit more info on the environment.
The server the plan is running against is running SQL Server 2008 V2 (10.50.1600.1) and this is one step in a plan created using the wizard on SSMS 2008 V2 on my (Window7) desktop. I've not tweaked anything generated by the wizard so the failed statement is exactly as generated: there's no "View T-SQL" button for the Maintenance Cleanup Task so I'm assuming the command issued is exectly as shown in the error message. Privilege wise, I'm not runninng as sa but I do have sa pivileges over this database server.
Hope this helps.
August 9, 2011 at 8:32 am
Check the role for the SQL Server Service account, is it set to sysadmin?
Here's a good article on some work around code for this problem.
Other thing to check would be whether it would work if you change the file type from bak to log or txt? Apparently this xp only deletes certain file types.
August 9, 2011 at 12:33 pm
Check the folder path that you defined in the Define Maintanence Cleanup Task window. The sql service account will need permissions to this folder. Also this path cannot be blank, which may be your issue.
Adam Durr
Remote DBA support
www.bluegecko.net
August 9, 2011 at 3:02 pm
Open up the maintenance plan in SSMS - select the maintenance cleanup task and generate the T-SQL. Paste that here and we should be able to see if there is a problem with how you have it configured.
Other than that, follow up the recommendations of the other posters to see if that helps.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 10, 2011 at 1:27 am
My original issue is now fixed, so many thanks guys for your help. It came down to leaving the path blank (I foolishly thought that, since the wizard knew where its outputs were going, it would also know where to delete old ones from!)
However, I've now hit another issue and I don't know if this is a problem or not. I've set my plan up so that each step is linked to the previous one based on the "Success" criteria. EG The DB Log backup step should only be triggered if the preceding step, DB backup, completes succesfully. My thoughts here were that such a set up would make the process serial. However, a look at the logs afterwards suggests that some steps (particlurly the Maintenance Cleanup steps) are being triggered in parallel - i.e. they start before their proper "place" in the plan. Is this an issue or am I being overcautious? One thing I am keen to avoid is the Maintenance Cleanup task deleting backups when no more recent backup has been taken.
I'd welcome any feedback on this.
Cheers
August 10, 2011 at 2:01 am
Can you paste a screen of your maintenance plan?
August 10, 2011 at 2:19 am
Just tried to upload a screenshot as an attachment but got this:
Server Error in '/Forums' Application.
--------------------------------------------------------------------------------
Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.
Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
<!-- Web.Config Configuration File -->
<configuration>
<system.web>
<customErrors mode="Off"/>
</system.web>
</configuration>
Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.
<!-- Web.Config Configuration File -->
<configuration>
<system.web>
<customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>
</system.web>
</configuration>
August 10, 2011 at 2:20 am
Upload the screenshot to a websitre and place the link 🙂
August 10, 2011 at 2:34 am
Is this usable?
Sorry - I meant to add that this is the plan AFTER I had changed it to use "Completion" rather then "Success" for the links between steps. It didn't make any difference....
August 10, 2011 at 3:02 am
And how do you check if the cleanup task started before the log backup?
But I don't understand why you make this chain?
You make a full backup and directly a log backup of your database(s)...
It's beter to make sub-plans, so you can shedule the tasks in a maintenance window.
EG:
Backup (Maintenance plan)
- Full Backup (Subplan: starts every day at 6AM)
- Transaction Log Backup (Subplan: starts every day each hour)
Cleanup (Maintenance plan)
- Cleanup Old Full Backups (Subplan: starts every day at 2AM)
- Cleanup Old Log Backups (Subplan: starts every day at 2AM)
- Cleanup Maintenance Job Logs (Subplan: starts weekly at sunday at 3AM)
- Cleanup History (Subplan: starts weekly at sunday at 3AM)
Optimize (Maintenance plan)
- Rebuild/Reorganize Index (Subplan: starts every day at 2AM)
- Update Statistics (Subplan: starts weekly at sunday at 5AM)
- Check Integrity (Subplan: starts every day at 1AM)
Then you've more controle and easier to check/restart (jobs)
August 10, 2011 at 4:21 am
I'd agree - and if I was a "proper" DBA then I'd be wanting to do it properly along the lines suggested. But I'm not a DBA - we don't have one - and I have been asked to develop a very simple maintenance plan, to run once a day, that I can hand over to our service desk for them to "copy" and implement on each of our DB servers. Not ideal, but since we don't have any sort of consistent DB backups at present then its at least a start on a way forward!
But to answer the question: why do I think the steps are running out of order?
Here's the link to a screenshot of the plan's execution history.
If you look at the "start" timestamps for the steps you'll see that (a) they are out of order and (b) some steps start at the same time. Both of these lead me to beielve that teh plan is not executing serially as intended.
Thx for your assistance BTW!
August 10, 2011 at 5:13 am
Your start as DBA'er 🙂
I don't know the configuration of your db's, but when this would be your daily maintenance plan, I will put your recovery models into 'Simple', so you don't need transaction log backups - less to take care about.
This gives you more free disk space (no log backups) but you only can't recover back 'in time' (only recover to the time of the latest full backup).
About the chain: it's normal about the same start times, as you can see, it didn't take much time to do his task (SQL Server was fast :).
Only a unlucky sorting in your screen...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply