locked
tbXML grows out of control on spanish language SBS servers RRS feed

  • Question

  • I know this is an old subject - large WSUS databases. I already know about the solution of removing and putting wsus back. This is not the same issue.

    I manage quite a few SBS installations and on some of them I see a problem where the wsusdb has grown beyond its normal 2-4Gb size. Typically it will hit around 20Gb with the vast majority of this space being occupied by the tbXml table.

    These servers are always configured to only download spanish language patches. However the tbXml table contains xml details for many languages. I think all languages, though I'm not sure.

    I could remove and put back wsus in each case, but this is not going to cure the problem if wsus pulls down the same data again. It doesn't seem to be possible to just do a 'delete from tbXml where languageId=nnn' and remove the other language entries. I've tried this in the past and ended up killing sus. If I remember correctly I have reinstalled wsus completely and seen the problem come back again.

    so, anyone got any ideas as to whats going on or how to fix it?

    Friday, May 9, 2014 10:09 AM

All replies

  • Hi,

    Since you define only Spanish in update files and languages, then only Spanish should be downloaded.

    How long since you last cleanup your server? You need run the cleanup wizard regularly.

    For your information:

    Best Practices with Windows Server Update Services 3.0

    http://technet.microsoft.com/en-us/library/cc720525(v=ws.10).aspx

    Hope this helps.

    Monday, May 12, 2014 6:05 AM
  • The cleanup wizard rarely purges anything at all from the databases. I has already run it on this database without it cleaning up anything in this table. As an experiment I decided to mark the 'all languages' option and then unmark it to see if that provoked an effect.

    I left it run for 4 days but wsus was still reporting that I couldn't change any options as it was still processing a previous operation.

    so, as with the other simliar cases, I gave up, stopped the service and uninstalled wsus. I then had to reinstall and reapprove/reject everything *again*.

    This time I've taken a back up of the db so if it ever occurs again I'll just restore over the top.

    -Ian

    Tuesday, May 13, 2014 2:12 PM
  • >then only Spanish should be downloaded.

    This is definitely not true. As soon as you install wsus it marks english regardless of what you choose. You then have to reject back all the useless english language patches. Unmarking english and resyncronizing/running the wizard have no effect at all. Once they're included you have to either accept or reject the patches.

    not impressed.

    Tuesday, May 13, 2014 2:15 PM
  • Hi,

    That is correct, the reason we have to choose English language pack is that all updates are based on English language packs. So we need English pack in addition to any other language pack required I your organization.

    How many clients do you have? How many products did you define?

    I found several similar thread with SBS server, try the last post in this one:

    Susdb.mdf file too big on a Windows SBS 2008 server

    http://social.technet.microsoft.com/Forums/windowsserver/en-US/487ec289-9d75-4fba-8bc8-af50cee231cb/susdbmdf-file-too-big-on-a-windows-sbs-2008-server?forum=winserverwsus

    Hope this helps.

    Thursday, May 15, 2014 5:51 AM
  • Typically it will hit around 20Gb with the vast majority of this space being occupied by the tbXml table.

    First step is to perform standard update maintenance so that the Server Cleanup Wizard can remove the old revisions and expired updates.

    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Monday, May 19, 2014 1:03 AM
  • The cleanup wizard rarely purges anything at all from the databases.

    This is not accurate; the SCW actually purges quite a bit from the database in the form of expired update and old revisions, however, it cannot purge those updates from the database if they have approvals hanging off of them. Since this is an SBS server, which is typically managed by the use of automatic approvals, I'd place a big bet that there are thousands of legacy updates with approvals still present and preventing the SCW from deleting that metadata from the database. Decline the old superseded/expired updates and run the SCW.

    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Monday, May 19, 2014 1:05 AM
  • Hello,

    Since it has been for several days we haven't hear from you, I want to confirm if the issue has been resolved.

    If you have any question pelase feel free to let us know.

    Thank you.

    Monday, May 19, 2014 1:45 AM
  • Hi Lawrence, 

    When I said that the cleanup tool rarely does anything I meant that when I run it, it usually reports back that it has done nothing - all the counters it displays are 0. This can occur even after you have manually declined thousands of patches or even when a wsus server has not been touched in a year or more. Given the replacement rate of patches it should always report something as being purged.

    On occasion it will clear up several Gb of files, so I know it sometimes does something. Its also not very 'intelligent' about it. By this I mean that, say, approvaing SQL 2008 SP2 should make every patch which is rolled up into it, including SP1 as obsolete. It doesn't.

    Whats weirder is that, if you make a mistake and mark, say, sql2005 when you don't have that product and then you unmark it, wsus will have already pulled down the list of updates and they will stay there until you either approve them or decline them. The cleanup wizard does nothing to remove products which are unmarked. This is a problem with SBS, but its also a problem with installations where they had XP and no longer do, or they upgrade from sql 2005 to sql2012. Unmarking sql2005 has no effect, neither does running the cleanup tool. Its almost totally manual.

    When you say that 'the first step is to perform the standard update maintenance' I assume you are talking about the wsus cleanup wizard which you find in the navigation tree under options or the command line tool? 

    Over the years I've tried all sorts of things, including most of the scripts to do sql maintenance, but find that essentially I have to run through the list of patches one by one. Whenever I find products in the approved/unapproved list which shouldn't be there I run a search and decline everything with that product in the name. Its tedious...

    This posting in particular was about the huge growth in the size of the tbXml table. In this particular case I had unmarked most products - the client was tiny and only had a few Ms products. Declining the updates didn't work because wsus just timed out all the time and the  row count of tbXml never went down, even after running the cleanup tool. I could see that the table contained Xml entries for all languages - something which made no sense because not even SBS has all languages marked by default. Its not the first time I've seen this either. It normally doesn't occur, but I have seen it on moe than one sbs, so I'm not imagining it.

    In the past I haven't wasted time investigating. Wiping wsus and reinstalling has been faster, but I'd like to get to the bottom of it.

    Monday, May 19, 2014 12:43 PM
  • How many *approved* updates are there currently on the WSUS server?

    How many *total* updates are there currently on the WSUS server?


    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Wednesday, May 21, 2014 2:50 AM
  • Hello!

    I see this thread kind of died silently, but the problem is still there. Multiple instances, multiple versions (on 2003, 2008 R2, 2012 R2), but the same issue: tbXML is huge and not shrinking if I decline or remove products or classifications.

    In fact, I had 2 similar setups, more or less similar products. On one, I made a mistake to leave all languages and now my DB is almost 4 GB, where on second setup I have chosen English only and my DB is 1.2 GB. Unchecking languages later doesn’t help, I can’t cleanup the DB now, the only way now is to reinstall.

    Any updates on this one? If at least I could preserve some settings I’d rebuild WSUS again. Any way to drop some tables to forget all XP updates?

    Tuesday, August 4, 2015 4:32 PM
  • I've just noticed that Lawrence responded to me, but I didn't get that response. Odd. Apologies Lawrence.

    Igor, I would consider a 4gb db to be pretty standard. I was seeing dbs which were running over 20gb for 3-4 person outfits. The purge jobs seem to have pretty much no effect and nothing seems to ever clear down the tbXml table, which is where all the space is being consumed. 

    I never did find a solution, other than recreate the wsus db. 


    • Edited by imurphy Tuesday, September 1, 2015 10:52 AM
    Monday, August 10, 2015 4:00 PM
  • we have several sbs2008 and sbs2011 and most, if not all of them, have the same Problem (german language here).

    susdb is enormously large, 20gb being the smallest and 80gb being the biggest i have seen so far.

    wsus cleanup wizard rarely helps at all. uninstalling/installing wsus as a whole helps, but i'm tired of doing over and over again.

    i have found the following solution but i'm not an SQL expert so i don't know if this does any harm to SBS. maybe someone can confirm if this is any good:

    --------------------------------------------------

    My tblEventInstance is only 18 MB
    The whole database is 16 GB

    This script

    USE SUSDB
    GO
    exec spGetObsoleteUpdatesToCleanup

    showed 35185 obsolete updates

    I’ve been running this

    USE SUSDB
    DECLARE @var1 INT
    DECLARE @msg nvarchar(100)
    Declare @myTable table (Col1 INT)

    –CREATE TABLE #results (Col1 INT) INSERT INTO #results(Col1)
    INSERT INTO @myTable (Col1)
    EXEC spGetObsoleteUpdatesToCleanup
    –DECLARE WC Cursor FOR SELECT Col1 FROM #results order by Col1
    DECLARE WC Cursor FOR SELECT Col1 FROM @myTable order by Col1
    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

    For 10 days now.
    It doesn’t bag the server.
    It kills one obsolete update per minute or so.
    I stop the query, and close the script, and it says there are transactions to complete — which I let it do.
    Then I start the script again.

    tblXML is the huge one.
    But it is down from 12 GB to 4.5 GB with 6000 updates yet to kill
    Here are the biggies (sorry for mat issues!)
    Obtained with

    use susdb
    go

    declare @tablesize table (
    [name] nvarchar(256),
    [rows] varchar(18),
    reserved varchar(18),
    data varchar(18),
    index_size varchar(18),
    unused varchar(18)
    )

    insert @tablesize exec sp_msForEachTable ‘exec sp_spaceused [?]’
    select * from @tablesize order by data desc

    and sorted properly (text you know!) in Excel

    tblXML is stiil the largest, but coming down.

    Name rows reserved data index_size unused
    tbXml 1835011 6002536 4890440 59672 1052424
    tbPreComputedLocalizedProperty 929627 807224 601440 135208 70576
    tbLocalizedProperty 929627 634792 575232 3520 56040
    tbFile 428015 264296 262104 1376 816
    tbDeadDeployment 796316 311008 250048 55688 5272
    tbPrerequisiteDependency 3352610 362936 142464 133024 87448
    tbDeployment 534722 480432 130000 210032 140400
    tbMoreInfoURLForRevision 757282 138192 94456 21272 22464
    tbDriver 306597 168096 78992 71920 17184
    tbRevisionLanguage 2173624 118992 57392 40896 20704
    tbProperty 299684 104952 43104 23064 38784
    tbFileOnServer 428015 65360 39888 23312 2160
    tbInstalledUpdateSufficientForPrerequisite 1642946 94992 39176 29200 26616
    tbLocalizedPropertyForRevision 929590 50472 36008 400 14064
    tbRevision 299687 93112 25968 43728 23416
    tbUpdate 244978 65336 25272 24928 15136
    tbPrerequisite 1011325 63376 22472 16160 24744
    tbEventInstance 6507 36800 18864 6400 11536

    After the killing spree completes, I think the indexes and statistics will need updating.

    I use this variation on the WSUS maintenance script to just LIST what indexes need work

    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  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 = 5000 AND @fillfactorset = 0
    Print N’ALTER INDEX ‘ + @indexname + N’ ON ‘ + @schemaname + N’.’ + @objectname + N’ REBUILD WITH (FILLFACTOR = 90)’;
    ELSE
    Print 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;

    I then copy-and-paste the suggested commands in a few at a time until all the indexes have been maintenance.

    Then it’s updating stats time

    USE SUSDB
    EXEC sp_updatestats
    GO
    SELECT name AS stats_name,
    STATS_DATE(object_id, stats_id) AS statistics_update_date
    FROM sys.stats ;
    GO

    But I still don’t know if the MMC will time out after this is all done!

    --------------------------------------------------

    taken from: https://www.3ait.co.uk/blog/solved-shrink-a-wsus-database-susdb-mdb-to-almost-nothing-2/


    • Edited by InfoCN Friday, April 7, 2017 2:51 PM
    Friday, April 7, 2017 2:48 PM