Unsolved
This post is more than 5 years old
6 Posts
0
2242
February 23rd, 2017 01:00
Dell OpenManage Software 8.4 - SQL errors Msg 8623
Hello,
We are using Dell OpenManage Software 8.4
With our DBA's found that procedure DeviceGroupToDevice_Update is generating lot of SQL error logs.
Msg 8623, Level 16, State 1, Line 1
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
Our DBA created a copy of our database and restored to few different servers with more resources and this procedure is still failing with that error. That is then not error related with our server or not enough hardware resources (we tried on huge SQL machines even).
Could someone take a look and rewrite that SQL procedure to better version ? it contains lot of unions and SQL can't complete query , we tried on different patch level of SQL 2012 also SQL 2014 and SQL 2016, and on all versions procedure failed with the same results.
Details:
PROCEDURE [dbo].[DeviceGroupToDevice_Update]
-- =======================================================================
-- Author: David Angel
-- Description: Updates the [DeviceGroupToDevice] table with changes.
Maybe for troubleshootting we could provide backup of our database then developer could prepare fix or better version of that procedure ? how we could provide backup to Dell ?
Thanks,
Kamil
kamilp
6 Posts
1
February 24th, 2017 01:00
Hello,
I dig a bit more and did some debugging and that procedure is failing on query that is building subquery based on DeviceGroup table, so now I have question:
table [OMEssentials].[dbo].[DeviceGroup] - 1972 rows
lot of groups :
DeviceGroupName InternalName
Remote System Update Remote system update task device group query.
Server System Update Server system update task device group query.
table DeviceGroupArchive - 0 rows
how I could Archive some group created automatically for updates ?
or how I could cleanup this table DeviceGroup ?
Thanks,
Kamil
DELL-Vijay B
183 Posts
0
February 24th, 2017 02:00
Hi Kamil,
Thanks for the details in the post. This is a known issue in OME 2.2 when tasks/device groups reaches a thresh-hold limit.
Please get in touch with support 800-945-3355 to get the one-off fix for this issue.
Deleting the executed tasks should also get this behavior corrected.
Thanks,
Vijay
DELL-Vijay B
183 Posts
0
February 24th, 2017 03:00
Hi Kamil,
This should be PSE033608
Thanks,
Vijay
kamilp
6 Posts
0
February 24th, 2017 03:00
Hello,
thank you for hint, do you know maybe how this issue is recorded / named in Dell ? I am in call with support and it's not easy to find resolution
Thanks,
Kamil
kamilp
6 Posts
0
February 24th, 2017 04:00
ok, thank you, do you know what includes :
attached query (dbo.DeviceGroupToDevice_Update.proc.sql) ?
it's some better version of this proc ? I not received yet that attachement, only solution description
Thanks,
Kamil
DELL-Vijay B
183 Posts
0
February 24th, 2017 05:00
Yes. The solution should be the corrected version of the proc. which you can get from Dell support.
Thanks,
Vijay
kamilp
6 Posts
0
February 24th, 2017 06:00
Ok, I'll ask probably our email firewall removed attachement .sql from email so I'll try to receive that in other way
Thanks,
Kamil
DELL-Vijay B
183 Posts
0
February 27th, 2017 07:00
Thanks. Let us know if this fix from Dell Support resolved the problem.
Regards
Vijay
DELL-Vijay B
183 Posts
0
February 28th, 2017 05:00
Thanks for taking time and updating this thread.
Regards
Vijay
kamilp
6 Posts
0
February 28th, 2017 05:00
Hello,
Finally I received zip archive with file : dbo.DeviceGroupToDevice_Update.proc
I've tested that fix on copy and it was working also applied to production and issues gone
Thanks,
Kamil