Maintenance Plan errors on SQL 2005
-
I'm running SQL Server 2005 Std in a single forest, single domain 2003 AD environment. On the Maintenance Plan, the Maintnenance Cleanup Plan is failing with error -1073548784. The Backup Database and Check Database Integrity tasks within the same Maintenance Plan run without error. I'm new to SQL and trying to learn the ropes on the fly.
-
Then let me give you some advice... maint plans are worthless. Don't even go down that road. You'll spend more time tshooting them than you will anything else.
If you want to run backups, then run backups. If you want to run checkDB, then run it. But don't rely on maint plans to work for you.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
What's the Maintenance Cleanup Plan trying to do? Remove old backup files?
Crever, I've got maintenance plans that have been running smoothly since I set them up in September, and with my prior employer, I had ones that ran smoothly for five or more years. I find them quite easy to set up and maintain. Of course, there are things that I also set up directly in T-SQL and then schedule jobs. But simple maintenance plans haven't been a problem for me. Is my experience seriously atypical?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared-
Yes. The Maintenance Plan task that is failing is the cleaning up of database backup files. The other two tasks within the Maintenance Plan (Backup and Check Integrity) run without error.
-
GSquared (5/19/2008)
What's the Maintenance Cleanup Plan trying to do? Remove old backup files?Crever, I've got maintenance plans that have been running smoothly since I set them up in September, and with my prior employer, I had ones that ran smoothly for five or more years. I find them quite easy to set up and maintain. Of course, there are things that I also set up directly in T-SQL and then schedule jobs. But simple maintenance plans haven't been a problem for me. Is my experience seriously atypical?
GSquared - I've been using them for years, also without issue. Simple, quick setup for the most mundane, but necessary, database tasks. Just my 2 cents!
-- You can't be late until you show up.
-
Hey there GSquared... at almost every place I've been, maint plans just stop working at some point. And they don't handle changes very well at all. I don't know if your results are necessarily atypical, but there are plenty of cases on both sides of the fence. I know plenty of people who swear by them, and plenty of people who swear at them. I'm just one of the latter.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
The first thing I ask when dealing with problems on maintenance plans in 2005 is which Service pack is installed. There have been a couple of bugs and most of them were fixed in SP2. In the RTM version the clean up task wouldn't remove files located in a subfolder even though you had the option to select this.
Do you get any error messages or is it just not removing the old files without an error. What's the logfile saying?
[font="Verdana"]Markus Bohse[/font]
-
Markus-
Log files don't indicate any other errors. Where would I find the level service pack? (Server Properties?)
-
You have to check the version/build number
9.00.1399 is RTM
9.00.2047 is SP1
9.00.3042 is SP2
See also http://www.sqlservercentral.com/articles/Administration/2960/
[font="Verdana"]Markus Bohse[/font]
-
In query analyzer, run Select @@version. It'll display version numbers which you can check here to determine your level.
http://support.microsoft.com/kb/321185
-- You can't be late until you show up.
-
Markus-
Version is 9.00.3054
-
I wonder if you're looking at the correct logfile.
Do you have logging enabled in your maintenance plan?
You should look at the maintenance plan logfile not the SQL error log. The maintenance plan logfile should give you more detail about what is causing the error.
[font="Verdana"]Markus Bohse[/font]
-
Markus-
See below from Maintenance Plan log file (Error -1073548784)
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2008-04-21T00:00:05'
" 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.
-
obnetadmin (5/19/2008)
Markus-See below from Maintenance Plan log file (Error -1073548784)
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2008-04-21T00:00:05'
" 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.
The parameter which is missing in the procedure xp_delete_file is the path there the backup files are located. Check in your maintennace plan if the path is empty.
[font="Verdana"]Markus Bohse[/font]
-
Markus-
That did it! Thanks for your help.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply