locked
KB3194588 question about sql queries to delete in susdb RRS feed

  • Question

  • https://support.microsoft.com/en-us/help/3194588/-0xc1800118-error-when-you-push-windows-10-version-1607-by-using-wsus

    Following this guide, it is supposed that running query suggest wsus is in a bad state:

    select TotalResults = Count(*)
    from tbFile
    where (IsEncrypted = 1 and DecryptionKey is NULL) or (FileName like '%14393%.esd' and IsEncrypted = 0) 


    I get 1526, so seemingly my wsus is in a bad state.

    But when I run the cleanup query, its not cleaning up anything, because of the "except select FileDigest from tbFileForRevision" part.

    So I just got 0 rows affected. I have done this for version 1511, 1607 and 1703 esd files.

    So is my SUSDB still in a bad state or is this how it is supposed to be and I can continue with the last part?
    • Edited by Neverest Wednesday, June 7, 2017 1:39 PM
    Wednesday, June 7, 2017 1:19 PM

All replies

  • Hi Neverest,

    Seems you are deleting upgrade files in the WSUS server.

    You may go on with the following steps, before re-sync the upgrade files, ensure you have installed KB3095113 and KB3159706(with manual steps), after re-sync, you may use the query to check the state of the WSUS again:

    select TotalResults = Count(*)
    from tbFile
    where (IsEncrypted = 1 and DecryptionKey is NULL) or (FileName like '%14393%.esd' and IsEncrypted = 0)

    Check if the result is "0", if yes, you may deploy the upgrade files to WSUS clients.

    Best Regards,

    Anne


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, June 8, 2017 3:17 AM
  • KB3095113 and 3159706 is installed, with manual steps done for the last one. I still got 1526 after resync. 

    Dissecting the query, I get this:

    "where (IsEncrypted = 1 and DecryptionKey is NULL)" gives 1526

    "(FileName like '%15063%.esd' and IsEncrypted = 0)" gives 0

    "(FileName like '%14393%.esd' and IsEncrypted = 0)" gives 0

    I have deployed 1703 again, after deleting the deployment package store, so it did a new download.

    On a client I have deleted the ccmcache and the "del %windir%\SoftwareDistribution\DataStore\* ". Didn't help. I still got 0xC1800118.

    I changed the query to:

    "select TotalResults = Count(*)
    from tbFile
    where (IsEncrypted = 1 and DecryptionKey is not NULL) and (FileName like '%15063%.esd' and IsEncrypted = 1)"

    Then I get 38, so unless I do something wrong, I figure I have the 1703 files with encryption key. 

    Thursday, June 8, 2017 12:57 PM
  • Hi Neverest,

    If the clients still have issues get win10 1703 upgrade from the WSUS server, I would suggest re-build the WSUS server for a quick fix.

    To remove the WSUS server:

    1. Remove WSUS role in Server management;

    2. Delete WSUS content folder and WSUS site in IIS;

    3. Delete SUSDB in C:\Windows\WID\Data, SUSDB_mdf and SUSDB.log_ldf, then use SQL server management studio to check if the SUSDB is deleted, if not, right click the SUSDB and detach it;

    Then install KB3095113 and KB3159706 on the WSUS server and restart the server, after restart, re-install the WSUS.

    Best Regards,

    Anne


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, June 15, 2017 5:58 AM