Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Maintenance Plans Failing Expand / Collapse
Author
Message
Posted Sunday, October 21, 2012 4:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:12 AM
Points: 8, Visits: 196
I am getting the error below when I setup a job using the Maintenance Plan (SQL 2008 SP2). I have tried to create backups, integrity checks, update stats and index reorgs and get the same error every single time. I can setup backup jobs or other jobs manually that run under the sql agent account but not through a maintenance plan. The jobs are setup to run as the sql server agent account which has sys admin privs. The account is a local account on the server and belongs to the Users group. A little background...I received this server that is an image of another server. I noticed that the server name was incorrect when I did a select @@servername or sp_helpserver so I ran the sp_dropserver and sp_addserver to correctly name the server. I have also given the agent account privileges on the necessary directories. I am at a lost on how to fix this error. Also, the above steps worked on another server and jobs created from maintenance plans work fine. It's just this one server that isn't working now. I do not know if this is a permission issue or a SSIS issue.

Any help is greatly appreciated.

Error

Executed as user: xxxxxx\sqlsrvagt. Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:18:27 PM Error: 2012-10-19 14:18:28.47 Code: 0xC00291EC Source: {DFAFCDDB-E46A-4610-A902-A2F91DE4D79E} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2012-10-19 14:18:28.48 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2012-10-19 14:18:28.51 Code: 0xC0024104 Source: Back Up Database Task Description: The Execute method on the task returned error code 0x80131604 (Exception has been thrown by the target of an invocation.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Error: 2012-10-19 14:18:28.51 Code: 0xC0024104 Source: {9D9F5F95-7345-4A2B-BD70-5D91C8C08CD0} Description: The Execute method on the task returned error code 0x80131604 (Exception has been thrown by the target of an invocation.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Warning: 2012-10-19 14:18:28.51 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:18:27 PM Finished: 2:18:28 PM Elapsed: 1.156 seconds. The package execution failed. The step failed.
Post #1375212
Posted Monday, October 22, 2012 2:17 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 681, Visits: 1,338
Are you trying to run a preexisting maintenance plan, or are you creating one from scratch and are getting this error when trying to save or run it? Also, just to check, is the SSMS you are connecting from the same version and patch level as the instance of SQL Server you are receing the error on? I have commonly seen where different versions or even SP levels of SSMS will generate errors when trying to create and/or modify maintenance plans.

Joie Andrew
"Since 1982"
Post #1375300
Posted Monday, October 22, 2012 5:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
sqlnovice2001 (10/21/2012)
Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection.
its clearly stating that you havent provided proper permissions here


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1375356
Posted Monday, October 22, 2012 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:12 AM
Points: 8, Visits: 196
The sql agent account has sysadmin privs. What other privileges does it need?
Post #1375479
Posted Tuesday, October 23, 2012 6:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:12 AM
Points: 8, Visits: 196
I also want to add that I tested running the services under the local system account (just to test, I know this isn't an option) to see if the job would still fail and it did. Same error. So it's not just when creating maintenance plans under the sql agent account but also as local system and local system has sysadmin privs as well.

Executed as user: *******\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:40:54 AM Error: 2012-10-23 07:41:00.74 Code: 0xC00291EC Source: {2A18DE07-1322-40AC-AA95-9F41C2FA8ABD} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2012-10-23 07:41:00.74 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2012-10-23 07:41:01.25 Code: 0xC0024104 Source: Check Database Integrity Task Description: The Execute method on the task returned error code 0x80131604 (Exception has been thrown by the target of an invocation.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Error: 2012-10-23 07:41:01.33 Code: 0xC0024104 Source: {AB2FE0CE-1CA4-4E16-9FC0-9172D601D902} Description: The Execute method on the task returned error code 0x80131604 (Exception has been thrown by the target of an invocation.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Warning: 2012-10-23 07:41:01.33 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:40:54 AM Finished: 7:41:01 AM Elapsed: 6.688 seconds. The package execution failed. The step failed.
Post #1376005
Posted Tuesday, October 23, 2012 7:05 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 681, Visits: 1,338
Are you trying to run a preexisting maintenance plan, or are you creating one from scratch and are getting this error when trying to save or run it?


You never answered this question. You stated that you inherited this server as an image of another server. I am curious if the "local server connection" that the error is saying is failing was setup when the maintenance plan was created to connect to an instance name which is no longer valid.

If you create a new maintenance plan does it fail with the same error, or does it succeed?


Joie Andrew
"Since 1982"
Post #1376016
Posted Tuesday, October 23, 2012 8:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:12 AM
Points: 8, Visits: 196
I am creating new maintenance plans. There were no prior jobs or maintenance plans. Ironically, I can create a manual job, for example to cycle the errorlog or a backup. Also, I changed the server name from the old one to the new server name using sp_dropserver and sp_addserver. So as far as I can tell, the local server name is correct. I don't know where else to check to see what server name it could be pulling from.

Also, the version of SSMS is 10.0.4064, same as the SQL Server version.
Post #1376077
Posted Tuesday, October 23, 2012 8:24 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 681, Visits: 1,338
Have you checked out the following:

SSIS package does not run when called from a SQL Server Agent job step


Joie Andrew
"Since 1982"
Post #1376087
Posted Tuesday, October 23, 2012 9:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:12 AM
Points: 8, Visits: 196
SSIS is not installed on the server. Not sure if these steps would apply. This is just utilizing maintenance plans. Integration Services isn't required to be installed correct? It's not installed on other servers that just utilize maintenance plans.
Post #1376115
Posted Tuesday, October 23, 2012 10:19 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 681, Visits: 1,338
No, SSIS is not required to run maintenance plans, but maintenance plans actions in SSMS are a subset of what can be done in SSIS and a maintenance plan built in SSMS is a SSIS package. I do not think it would not hurt to troubleshoot based on the article.

For more info
Maintenance Plans


Joie Andrew
"Since 1982"
Post #1376167
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse