locked
Database Error when running cleanup. RRS feed

  • Question

  • Hi there.  I've done a fair amount of research and attempted multiple suggestions from other threads about this issue but nothing has worked so I'm hoping someone can provide me with some information about the Database Error message I'm getting when running Server Cleanup Wizard in WSUS version 6.3.9600.18838.  I can run all the options except the first one and get this error when running it.  Help would really be appreciated.  Thanks.

    FIRST ERROR in EVENT LOG    

    The WSUS administration console was unable to connect to the WSUS Server Database.

    Verify that SQL server is running on the WSUS Server. If the problem persists, try restarting SQL.

    System.Data.SqlClient.SqlException -- Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    SECOND ERROR in EVENT LOG

    The description for Event ID 0 from source WSusCertServer cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

    Tuesday, January 8, 2019 3:05 PM

Answers

All replies

  • Hi Fuzzy,

    since you're getting a timeout at that step, you can run the SQL query from the "HELP! My WSUS has been running for years without ever having maintenance done and the cleanup wizard keeps timing out." section of the complete maintenance blog:
    https://blogs.technet.microsoft.com/configurationmgr/2016/01/26/the-complete-guide-to-microsoft-wsus-and-configuration-manager-sup-maintenance/

    This will take care of most updates that need to be deleted and afterwards the cleanup wizard should run as expected.

    Best regards,
    Andrei


    We could change the world, if God would give us the source code.

    Tuesday, January 8, 2019 4:25 PM
  • Thanks for this fast reply!  I really appreciate it. I will go visit that link and give it a go and report back.  Thank you so much!
    Tuesday, January 8, 2019 4:26 PM
  • Hi Fuzzy,

    since you're getting a timeout at that step, you can run the SQL query from the "HELP! My WSUS has been running for years without ever having maintenance done and the cleanup wizard keeps timing out." section of the complete maintenance blog:
    https://blogs.technet.microsoft.com/configurationmgr/2016/01/26/the-complete-guide-to-microsoft-wsus-and-configuration-manager-sup-maintenance/

    This will take care of most updates that need to be deleted and afterwards the cleanup wizard should run as expected.

    Best regards,
    Andrei


    We could change the world, if God would give us the source code.

    Alright, well I didn't get very far on that page :). First, I'd prefer not to do reinstall WSUS because I do not know what is involved in that.  Second, I don't know how to re-index :(  If you can help me with that I'll give it a go.  Really appreciate your help!
    Tuesday, January 8, 2019 4:29 PM
  • That link has Microsoft's official guide - it's has a lot of moving parts in it and you have to know how to connect to the SQL Database in order to do it. Either by command line or by GUI.

    If you go to the bottom section of my re-installation guide - https://www.ajtek.ca/wsus/how-to-remove-wsus-completely-and-reinstall-it/ - you can get the instructions for the GUI (SSMS) to connect to the Windows internal database, and then create a new query and paste in the contents of the re-indexing script from Microsoft (https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61) and click Execute.

    If you want to re-install WSUS - by all means use my guide to do so. Then, on setup, follow my 8 part blog series on How to Setup, Manage, and Maintain WSUS - https://www.ajtek.ca/wsus/how-to-setup-manage-and-maintain-wsus-part-1-choosing-your-server-os/


    Adam Marshall, MCSE: Security
    https://www.ajtek.ca
    Microsoft MVP - Windows and Devices for IT

    • Marked as answer by FuzzyReets Thursday, January 10, 2019 2:02 PM
    Wednesday, January 9, 2019 3:05 AM
  • Hello,
     
    If you have not maintained the WSUS for a long time, query and delete unused updates and update revisions would take lots of time and cause the timeout error. Here are two methods you could try. However, both need you to connect the WID via SQL Server Management Studio (SSMS) or its express edition. You should run it with administrator right and use \\.\pipe\MICROSOFT##WID\tsql\query as the server name.
     
    1> Right click on the server name, select Properties/Connections, set timeout from remote query timeout from 600 to 0 (= no timeout). Then run the server cleanup wizard again and wait for the results. It would take lots of time to complete.
     

     
    2> We could run the following query in the SSMS to delete the unused updates and update revisions directly.
     
    DECLARE @var1 INT
    DECLARE @msg nvarchar(100)
    
    CREATE TABLE #results (Col1 INT)
    INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup
    
    DECLARE WC Cursor
    FOR
    SELECT Col1 FROM #results
    
    OPEN WC
    FETCH NEXT FROM WC
    INTO @var1
    WHILE (@@FETCH_STATUS > -1)
    BEGIN SET @msg = 'Deleting' + CONVERT(varchar(10), @var1)
    RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID=@var1
    FETCH NEXT FROM WC INTO @var1 END
    
    CLOSE WC
    DEALLOCATE WC
    
    DROP TABLE #results
     
    It will also take a lot of time. When it is finished try server cleanup wizard again and check the results.
     

     
    Hope my answer could help you and look forward to your feedback.
     
    Best Regards,
    Ray

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

    Wednesday, January 9, 2019 5:43 AM
  • Hello,
     
    If you have not maintained the WSUS for a long time, query and delete unused updates and update revisions would take lots of time and cause the timeout error. Here are two methods you could try. However, both need you to connect the WID via SQL Server Management Studio (SSMS) or its express edition. You should run it with administrator right and use \\.\pipe\MICROSOFT##WID\tsql\query as the server name.
     
    1> Right click on the server name, select Properties/Connections, set timeout from remote query timeout from 600 to 0 (= no timeout). Then run the server cleanup wizard again and wait for the results. It would take lots of time to complete.
     

     
    2> We could run the following query in the SSMS to delete the unused updates and update revisions directly.
     
    DECLARE @var1 INT
    DECLARE @msg nvarchar(100)
    
    CREATE TABLE #results (Col1 INT)
    INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup
    
    DECLARE WC Cursor
    FOR
    SELECT Col1 FROM #results
    
    OPEN WC
    FETCH NEXT FROM WC
    INTO @var1
    WHILE (@@FETCH_STATUS > -1)
    BEGIN SET @msg = 'Deleting' + CONVERT(varchar(10), @var1)
    RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID=@var1
    FETCH NEXT FROM WC INTO @var1 END
    
    CLOSE WC
    DEALLOCATE WC
    
    DROP TABLE #results
     
    It will also take a lot of time. When it is finished try server cleanup wizard again and check the results.
     

     
    Hope my answer could help you and look forward to your feedback.
     
    Best Regards,
    Ray

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


    Thank you for this reply!  I tried the first idea but that didn't work.  Running the query from the second idea and still going after an hour so that's good I think.  Running this on my test export of this VM in HyperV on an NVME drive so it should hopefully be faster than it would be in a standard HDD RAID.  I will post back.  Thanks!
    Wednesday, January 9, 2019 6:14 PM
  • That link has Microsoft's official guide - it's has a lot of moving parts in it and you have to know how to connect to the SQL Database in order to do it. Either by command line or by GUI.

    If you go to the bottom section of my re-installation guide - https://www.ajtek.ca/wsus/how-to-remove-wsus-completely-and-reinstall-it/ - you can get the instructions for the GUI (SSMS) to connect to the Windows internal database, and then create a new query and paste in the contents of the re-indexing script from Microsoft (https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61) and click Execute.

    If you want to re-install WSUS - by all means use my guide to do so. Then, on setup, follow my 8 part blog series on How to Setup, Manage, and Maintain WSUS - https://www.ajtek.ca/wsus/how-to-setup-manage-and-maintain-wsus-part-1-choosing-your-server-os/


    Adam Marshall, MCSE: Security
    https://www.ajtek.ca
    Microsoft MVP - Windows and Devices for IT


    Thanks for your reply Adam.  I actually saw it after I scrolled up from the more recent post.  I will try that if the other stuff doens't work.  Thank you!
    Wednesday, January 9, 2019 6:15 PM
  • Hello,
     
    After it finishes, it is suggested to re-index the WSUS database. Just refer to the following article and do not forget to change the server name in the command. It is also recommended to reindex the WSUS database at least once a month.
     
    Reindex the WSUS Database
    https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/dd939795(v%3dws.10)
       
    The article and the script may be a little old but they are still useful.
     
    Hope my answer could help you and look forward to your feedback. 
    Best Regards,
    Ray

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

    Thursday, January 10, 2019 2:16 AM
  • Fuzzy, now you will have to be patient with the query, as it can take several hours to complete if it is the first time you run it.

    Example from my WSUS server when I first ran the cleanup (with just the first option):

    The Cleanup Wizard in my case did not time out, but as you can see below it took 7 hours to complete:

    C:\Program Files\Update Services\LogFiles\SoftwareDistribution.log:

    2014-12-17 13:38:56.058 UTC       Info        mmc.236              CleanupManager.PerformCleanup          PerformCleanup(): Start CleanupObsoleteUpdates.

    2014-12-17 14:52:37.008 UTC       Info        mmc.236              CleanupManager.PerformCleanup          PerformCleanup(): End CleanupObsoleteUpdates: 1815 updates are deleted

    2014-12-17 14:52:39.101 UTC       Info        mmc.236              CleanupManager.PerformCleanup          PerformCleanup(): Start CompressUpdates.

    2014-12-17 21:58:13.504 UTC       Info        mmc.236              CleanupManager.PerformCleanup          PerformCleanup(): End CompressUpdates: 16605 updates are compressed

    Hope this helps,
    Andrei


    We could change the world, if God would give us the source code.

    Thursday, January 10, 2019 8:05 AM
  • That link has Microsoft's official guide - it's has a lot of moving parts in it and you have to know how to connect to the SQL Database in order to do it. Either by command line or by GUI.

    If you go to the bottom section of my re-installation guide - https://www.ajtek.ca/wsus/how-to-remove-wsus-completely-and-reinstall-it/ - you can get the instructions for the GUI (SSMS) to connect to the Windows internal database, and then create a new query and paste in the contents of the re-indexing script from Microsoft (https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61) and click Execute.

    If you want to re-install WSUS - by all means use my guide to do so. Then, on setup, follow my 8 part blog series on How to Setup, Manage, and Maintain WSUS - https://www.ajtek.ca/wsus/how-to-setup-manage-and-maintain-wsus-part-1-choosing-your-server-os/


    Adam Marshall, MCSE: Security
    https://www.ajtek.ca
    Microsoft MVP - Windows and Devices for IT

    Hi Adam.  This totally worked.  I copied the string from the first link to connect to the Windows DB then ran the command in the second link.  After that I was able to run the first option in the Cleanup Wizard which was successful for the first time then I ran the other 4 and got back about 100GB.  Thank you!
    Thursday, January 10, 2019 2:02 PM
  • Fuzzy, now you will have to be patient with the query, as it can take several hours to complete if it is the first time you run it.

    Example from my WSUS server when I first ran the cleanup (with just the first option):

    The Cleanup Wizard in my case did not time out, but as you can see below it took 7 hours to complete:

    C:\Program Files\Update Services\LogFiles\SoftwareDistribution.log:

    2014-12-17 13:38:56.058 UTC       Info        mmc.236              CleanupManager.PerformCleanup          PerformCleanup(): Start CleanupObsoleteUpdates.

    2014-12-17 14:52:37.008 UTC       Info        mmc.236              CleanupManager.PerformCleanup          PerformCleanup(): End CleanupObsoleteUpdates: 1815 updates are deleted

    2014-12-17 14:52:39.101 UTC       Info        mmc.236              CleanupManager.PerformCleanup          PerformCleanup(): Start CompressUpdates.

    2014-12-17 21:58:13.504 UTC       Info        mmc.236              CleanupManager.PerformCleanup          PerformCleanup(): End CompressUpdates: 16605 updates are compressed

    Hope this helps,
    Andrei


    We could change the world, if God would give us the source code.

    Yup that is around what I had.  Lots of stuff cleaned up.  Thanks!
    Thursday, January 10, 2019 2:03 PM
  • Hello,
     
    If you have not maintained the WSUS for a long time, query and delete unused updates and update revisions would take lots of time and cause the timeout error. Here are two methods you could try. However, both need you to connect the WID via SQL Server Management Studio (SSMS) or its express edition. You should run it with administrator right and use \\.\pipe\MICROSOFT##WID\tsql\query as the server name.
     
    1> Right click on the server name, select Properties/Connections, set timeout from remote query timeout from 600 to 0 (= no timeout). Then run the server cleanup wizard again and wait for the results. It would take lots of time to complete.
     

     
    2> We could run the following query in the SSMS to delete the unused updates and update revisions directly.
     
    DECLARE @var1 INT
    DECLARE @msg nvarchar(100)
    
    CREATE TABLE #results (Col1 INT)
    INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup
    
    DECLARE WC Cursor
    FOR
    SELECT Col1 FROM #results
    
    OPEN WC
    FETCH NEXT FROM WC
    INTO @var1
    WHILE (@@FETCH_STATUS > -1)
    BEGIN SET @msg = 'Deleting' + CONVERT(varchar(10), @var1)
    RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID=@var1
    FETCH NEXT FROM WC INTO @var1 END
    
    CLOSE WC
    DEALLOCATE WC
    
    DROP TABLE #results
     
    It will also take a lot of time. When it is finished try server cleanup wizard again and check the results.
     

     
    Hope my answer could help you and look forward to your feedback.
     
    Best Regards,
    Ray

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

    As I mentioned I had started this second option you provided but realized that's actually cleaning it up.  I wanted to get it to the point so I could use the gui to run the cleanups going forward so I stopped it and the other stuff worked.  Thank you for the help though!
    Thursday, January 10, 2019 2:09 PM
  • I'm having a lot of this errors with the purge script:

    Deleting693699
    Msg 50000, Level 16, State 1, Procedure spDeleteRevision, Line 33
    @errorMessage
    Msg 50000, Level 16, State 1, Procedure spDeleteUpdate, Line 72
    spDeleteUpdate got error from spDeleteRevision
    Deleting693698

    Is this normal?

    Wednesday, April 15, 2020 2:37 PM