locked
Timeouts in the GUI, Powershell scripts & GUI 'All Computers' crash RRS feed

  • Question

  • I've posted a few times asking for advice after suffering GUI & Powershell timeouts when using the standard PS scripts in order to clean/tidy the WSUS DB.

    However this post is meant to offer a few key answers in order to help others and share my knowledge.  Finally I've managed to tame the DB in order to display All Updates (+27,000), run clean up scripts without timeouts and view All Computers without the GUI crashing.

    unable to connect to the WSUS console.  Please find the error below.

    Error: Connection Error

    An error occurred trying to connect the WSUS server.  The error can happen for a number of reasons.  Check connectivity with the server.  Please contact your network administrator if the problem persists

    Click Reset server node to try to connect to the server again.

    1.  Address IIS default limitations

    Make the following "Advanced Settings" for WSUS Application Pool in IIS:    

    - Queue Length: 25000 from 1000    

    - Limit Interval (minutes): 15 from 5    

    - "Service Unavailable" Response: TcpLevel from HttpLevel * (Stop IIS first) Edit the web.config ( C:\Program Files\Update Services\WebServices\ClientWebService\web.config ) for WSUS:    

    - Replace <httpRuntime maxRequestLength="4096" /> with <httpRuntime maxRequestLength="204800" executionTimeout="7200"/> <add key="maxInstalledPrerequisites" value="400"/> change to 800 *

    Adjust the app pool private memory limit.    

    - If you have WSUS Automated Maintenance (WAM), from the WAM Shell run: .\Clean-WSUS.ps1 -SetApplicationPoolMemory 4096 - If you don't have WAM, edit the pool's configuration directly to change it to 4194304 (4GB)

    Some also recommend changing pool memory to O i.e no memory limit

    2. SQL - how to connect to WSUS WID DB

    The internal WID SQL DB can be accessed  from local (maybe remote also) SQL Management Tools  via this computer name: \\.\pipe\MICROSOFT##WID\tsql\query

    If that fails make sure you run management tools using Admin and:

    np:\\.\pipe\MICROSOFT##WID\tsql\query

    Open as Domain Admin or any member of the server Admin group

    3.  Timeouts  - time to clean the SUS DB using TSQL

    The internal WID SQL DB can be access from local 2012 Management Tools (needs installing) via this computer name: \\.\pipe\MICROSOFT##WID\tsql\query

    If that fails make sure you run management tools using Admin and:

    np:\\.\pipe\MICROSOFT##WID\tsql\query

    Open as Domain Admin or any member of the server Admin group

    3  Re-index the SUS DB

    /****************************************************************************** 
    
    This sample T-SQL script performs basic maintenance tasks on SUSDB 
    
    1. Identifies indexes that are fragmented and defragments them. For certain 
    
       tables, a fill-factor is set in order to improve insert performance. 
    
       Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx 
    
       and tailored for SUSDB requirements 
    
    2. Updates potentially out-of-date table statistics. 
    
    ******************************************************************************/ 
    
     
    
    USE SUSDB; 
    
    GO 
    
    SET NOCOUNT ON; 
    
     
    
    -- Rebuild or reorganize indexes based on their fragmentation levels 
    
    DECLARE @work_to_do TABLE ( 
    
        objectid int 
    
        , indexid int 
    
        , pagedensity float 
    
        , fragmentation float 
    
        , numrows int 
    
    ) 
    
     
    
    DECLARE @objectid int; 
    
    DECLARE @indexid int; 
    
    DECLARE @schemaname nvarchar(130);  
    
    DECLARE @objectname nvarchar(130);  
    
    DECLARE @indexname nvarchar(130);  
    
    DECLARE @numrows int 
    
    DECLARE @density float; 
    
    DECLARE @fragmentation float; 
    
    DECLARE @command nvarchar(4000);  
    
    DECLARE @fillfactorset bit 
    
    DECLARE @numpages int 
    
     
    
    -- Select indexes that need to be defragmented based on the following 
    
    -- * Page density is low 
    
    -- * External fragmentation is high in relation to index size 
    
    PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)  
    
    INSERT @work_to_do 
    
    SELECT 
    
        f.object_id 
    
        , index_id 
    
        , avg_page_space_used_in_percent 
    
        , avg_fragmentation_in_percent 
    
        , record_count 
    
    FROM  
    
        sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f 
    
    WHERE 
    
        (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1) 
    
        or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0) 
    
        or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0) 
    
     
    
    PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20)) 
    
     
    
    PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121) 
    
     
    
    SELECT @numpages = sum(ps.used_page_count) 
    
    FROM 
    
        @work_to_do AS fi 
    
        INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
    
        INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
    
     
    
    -- Declare the cursor for the list of indexes to be processed. 
    
    DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do 
    
     
    
    -- Open the cursor. 
    
    OPEN curIndexes 
    
     
    
    -- Loop through the indexes 
    
    WHILE (1=1) 
    
    BEGIN 
    
        FETCH NEXT FROM curIndexes 
    
        INTO @objectid, @indexid, @density, @fragmentation, @numrows; 
    
        IF @@FETCH_STATUS < 0 BREAK; 
    
     
    
        SELECT  
    
            @objectname = QUOTENAME(o.name) 
    
            , @schemaname = QUOTENAME(s.name) 
    
        FROM  
    
            sys.objects AS o 
    
            INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id 
    
        WHERE  
    
            o.object_id = @objectid; 
    
     
    
        SELECT  
    
            @indexname = QUOTENAME(name) 
    
            , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END 
    
        FROM  
    
            sys.indexes 
    
        WHERE 
    
            object_id = @objectid AND index_id = @indexid; 
    
     
    
        IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0) 
    
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; 
    
        ELSE IF @numrows >= 5000 AND @fillfactorset = 0 
    
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)'; 
    
        ELSE 
    
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; 
    
        PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command; 
    
        EXEC (@command); 
    
        PRINT convert(nvarchar, getdate(), 121) + N' Done.'; 
    
    END 
    
     
    
    -- Close and deallocate the cursor. 
    
    CLOSE curIndexes; 
    
    DEALLOCATE curIndexes; 
    
     
    
     
    
    IF EXISTS (SELECT * FROM @work_to_do) 
    
    BEGIN 
    
        PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20)) 
    
        SELECT @numpages = @numpages - sum(ps.used_page_count) 
    
        FROM 
    
            @work_to_do AS fi 
    
            INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
    
            INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
    
     
    
        PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20)) 
    
    END 
    
    GO 
    
     
    
     
    
    --Update all statistics 
    
    PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)  
    
    EXEC sp_updatestats 
    
    PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)  
    
    GO 


    4 - Some useful Information

    TSQL

    --get the count of total updates, superseded ,declined updates.

    use SUSDB; select (Select count (*) 'Total Updates' from vwMinimalUpdate ) 'Total Updates', (Select count (*) 'Live updates'  from vwMinimalUpdate where declined=0) as 'Live Updates', (Select count (*) 'Superseded'  from vwMinimalUpdate where IsSuperseded =1) as 'Superseded', (Select count (*) 'Superseded But NoDeclined'  from vwMinimalUpdate where IsSuperseded =1 and declined=0) as 'Superseded but not declined', (Select count (*) 'Declined'  from vwMinimalUpdate where declined=1) as 'Declined', (Select count (*) 'Superseded & Declined' from vwMinimalUpdate where IsSuperseded =1 and declined=1) 'Superseded & Declined'

    Total Updates: count of all updates which includes superseded ,decline .This basically include all updates in your wsus db.

    Live updates: Count of updates without declined .This includes all updates with superseded/without superseded but not declined. These updates are considered to generate the update catalog file.

    Superseded: Count of all superseded updates

    Superseded but not declined: Count of all superseded updates but they are not declined yet.

    Declined:Count of updates that are declined. Declined updates never goes into update catalog file .


    5.  Decline those pesky superseded updates

    PS scripts to perform this decline on superseded updates would regular timeout, so we have to perform the action on the DB directly using TSQL

    Set @testRun to 1 to test without declining anything

    -- Decline superseded updates in SUSDB; 
    
    
    
    USE SUSDB
    
    
    
    DECLARE @thresholdDays INT = 90 -- Specify the number of days between
    today and the release date for which the superseded updates must not be
    declined (i.e., updates older than 90 days). This should match configuration of
    supersedence rules in SUP component properties, if ConfigMgr is being used with
    WSUS.
    
    DECLARE @testRun BIT = 0 -- Set this to 1 to test without declining
    anything.
    
    -- There shouldn't be any need to modify anything after this line.
    
    
    
    DECLARE @uid UNIQUEIDENTIFIER
    
    DECLARE @title NVARCHAR(500)
    
    DECLARE @date DATETIME
    
    DECLARE @userName NVARCHAR(100) = SYSTEM_USER
    
    
    
    DECLARE @count INT = 0
    
    
    
    DECLARE DU CURSOR FOR
    
             SELECT MU.UpdateID,
    U.DefaultTitle, U.CreationDate FROM vwMinimalUpdate MU
    
             JOIN PUBLIC_VIEWS.vUpdate
    U ON MU.UpdateID = U.UpdateId
    
    WHERE MU.IsSuperseded = 1 AND MU.Declined = 0 AND MU.IsLatestRevision =
    1
    
             AND MU.CreationDate <
    DATEADD(dd,-@thresholdDays,GETDATE())
    
    ORDER BY MU.CreationDate
    
    
    
    PRINT 'Declining superseded updates older than ' + CONVERT(NVARCHAR(5),
    @thresholdDays) + ' days.' + CHAR(10)
    
    
    
    OPEN DU
    
    FETCH NEXT FROM DU INTO @uid, @title, @date
    
    WHILE (@@FETCH_STATUS > - 1)
    
    BEGIN
    
             SET @count = @count + 1
    
             PRINT 'Declining update '
    + CONVERT(NVARCHAR(50), @uid) + ' (Creation Date ' + CONVERT(NVARCHAR(50),
    @date) + ') - ' + @title + ' ...'
    
             IF @testRun = 0
    
                     EXEC
    spDeclineUpdate @updateID = @uid, @adminName = @userName, @failIfReplica = 1
    
             FETCH NEXT FROM DU INTO
    @uid, @title, @date
    
    END
    
    
    
    CLOSE DU
    
    DEALLOCATE DU
    
    
    
    PRINT CHAR(10) + 'Attempted to decline ' + CONVERT(NVARCHAR(10), @count)
    + ' updates.'


    6.  Delete Obsolete updates

    Another PShell function which regularly timed out, again let's use TSQL directly on the SUS DB - it wont timeout no matter how long it runs for!

    USE SUSDB
    
    IF object_id('tempdb..#MyTempTable') is not null
    DROP TABLE #MyTempTable
    
    create table #MyTempTable (
    
    LocalUpdateID int
    
    )
    
    IF object_id('tempdb..#MyTempTable1') is not null
    DROP TABLE #MyTempTable1
    
    create table #MyTempTable1 (
    
    LocalUpdateID int
    
    )
    
    GO
    
    insert INTO #MyTempTable1 (LocalUpdateID)
    
    EXEC susdb.dbo.spGetObsoleteUpdatesToCleanup
    
    select * from #MyTempTable
    
    insert into #MyTempTable select top (2000) * from
    #MyTempTable1
    
    DECLARE @x INT
    
    DECLARE @Msg VARCHAR(50)
    
    DECLARE @Count INT
    
    SELECT @Count = COUNT(*) FROM #MyTempTable
    
    SELECT @msg = 'Number of updates to be deleted:' +
    CAST( @Count AS VARCHAR(10))
    
    RAISERROR(@msg, 0, 1) WITH NOWAIT
    
    declare c1 cursor local static for
    
    select * from #MyTempTable
    
    open c1
    
    fetch c1 into @x
    
    while @@FETCH_STATUS = 0
    
    begin
    
    SELECT @msg = 'Deleting update with ID:' + CAST (@x
    AS VARCHAR(10))
    
    RAISERROR(@msg, 0, 1) WITH NOWAIT
    
    EXEC spDeleteUpdate @localUpdateID=@x
    
    fetch c1 into @x
    
    END
    
    close c1
    
    deallocate c1
    
    SELECT @msg = 'Deletion completed'
    
    RAISERROR(@msg, 0, 1) WITH NOWAIT 
    
    
    
    

    7.  Using the GUI and opening 'All Computers' crashes with Reset Error message

    Make sure https://support.microsoft.com/en-us/help/4511553/windows-10-update-kb4511553 is installed.

    Clear out suspect Computers via SQL Management Tools

    Open SUSDB

    Navigate to > Tables > tbComputerTarget Right click Edit Top 200 rows, or if you need more rows, right click on an entry returned from the 200 row > Pane > SQL:

    SELECT        TOP (2000) TargetID, ComputerID, SID, ParentServerTargetID, LastSyncTime, LastReportedStatusTime, LastReportedRebootTime, IPAddress, FullDomainName, IsRegistered, LastInventoryTime, LastNameChangeTime,

                             EffectiveLastDetectionTime, LastSyncResult

    FROM            tbComputerTarget

    ParentServerTargetID should all be NULL, if 2 or any other select them and delete.

    Or this also apparently works:

    update tbComputerTarget set ParentServerTargetID = NULL where ParentServerTargetID is NOT NULL

    Ref: 

    https://support.microsoft.com/en-us/help/4490644/complete-guide-to-microsoft-wsus-and-configuration-manager-sup-maint

    http://eskonr.com/2019/06/clean-up-your-wsus-database-for-better-performance-and-sccm-software-update-compliance/



    • Edited by LeaUK Wednesday, June 17, 2020 12:06 PM
    Wednesday, June 17, 2020 12:01 PM

All replies

  • Hi,

    Thanks for your sharing, I admire your technical ability and this post could be very useful to a lot of people.

    Thanks for your time.

    Best regards,
    Amanda You

    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 18, 2020 6:30 AM