First published on TECHNET on Oct 02, 2014
https://blogs.technet.microsoft.com/umairkhan/2014/10/02/the-case-of-the-unexplained-the-package-get...
Hi Folks,
Lately was looped in an issue where we target a DP to the package and from the DistMgr.log it shows 2301 for the package being successfully send to the DP and verified in the ContentLib too but does not show in the console. It stays 0.
I’ve a key created before on the info of the flow in background. There are 4 main Global tables which are responsible for other updates on other tables via Trigger –
1. SMSPackages_G
|
[tr_SMSPackages_ContentNotification] -> ContentDistribution
|
[tr_ContentDistribution_Notification] -> ContentDistributionNotification
|
[tr_ContentDistributionNotification_Summarizer] -> ContentDistributionMessages
2. PkgServers_G
|
[ tr_PkgServers_G_ContentNotification ] –>ContentDistributionNotification -[ tr_ContentDistributionNotification_insert ] –>ContentDistribution
–>[Cont] ContentDistribution
|
[ tr_ContentDistributionNotification_Summarizer ] –> ContentDistributionMessages
|
[tr_ContentDistribution_Notification] -> ContentDistributionNotification
3. PkgStatus_G | [tr_PkgStatus_G_ContentNotification]-> ContentDistribution | [tr_ContentDistribution_Notification] -> ContentDistributionNotification | [tr_ContentDistributionNotification_Summarizer] -> ContentDistributionMessages
4. DistributionStatus | [ tr_DistributionStatus_ContentNotification ] -> ContentDistributionMessages
- Use the 2 nd one above for this case as we are tracking the DP targeting.
Now in our case, I see the entry in the PkgServers_G table for the DP being added.
Checking for the package
select * from smspackages where pkgid = 'TX1000AD'
select * from PkgServers_G where pkgid = 'TX1000AD'
select * from ContentDistributionNotification where pkgid = 'TX1000AD'
select * from ContentDistribution where pkgid = 'TX1000AD'
- So I see the ContentDistributionNotification has the value but ContentDistribution table does not.
- So the insert trigger [ tr_ContentDistributionNotification_insert ] on the ContentDistributionNotification table is not running.
- Got a snippet of the trigger [see below] and modified it to return and check if that has the value and yes we do see the value, so if this trigger was run it would have send the updates to the other tables.
SELECT ins.DistributionID,
ins.DPID,
ins.PkgID
AS PkgID,
ISNULL(ver.AvailableVersionState, 0) AS PkgSourceState,
0
AS
PkgServerAction, /* will be updated after this trigger */
GETUTCDATE() AS PkgServerLastRefresh, /* will be updated after this trigger
*/
ISNULL(s.Status, 0) AS PkgStatusStatus,
ISNULL(s.SourceVersion, 0) AS PkgStatusSourceVersion,
ISNULL(s.UpdateTime, GETUTCDATE()) AS PkgStatusUpdateTime,
ISNULL(v.DistributedVersionState, 0) AS DPReportedState,
ISNULL(v.DistributedVersion, 0) AS DPReportedVersion,
ISNULL(v.DistributedVersionTime, GETUTCDATE()) AS DPTimeLastReported
FROM ContentDistributionNotification ins
JOIN DistributionPoints dp on dp.DPID = ins.DPID
LEFT JOIN SMSPackages_G pkg on pkg.PkgID = ins.PkgID
LEFT JOIN DistributionContentVersion ver ON ver.PkgID = ins.PkgID AND
pkg.SourceSite = ver.SiteCode AND ver.DPNALPath = N''
LEFT JOIN PkgStatus_G s ON s.PkgServer = dp.NALPath and s.ID = ins.PkgID
LEFT JOIN DistributionContentVersion v ON v.DPNALPath = dp.NALPath and
v.PkgID = ins.PkgID
where ins.PkgID = 'TX1000AD'
- Confirmed it by running a Profiler that the Insert Trigger never ran.
- Ran the Insert Trigger Manually. And now the Data came in the ContentDistribution.
- But still the Data is not reflecting in the Console.
- Upon Verification found that the 'StateGroup' for the working is 1 in the ContentDistributionNotifications Table. But in our case it is 0.
- Found that there is Trigger for the ContentDistribution table [ tr_ContentDistribution_Notification ] which updates that in the ContentDistributionNotification table. And that also did not run.
- Ran the same manually and the StateGroup changed to 1.
- The console now shows the same as installed.
- So not sure why the insert trigger did not work for -
1. ContentDistributionNotification
2. ContentDistribution
- One thing which is now known to us is that only the first trigger on the PkgServers_G ran and after that none of the triggers ran. Rings a bell!!! :)
- The setup has not worked from August and upon checking found that the SQL was upgraded to SQL 2012. So chances are that the SQL properties could be modified.
- Followed the link - http://msdn.microsoft.com/en-us/library/ms190946(v=sql.110).aspx and checked the option for
1.) Server trigger recursion and 2.) Nested triggers which was OFF .
- Set it on by running:
sp_configure 'show advanced options' , 1
reconfigure with override
sp_configure 'server trigger recursion' , 1
reconfigure with override
sp_configure 'nested triggers' , 1
reconfigure with override
Checked now and the issue got fixed!! :)
Hope it helps! Do keep working and sharing!
Umair Khan
Support Escalation Engineer | Microsoft System Center Configuration Manager
Disclaimer: This posting is provided "AS IS" with no warranties and confers no rights.