Maintenance Plan errors on SQL 2005

  • obnetadmin

    SSC Rookie

    Points: 38

    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.

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    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/DBARant

    Minion Maintenance is FREE:

  • GSquared

    SSC Guru

    Points: 260824

    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

  • obnetadmin

    SSC Rookie

    Points: 38

    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.

  • tosscrosby

    SSCoach

    Points: 18045

    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.

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    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/DBARant

    Minion Maintenance is FREE:

  • MarkusB

    SSC-Dedicated

    Points: 37369

    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]

  • obnetadmin

    SSC Rookie

    Points: 38

    Markus-

    Log files don't indicate any other errors. Where would I find the level service pack? (Server Properties?)

  • MarkusB

    SSC-Dedicated

    Points: 37369

    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]

  • tosscrosby

    SSCoach

    Points: 18045

    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.

  • obnetadmin

    SSC Rookie

    Points: 38

    Markus-

    Version is 9.00.3054

  • MarkusB

    SSC-Dedicated

    Points: 37369

    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]

  • obnetadmin

    SSC Rookie

    Points: 38

    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.

  • MarkusB

    SSC-Dedicated

    Points: 37369

    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]

  • obnetadmin

    SSC Rookie

    Points: 38

    Markus-

    That did it! Thanks for your help.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply