Tag Archives: sql

ConfigMgr and a backlog in distributions


Do you have a primary site and a few secondary sites in ConfigMgr 2012+?

Do you schedule the legacy Package format to update on a schedule?


Do you have a backlog in the distribution manager?

Well, so far this is known (by Microsoft) defect that apparently is yet to be fixed (until 1606 – nothing confirmed beyond that)


If you review the database where ConfigMgr resides you can see that there is a constant growing amount of DistributionJobs. Sample query to get an overview;

use <database>
select COUNT(*) from distributionjobs

The problem grows the more packages you have set to update on a schedule. The frequency of the schedule is not relevant, the package will loop into a forever updating loop. Most likely the primary site will handle this efficiently, however the sending to secondary sites will cause a backlog that is not just an annoyance but causing severe problems as the backlog will continue to grow.

Repeating this: The frequency of the schedule is not relevant. Just check the above checkbox and the issue will occur.

SQL query to locate relevant packages

use <database>
select pkg.PkgID, pkg.Manufacturer, pkg.Name, pkg.Version, pkg.Language, pkg.RefreshSchedule from SMSPackages as pkg
where datalength(pkg.RefreshSchedule) !=0


Easy – uncheck all these check-boxes that updates packages. If you still want to update packages on a schedule use a powershell script to trigger the update and use the task scheduler to run the update.

Run the command-line;

powershell -executionpolicy bypass -file SCCM.UpdatePkg.ps1 -packageid <PACKAGEID>

(I honestly don’t know if I have stolen / copied this from somewhere – if I have give me a ping and I will remove this)

# Created on: 2014-10-28 15:06
# Created by: Nicke Källén
# Organization: Applepie.se
# Filename: SCCM.UpdatePkg.ps1

Function Invoke-CMPackageUpdate
[Parameter(Mandatory=$True,HelpMessage="Please Enter Primary Server Site code")]
[Parameter(Mandatory=$True,HelpMessage="Please Enter Primary Server Name")]
[Parameter(Mandatory=$True,HelpMessage="Please Enter Package/Application ID")]

$PackageClass = [wmiclass] "\\$($siteserver)\root\sms\site_$($sitecode):SMS_Package"
$newPackage = $PackageClass.CreateInstance()

$newPackage.PackageID = $PackageID



Invoke-CMPackageUpdate -SiteCode <SITECODE> -SiteServer <SERVER> -PackageID $packageid

App-V 5.0 SP3 and remote SQL

As App-V 5.0 SP3 has been released it is not only the client that gotten an update, but the App-V Server components has gotten a fresh new installer for the first time since App-V 5.0 SP1.

If one is working in a locked down environment where the roles of server and databases are seperated, and databases might be located on a shared SQL-hotel that no ordinary administrator is allowed access to – the question that rises is how does one get the database up 2 speed?

According to the notes the only supported scenario is to run from the installer provided by App-V 5.0 SP3, and what that is can be debated, which no SQL admin would dream about.

If executing the appv_server_setup.exe with the switch /layout there will be a folder extracted named database scripts.

Looking at the contents (in databasescripts) in App-V 5.0 SP3 and comparing that to the contents of App-V 5.0 SP1 – the files look fairly much the same;


When comparing the contents of the files the following differences emerge;


CREATE TABLE dbo.PackageGroupMembers (
Id                              int PRIMARY KEY IDENTITY,
PackageGroupId                  int NOT NULL,
PackageVersionId                int NOT NULL,
LoadOrder                       int NOT NULL,
<em>PackageOptional                    bit NOT NULL DEFAULT 0,
VersionOptional                    bit NOT NULL DEFAULT 0</em>

CREATE TABLE dbo.SchemaVersion (
Version                       int NOT NULL


SELECT @minserviceversion = N'5.0.10107.0'
SELECT @dbversion  = N'5.0.10107.0'

And finally, looking at updates.sql – you can notice that there are some major changes. For example;

to begin with;

-- Replace SchemaChanges table with SchemaVersion table
RAISERROR('Removing SchemaChanges table', 0, 1) WITH NOWAIT
DROP TABLE [SchemaChanges]
INSERT INTO [SchemaVersion] VALUES (1)

and continue with;

-- Add PackageOptional and VersionOptional columns to PackageGroupMemebers table
ALTER TABLE PackageGroupMembers ADD PackageOptional bit NOT NULL DEFAULT 0, VersionOptional bit NOT NULL DEFAULT 0


-- Erase the current schema version from the SchemaVersion table (the new current version will be written during the install)
RAISERROR('Removing current schema version', 0, 1) WITH NOWAIT
DELETE FROM [SchemaVersion]

It seems that all the changes to the database are actually contained in updates.sql and createtables.sql.

It seems that the below commands are suffice. All of this is just gathered information from the Microsoft provided scripts on howto setup a clean database.

CREATE TABLE dbo.SchemaVersion (
Version                       int NOT NULL

INSERT INTO [SchemaVersion] VALUES (1)

-- Add PackageOptional and VersionOptional columns to PackageGroupMemebers table
ALTER TABLE PackageGroupMembers ADD PackageOptional bit NOT NULL DEFAULT 0, VersionOptional bit NOT NULL DEFAULT 0
-- Update SchemaVersion table to version 2
DELETE FROM [SchemaVersion]
INSERT INTO [SchemaVersion] VALUES (2)

DELETE FROM [SchemaVersion]

Once this is completed, head on over to your App-V Management Server. install the .NET Framework 4.5.1

Then install the new App-V 5.0 SP3 bits – first the Management Server. Then the Publishing server. After that you are all done!

You can verify that your servers are operational by simply accessing their respective website. if anything odd shows up – check the event logs under Microsoft\App-V