Post reply

Maintenance Plan

  • October 23, 2008 at 12:33 am

    #128079

    Hi,

    Occasionally, a maintenance plan will fail but the reporting is very poor, saying only "Failed:(100) An exception occurred while executing a Transact-SQL statement or batch." The step that fails is a statement along the lines of:

    Use master

    GO

    EXEC dbo.usp_kill_all_connections_to_a_database

    GO

    This will work 99% of the time. Is there any way to get some better error reporting to find out what is causing this error?

    Thanks.

  • October 23, 2008 at 1:25 am

    #888484

    Check the maintenance plan history: Right click on the maintenance plan, select View History, then expand the failure and look at each entry

    Check the job history (similar to above - Right click the job and select view history, then expand the failure node)

    Check the windows and SQL Server logs to see if there was anything logged there (maybe a Deadlock?)

    I'm not sure if the maintenance plan report will include further detail, but there is an option there for "Log Extended Information" that might give you more detail.

    Maybe add some debug code into the stored procedures being called if they are your own - have them write out status to a table so you know what line failed and see if that gives you a direction to run.

    Good luck!

    Chad

  • October 23, 2008 at 1:38 am

    #888489

    Thanks for the reply. The maintenance plan history is where I got the error message from. The SQL Agent job history was even less helpful, saying "The step failed.".

    I've activated the "Log extended information" option now as you suggested and I will add some print statements to the stored proc.

    I think there's large scope for improvement from Microsoft on error reporting in SQL Server.

    Thanks again.

  • October 23, 2008 at 1:50 am

    #888494

    Hi,

    If it is 2005, if you go to the section "Logging" while creating maintenance plan, there is an option "Generate text file report". You can specify a path where this log file can be generated.

    [font="Verdana"]Renuka__[/font]

  • November 19, 2008 at 3:27 am

    #900403

    This is still happening, unfortunately. These are the two separate errors that I receive from time to time:

    "failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded.

    A severe error occurred on the current command. The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    and

    "An exception occurred while executing a Transact-SQL statement or batch."

    This is with extended logging activated.

    The step is just a RESTORE DATABASE command and the restore does complete successfully, despite the errors.

  • December 4, 2008 at 3:35 am

    #907449

    I recently tried rebuilding the maintenance plans but I'm still receiving this error every night. I've also tried adding SET NOCOUNT ON to the beginning of each step in the maintenance plans.

    Does anyone know what could be causing this?

  • December 4, 2008 at 1:55 pm

    #907921

    Did you tried logging job history to file option as someone suggested above?

    MJ

  • December 4, 2008 at 2:11 pm

    #907934

    yes, tried that and still get no more than the one line error which gives nothing away.

  • December 4, 2008 at 3:17 pm

    #907973

    Try running it manually under management studio.

    MJ

  • December 4, 2008 at 4:25 pm

    #908002

    What exactly is in the maintenance plan if you open it up? What steps?

    Could you have client tools at a different SP/CU version than the server?

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • December 5, 2008 at 3:16 am

    #908123

    The plan does work sometimes. Perhaps 1 in 5. My client tools are a CU behind but as the plan works sometimes and most of the other maintenance plans work, I didn't consider this might be the problem. The steps in the plan are roughly:

    Step 1 - Run a script via xp_cmdshell to snapshot the permissions of a database.

    Step 2 - Set the database into single-user mode to kill all connections.

    Step 3 - Restore the database

    Step 4 - Apply permissions script.

    There are 14 databases restored and the plan will fail on a different step each time.

Viewing 11 posts - 1 through 10 (of 10 total)

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