Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Thursday, April 21, 2011 3:48 PM
Hi,
I am getting strange error( may be it is strange to me) with updatestats maintenance plan. the job related to updatestat stats failing and i checked error message and it as follows while maintenance plan history show green right mark(success).
windows server: Microsoft Windows NT 6.0 (6002)
sql server 2008 sp1: Microsoft SQL Server Enterprise Edition (64-bit)
Schedule run error message
Date 4/17/2011 9:30:00 PM
Log Job History (UpdateStats.All DBs)
Step ID 1
Server server1
Job Name UpdateStats.All DBs
Step Name All DBs
Duration 00:04:42
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: domain1\svcaccount1. 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: 9:30:00 PM Progress: 2011-04-17 21:30:02.82 Source: {E4453A44-5034-45C3-BF29-AFD3CC0325A6} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2011-04-17 21:34:40.15 Code: 0xC0024104 Source: Update Statistics Task Description: The Execute method on the task returned error code 0x80004003 (Object reference not set to an instance of an object.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:30:00 PM Finished: 9:34:40 PM Elapsed: 279.881 seconds. The package execution failed. The step failed.
I ran the job manully and it ran for 30 min and failed with following error message
Manual run error message
Date 4/17/2011 9:57:28 PM
Log Job History (UpdateStats.All DBs)
Step ID 1
Server server1
Job Name UpdateStats.All DBs
Step Name All DBs
Duration 00:29:08
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: doain1\svcaccount1. ....0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:57:28 PM Progress: 2011-04-17 21:57:28.97 Source: {E4453A44-5034-45C3-BF29-AFD3CC0325A6} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2011-04-17 21:57:32.25 Source: Update Statistics Task Executing query "use [master] ".: 50% complete End Progress Progress: 2011-04-17 21:57:32.28 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[JobFailNotify] WITH FUL...".: 100% complete End Progress Progress: 2011-04-17 21:57:32.32 Source: Update Statistics Task Executing query "use [msdb] ".: 50% complete End Progress Progress: 2011-04-17 21:57:32.32 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[JobFailNotify] WITH FUL...".: 100% complete End Progress Progress: 2011-04-17 21:57:32.37 Source: Update Statistics Task Executing query "use [ePO4_USSPIS15] ".: 0% complete End Progress Progress: 2011-04-17 21:57:32.38 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[EPOAFToCatMapping] WITH...".: 0% complete End Progress Progress: 2011-04-17 21:57:32.38 Source: Update Statistics Task Executing query "use [ePO4_USSPIS15] ".: 0% complete End Progress Progress: 2011-04-17 21:57:32.38 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[EPOAgentGUIDBlackList] ...".: 0% complete End Progress Progress: 2011-04-17 21:57:32.38 Source: Update Statistics Task Executing query "use [ePO4_USSPIS15] ".: 0% complete End Progress Progress: 2011-04-17 21:57:32.38 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[EPOAgentHandlerAssignment...".: 1% complete End Progress Progress: 2011-04-17 21:57:32.38 Source: Update Statistics Task Executing query "use [ePO4_USSPIS15] ".: 1% complete End Progress Progress: 2011-04-17 21:57:32.38 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[EPOAgentHandlerAssignment...".: 1% complete End Progress Progress: 2011-04-17 21:57:32.38 Source: Update Statistics Task Executing query "use [ePO4_USSPIS15] ".: 1% complete End Progress Progress: 2011-04-17 21:57:32.38 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[EPOAgentHandlerAssignment...".: 1% complete End Progress Progress: 2011-04-17 21:57:32.39 Source: Update Statistics Task Executing query "use [ePO4_USSPIS15] ".: 2% complete End Progress Progress: 2011-04-17 21:57:32.39 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[EPOAgentHandlerAssignment...".: 2% complete End Progress Progress: 2011-04-17 21:57:32.39 Source: Update Statistics Task Executing query "use [ePO4_USSPIS15] ".: 2% complete End Progress Progress: 2011-04-17 21:57:32.41 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[EPOAgentHandlerAssignment...".: 2% complete End Progress Progress: 2011-04-17 21:57:32.41 Source: Update Statistics Task Executing query "use [ePO4_USSPIS15] ".: 2% complete End Progress Progress: 2011-04-17 21:57:32.46 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[EPOAgentHandlerDataChanne...".: 2% complete End Progress Progress: 2011-04-17 21:57:32.46 Source: Update Statistics Task Executing query "use [ePO4_USSPIS15] ".: 3% complete End Progress Progress: 2011-04-17 21:57:32.49 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[EPOAgentHandlerDataChanne...".: 3% complete End Progress Progress: 2011-04-17 21:57:32.49 Source: Update Statistics Task Executing query "use [ePO4_USSPIS15] ".: 3% complete End Progr... The package execution fa... The step failed.
now the maintenance plan shows red cross mark (failed) and error message as follwos
Maintenance plane error message
Executing the query "UPDATE STATISTICS [dbo].[PAHoldDefDashboard]
WIT..." failed with the following error: "Table 'PAHoldDefDashboard' does not exist.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
then I checked for above object in database and it’s not there. all the sudden today update maintenance plan job run successfully in 5min approximately.
Question: why some times maintenance plan jobs failing without modifying anything on them?
In this particular scenario if object (Table ‘PAHoldDefDashboard’) deleted in database then the maintenance plan should be updated according (that’s how maintenance plan should work and working most times)..could any one give more explanation on how maintenance plans working and if possible relate your explanantion to this scenario
thanks in advance
Brahma http://brahmarao.tech.officelive.com http://nani1211.info/sql
Wednesday, April 27, 2011 8:13 PM ✅Answered
Hello,
Yes if the table is dropped and recreated frequently then you may see the error, can you remove the particular database from maintenance plan and create a SQL job which update the stats or rebuild index, exclude the table which are frequently dropped and recreated and see that can help.
Thank You
Jayaprakash JO - MSFT
Friday, April 22, 2011 7:35 AM
Hello,
Thank you for your question.
I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.
Thank you for your understanding and support.
Best Regards,
Chunsong Feng
Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Friday, April 22, 2011 2:35 PM
Hello ,
Can you run the following query and let me know the TABLE _SCHEMA for PAHoldDefDashboard table.
select * from Information_schema.tables
Thank You
Jayaprakash JO - MSFT
Friday, April 22, 2011 4:49 PM
Hi JO,
there is no table with name PAHoldDefDashboard. i mentioned same thing in my initial post.
Thanks,
Brahma
Brahma http://brahmarao.tech.officelive.com http://nani1211.info/sql