March 13, 2011 at 10:54 am
I have two servers I am trying to get this working on. We use a separate physical server for each database application. For whatever reason, this job will not complete successfully on either server (I have it working on some others though).
I run the job as sa. All other jobs run as sa and work fine.
I generally use maintenance plans for jobs, and other jobs work fine for backups and maintenance clean up, statistics, et cetera.
Focusing on just one of the servers (they both have the exact same error), all I get is what follows:
Started: 11:48:01 AM DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:48:01 AM Finished: 11:48:03 AM Elapsed: 2.312 seconds. The package execution failed. The step failed.
From the maintenance task log file:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.4035
Report was generated on "RHDBHS02".
Maintenance Plan: Integrity Checks
Duration: 00:00:00
Status: Warning: One or more tasks failed..
Details:
Check Database Integrity Task (RHDBHS02)
Check Database integrity on Local server connection
Databases: master
Include indexes
Task start: 2011-03-13T11:48:03.
Task end: 2011-03-13T11:48:03.
Failed:(0) Alter failed for Server 'RHDBHS02'.
From the file ERRORLOG:
2011-03-13 11:48:03.44 spid134 Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.
I have searched the Internet, and especially SQLServerCentral.com, but can't find an answer that works.
Any assistance would be very much appreciated.
Dave
Dave
March 13, 2011 at 12:20 pm
The message about user options is not an error, it's just an informational message.
Is there no other information anywhere?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2011 at 12:45 pm
Not that I can find.
The only thing I can find that looks weird is that the first message says it was run as the server logged in user. I verified that every connection is set to sa. Frequently I see SQL Server change the job user to the logged in user when you save the maintenance plan. When that happens you change the job user and it works.
I am going to compare permission settings for the logged in user and the sa account to see if that helps.
Any advice is still welcome.
Dave
Dave
March 13, 2011 at 4:35 pm
OK, for all those others who seem to have this issue, I was able to figure out what the cause was.
First, one MS article suggested issues with SP2. So I recommend applying SP3 or newer. The product this is for does not yet support SP4 so I had to go with SP3.
Second, I ran the following based on another article I read. I found something on social.microsoft.com, which had a post that suggested another one on SQLBlogCasts.com. I am not sure if it is appropriate to post links here, so I am not going to right now. If a moderator can tell me for sure I will be glad to.
sp_configure 'Allow Updates', 0
RECONFIGURE
Everything now works fine. Both systems.
Dave
Dave
March 13, 2011 at 6:46 pm
djackson 22568 (3/13/2011)
OK, for all those others who seem to have this issue, I was able to figure out what the cause was.First, one MS article suggested issues with SP2. So I recommend applying SP3 or newer. The product this is for does not yet support SP4 so I had to go with SP3.
Second, I ran the following based on another article I read. I found something on social.microsoft.com, which had a post that suggested another one on SQLBlogCasts.com. I am not sure if it is appropriate to post links here, so I am not going to right now. If a moderator can tell me for sure I will be glad to.
sp_configure 'Allow Updates', 0
RECONFIGURE
Everything now works fine. Both systems.
Dave
Yes - I have seen that before, and allow updates has to be turned off for maintenance plans to work correctly.
BTW, when using maintenance plans you need to view the maintenance plan history and not the job history to get the error messages. In this case though, you probably didn't have anything in the maintenance plan history because that error causes the job to fail before the maintenance plan is even started.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply