DB Maintenance Plan JoB failing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

DB Maintenance Plan JoB failing

Author

Topic

catchvaas
Starting Member

22 Posts

Posted - 2010-11-15 : 09:30:08

Hi,
I am new to SQL administration.
I've setup a new job in our dev SQL server 2008 and setup a weekly job for DB maintenance and it keeps failing.

Here's the error:
Date 11/13/2010 8:00:01 AM
Log Job History (Weekly User DB Maintenance Plan.Weekly on Saturdays at 8 AM CT)

Step ID 1
Server USMKEVDB206\CLUS206Q
Job Name Weekly User DB Maintenance Plan.Weekly on Saturdays at 8 AM CT
Step Name Weekly on Saturdays at 8 AM CT
Duration 00:00:48
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: NA\svc_na_sqlagentsvc_q. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:00:01 AM Progress: 2010-11-13 08:00:01.88 Source: {D3A167BC-6A00-4A2E-B8D1-FD573A9226F8} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2010-11-13 08:00:48.27 Code: 0xC0024104 Source: Rebuild Index Description: The Execute method on the task returned error code 0x80004002 (Unable to cast object of type 'System.DBNull' to type 'System.String'.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:00:01 AM Finished: 8:00:48 AM Elapsed: 46.972 seconds. The package execution failed. The step failed.

Please help me fix this problem...Appreciate the help!

Regards,
Vaas

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-11-15 : 15:50:37

Please review the maintenance plan history (right-click on the maintenance plan in Object Explorer and View History). From here you will be able to see what task failed and the actual error.

Jeff

catchvaas
Starting Member

22 Posts

Posted - 2010-11-16 : 14:58:56

Hi All,

I tried deleting the maintenance plan and recreating it but still no go.

Could anyone please help me resolving this???

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-16 : 15:55:55

What Jeff said.

-Chad

catchvaas
Starting Member

22 Posts

Posted - 2010-11-17 : 14:19:12

From the histroy I see that 'Rebuild Index' task is being failed..

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-11-17 : 16:58:41

And the failure message is?

catchvaas
Starting Member

22 Posts

Posted - 2010-11-17 : 17:01:36

Hi Jeff,

Here's the error

Message
Executed as user: NA\svc_na_sqlagentsvc_q. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:02:52 PM Progress: 2010-11-16 13:02:54.83 Source: {5977FB20-436C-4981-B19C-9EF3F5690F9C} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2010-11-16 13:03:53.96 Code: 0xC0024104 Source: Rebuild Index Task Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:02:52 PM Finished: 1:03:54 PM Elapsed: 62.041 seconds. The package execution failed. The step failed.

regards,
Agni

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-11-18 : 16:56:52

That is not the message you get from the maintenance plan history. View the maintenance plan history, open the failed job and click on the failed step. In the bottom of the window there will be a text box with the error message. Copy/Paste that here.

Jeff

catchvaas
Starting Member

22 Posts

Posted - 2010-11-22 : 17:53:47

Hi Jeff,

Here's the error I get..
Executing the query "ALTER INDEX [IDX_COMM_FLD_NAME] ON [ALARMPOINT4].[..." failed with the following error: "Transaction (Process ID 123) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Please help

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-11-23 : 11:33:01

Well, that is pretty clear - your index rebuild process is being chosen as the deadlock victim. That means you have another process that is inserting/updating data on that table at the time you try to rebuild the index.

There are a couple of things you can try:

1) If you are on Enterprise Edition, try rebuilding the index online. If already rebuilding the index online, try rebuilding it offline (it will block your other process, so this might not be an option).

2) Add a non-clustered index to the table. You will need to identify an index that your other process could use instead of the index being rebuilt. This might fix the issue - but no guarantees.

3) Reschedule one of these processes so they do not run at the same time.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources