Today I was at one of my customers and looked at an issue that my Clean up Powershell script was not working anymore. While troubleshooting I noticed that the script was exited with a Quota Violation error, since they also has some WMI issues since a power outage a couple of weeks ago I thought it was a WMI issue like described here.
When running the script with different maximum query result limits taught me that when using a limit of 400 or less that the script was running fine. In the figure below you see that the script fails with unlimited, maximum of 1000 or a maximum of 500 query results. The first two tries failed with the Quota Violation error and the last with a SQL error about the fact that the SQL statements is nested to deeply.
Leaving my script aside and trying it with only using it with the Get-CMDevice cmdlet again trying it with the maximum query limit of 500 items resulted in the following error:
“PS CEN:> Set-CMQueryResultMaximum 500
PS CEN:> Get-CMDevice
WARNING: The result set exceeded the maximum size. Only first 500 items used. You can use Set-CMQueryResultMaximum to change the maximum.
Get-CMDevice : ConfigMgr Error Object: instance of __ExtendedStatus
{ Description = “[42000][191][Microsoft][SQL Server Native Client 11.0][SQL Server]Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.”;
Operation = “ExecQuery”;
ParameterInfo = “SELECT * FROM SMS_CombinedDeviceResources WHERE (ResourceID=16779172 OR ResourceID=16779174 OR ResourceID=16779827 OR ResourceID=16780129 OR ResourceID=16780130 OR ResourceID=16780132 OR ResourceID=16780133 OR ResourceID=16780134 OR ResourceID=16780136 OR ResourceID=16780137 OR ResourceID=16780139 OR ResourceID=16780140 OR ResourceID=16780141 OR ResourceID=16780144 OR ResourceID=16780145 OR ResourceID=16780147 OR……………………………….etc etc”
Setting the Query Result Maximum to 400, allowed me to run the script and the Get-CMDevice cmdlet. Strange…
After looking at the logs and events the SP1 Cumulative Update 1 came up as the last change before the script stopped working. So after removing the Configuration Manager 2012 SP1 Cumulative Update 1 for the Console, which also holds the extra cmdlets, the cleanup script works again. So if you run into issues with Powershell cmdlets in Configuration Manager 2012 SP1 Cumulative Update 1, try to uninstall the console fix.
I have CU2 Installed and I am experiencing this issue. Have you found a workaround yet as this is an excellent script and I thank you for it.
Havent checked it yet. I will let you know when I was able to check it.