October 23, 2008 at 12:33 am
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
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
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
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
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
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
Did you tried logging job history to file option as someone suggested above?
MJ
December 4, 2008 at 2:11 pm
yes, tried that and still get no more than the one line error which gives nothing away.
December 4, 2008 at 3:17 pm
Try running it manually under management studio.
MJ
December 4, 2008 at 4:25 pm
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?
December 5, 2008 at 3:16 am
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