SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Maintenance Plans Failing


SQL Server Maintenance Plans Failing

Author
Message
sqlnovice2001
sqlnovice2001
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 241
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.
Joie Andrew
Joie Andrew
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6499 Visits: 2032
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"
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13394 Visits: 4077
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;-)
sqlnovice2001
sqlnovice2001
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 241
The sql agent account has sysadmin privs. What other privileges does it need?
sqlnovice2001
sqlnovice2001
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 241
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.
Joie Andrew
Joie Andrew
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6499 Visits: 2032
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"
sqlnovice2001
sqlnovice2001
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 241
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.
Joie Andrew
Joie Andrew
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6499 Visits: 2032
Have you checked out the following:

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

Joie Andrew
"Since 1982"
sqlnovice2001
sqlnovice2001
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 241
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.
Joie Andrew
Joie Andrew
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6499 Visits: 2032
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search