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.
Monday, June 13, 2016 7:07 AM
Hi
Good day..
I configured reorganiese Index plan on my prod server, it's failing due to the below following reason please check and let me help to fix this issue!
Message
Executed as user: TEST1\Server$. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 10:30:00 PM Progress: 2016-06-10 22:30:01.00 Source: {F4F51A0B-544D-4179-9E8C-C0CEC502D62B} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2016-06-10 22:30:23.21 Code: 0xC0024104 Source: Reorganize Index 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: 10:30:00 PM Finished: 10:30:23 PM Elapsed: 22.886 seconds. The package execution failed. The step failed.
Thanks,
V
DBA
Monday, June 13, 2016 10:54 AM
I cannot understand the reason, can you ?. Please post more details like how are you running the reorganize via TSQL or SSMS or SSIS. Does database on which tables reside have database owner.
If this is a job who owner of the job. Does that login is valid login ?
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Monday, June 13, 2016 12:00 PM
That error is from the SQL Agent job, which is limited. Please see the actual maintenance plan log.
https://www.mssqltips.com/sqlservertip/3225/sql-server-maintenance-plans-reporting-and-logging/
Monday, June 13, 2016 12:30 PM
Hello,
Please edit the maintenance plan and save it to see if an error is displayed.
Based on the error “Object reference not set to an instance of an object” I would suggest you to check on the section of the Reorganize Task (maintenance plan) where you select the databases to make sure all of them still exist and there are none of them offline.
Hope this helps.
Regards,
Alberto Morillo
SQLCoffee.com
Monday, June 13, 2016 1:14 PM
Hi Shanky,
I just checked, the user having sys adimin rights and we configured reorganize index by using Maintenance plan wizard.. Even the job history also showing above error only.
I also verified no other jobs are conflicting to Index re organize job.
Did I miss anything to check, please let me know if I miss anything!
Thanks
DBA
Monday, June 13, 2016 2:23 PM
Hi Shanky,
I just checked, the user having sys adimin rights and we configured reorganize index by using Maintenance plan wizard.. Even the job history also showing above error only.
I also verified no other jobs are conflicting to Index re organize job.
Did I miss anything to check, please let me know if I miss anything!
Thanks
DBA
Yes you might, there has to be more information. With such limited information I cannot help. Did you checked jobs owner and database owner.
Can you rerun the job again and see if it fails ?
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Monday, June 13, 2016 9:39 PM | 1 vote
That's a .NET error - could be bug somewhere in SSIS.
A simple resolution would be to scrap these jobs in favour of the solution that most people use these days: http://ola.hallengren.com
Tuesday, June 21, 2016 11:05 AM
Hi Alberto,
The reason for failing Reorganise Index is due to deadlocks..Is there anyway to avoid deadlocks, we are rebuilding Indexes weekly once @ 2AM.
Please check the below error message
EX [IX_DATA_IN_OUT] ON [..." failed with the following error: "Transaction (Process ID 1299) 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.
Kindly suggest me how to avoid and rebuild Indexes..
Thanks
DBA
Tuesday, June 21, 2016 9:54 PM
It appears that there is more business going on in the database at that time. Maybe you should schedule the job at a different time?
It also worth investigating what the other job is doing to be able to understand why the deadlock occurs.
You seem to mix "reorganize" and "rebuild" - those are two quite different operations, although they aim at the same thing. Which one are you using?