none
tbEventInstance table is very big from SUSDB RRS feed

  • Question

  • We have noticed that one of the SUSDB used in our environment with SCCM is of very big size (150GB +) and tbEventInstance table is utilizing 140GB space. 

    Please suggest.


    Prashant Patil

    Wednesday, August 9, 2017 11:35 AM

All replies

  • Have a peek at my Adamj Clean-WSUS script. It is the last WSUS Script you will ever need.

    http://community.spiceworks.com/scripts/show/2998-adamj-clean-wsus

    What it does:

    1. Remove all Drivers from the WSUS Database.
    2. Shrink your WSUSContent folder's size by declining superseded updates.
    3. Remove declined updates from the WSUS Database.
    4. Clean out all the synchronization logs that have built up over time (configurable, with the default keeping the last 14 days of logs).
    5. Compress Update Revisions.
    6. Remove Obsolete Updates.
    7. Computer Object Cleanup (configurable, with the default of deleting computer objects that have not synced within 30 days).
    8. Application Pool Memory Configuration to display the current private memory limit and easily increase it by any configurable amount.
    9. Run the Recommended SQL database Maintenance script on the actual SQL database.
    10. Run the Server Cleanup Wizard.

    It will email the report out to you or save it to a file, or both.

    Although the script is lengthy, it has been made to be super easy to setup and use. There are some prerequisites and instructions at the top of the script. After installing the prerequisites and configuring the variables for your environment, simply run:

    .\Clean-WSUS.ps1 -FirstRun

    and then

    .\Clean-WSUS.ps1 -InstallTask

    If you wish to view or increase the Application Pool Memory Configuration, you must run it with the required switch. See Get-Help .\Clean-WSUS.ps1 -Examples

    If you're having trouble, there's also a -HelpMe option that will create a log so you can send it to me for support.

    Adam Marshall, MCSE: Security
    http://www.adamj.org

    Thursday, August 10, 2017 4:27 AM
  • My script takes care of your issue with Item #4

    4. Clean out all the synchronization logs that have built up over time (configurable, with the default keeping the last 14 days of logs).


    Adam Marshall, MCSE: Security
    http://www.adamj.org

    Thursday, August 10, 2017 4:31 AM
  • Thanks Adam for sharing your script.

    Is it safe to truncate tbEventInstance table & shrink the DB as quick fix till the time we get necessary approvals to implement the suggested script.


    Prashant Patil

    Thursday, August 10, 2017 5:30 AM
  • I'm not entirely positive that it wont have adverse effects.

    If you want a quick fix, the SQL Script I'm using to rid that table is

    Read through and replace $variables because it's meant to be used through my script.

    ################################
    #  Adamj WSUS Synchronization  #
    #      Cleanup SQL Script      #
    #       Version 1.0            #
    #  Taken from various sources  #
    #      from the Internet.      #
    #                              #
    #  Modified By: Adam Marshall  #
    #     http://www.adamj.org     #
    ################################
    */
    $(
        if ($ConsistencyNumber -ne "0") {
        $("
    USE SUSDB
    GO
    DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389') AND DATEDIFF($($ConsistencyTime), TimeAtServer, CURRENT_TIMESTAMP) >= $($ConsistencyNumber);
    GO")
    }
    elseif ($All -ne $False) {
    $("USE SUSDB
    GO
    DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389')
    GO")
    }
    )

    If you want to execute my script just for this part, use:

    .\Clean-WSUS.ps1 -CleanUpWSUSSynchronizationLogs

    It will clean up by default (configurable in the config) everything except the last 14 days of logs.

    ################################
    #        Clean Up WSUS         #
    #     Synchronization Logs     #
    #           Variables          #
    ################################
    
    # Clean up the synchronization logs older than a consistency.
    
    # (Valid consistency number are whole numbers.)
    [int]$AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber = "14"
    
    # Valid consistency time are "Day" or "Month"
    [String]$AdamjCleanUpWSUSSynchronizationLogsConsistencyTime = "Day"
    
    # Or remove all synchronization logs each time
    [boolean]$AdamjCleanUpWSUSSynchronizationLogsAll = $False


    Adam Marshall, MCSE: Security
    http://www.adamj.org

    Thursday, August 10, 2017 12:56 PM
  • Thanks again Adam.

    We have noticed that almost 95% entries in tbeventinstance table are against event id 148, which will not be deleted through your script.

    Please help us to understand these event ids in details & consequences of the deleting entries against them


    Prashant Patil

    Friday, August 11, 2017 5:49 AM
  • That would be a question directly for Microsoft :)

    Adam Marshall, MCSE: Security
    http://www.adamj.org

    Friday, August 11, 2017 1:00 PM