{"id":180,"date":"2014-12-25T15:44:40","date_gmt":"2014-12-25T15:44:40","guid":{"rendered":"http:\/\/www.torontohelpdesk.ca\/blog\/?p=180"},"modified":"2014-12-25T15:44:52","modified_gmt":"2014-12-25T15:44:52","slug":"shrinking-wsus-database-susdb","status":"publish","type":"post","link":"https:\/\/www.torontohelpdesk.ca\/blog\/shrinking-wsus-database-susdb\/","title":{"rendered":"Shrinking WSUS Database (susdb)"},"content":{"rendered":"

Problem:<\/strong><\/p>\n

Your susdb.dbf is huge. You’re maybe running out of space.<\/p>\n

Solution:<\/strong><\/p>\n

(h\/t 3aIT<\/a>)<\/p>\n

WSUS is both a very useful beast and a wild one: It allows you to manage a huge number of Windows workstations to keep your PCs updated and your network secure, yet it itself can be quite hard to manage.<\/p>\n

It has built in clean-up tools for removing old, obsolete or superseded patches, but nothing to help you manage the database itself, which can grow to quite an alarming size.<\/p>\n

This is how to properly clean up a WSUS server that is critically out of space, and reduce its database size by almost 90%.<\/p>\n

1. See if you can reclaim some space using conventional means<\/h3>\n

The first you may know that your server is in trouble is that it only has a few meg free and some services are failing. \u00a0WSUS, by default, places its database and downloaded files on your system drive, which can leave your server in a critical state.<\/p>\n

You can use a tool like\u00a0CCleaner<\/a>\u00a0to remove temporary files and old server system updates to give yourself some breathing space.<\/p>\n

2. Use the WSUS Cleanup Tool to remove updates that are no longer needed<\/h3>\n

Under \u201cOptions\u201d in the WSUS admin tool, you can find the cleanup wizard. This does not necessarily remove disk files you would expect and can need some encouragement by manually \u201cunapproving\u201d updates under \u201cAll Updates\u201d. Follow the steps\u00a0here<\/a>\u00a0to deselect updates you don\u2019t ever want and remove all updates from disk. \u00a0By removing all updates from disk and running a reset, it will start downloading those updates again; this delete is nothing to worry about.<\/p>\n

It would be best to stop the WSUS service after this step.<\/p>\n

3. Stop the WSUS service<\/h3>\n

The service is stopped via the IIS Manager, as it runs as a web site.<\/p>\n

4. Taming the database \u2013 installing the right tools<\/h3>\n

By this point, you will hopefully have recovered a few gig of disk space. In my case, I still had the problem of the WSUS Database itself being 30Gb. \u00a0This was after a couple of years of use with maybe 50 PCs \u2013 so a fairly small site.<\/p>\n

The database in WSUS is an embedded SQL Server database (SQL Server Express).\u00a0It can be managed via SQL Server Management Studio. \u00a0You can download this for free from\u00a0here<\/a>.<\/p>\n

Note: There are many versions of Management Studio.\u00a0After many problems downloading versions that couldn\u2019t be installed on this particular server due to its particular configuration, version, patch level etc, I found Management Studio 2005 worked a treat. \u00a0Your mileage may vary and it is worth persevering until you find an appropriate working version.<\/strong><\/p>\n

5. Taming the database \u2013 connecting to the .mdb file<\/h3>\n

You can now connect to the WSUS .mdb file by opening SQL Server Management Studio and entering in to the \u201cServer\u201d box:<\/p>\n

\\\\.\\pipe\\MSSQL$MICROSOFT##SSEE\\sql\\query<\/p><\/blockquote>\n

Use \u201cWindows Authentication\u201d.<\/p>\n

If this goes well, you should be looking at a standard database view, with a list of tables on the left and an info window on the right.<\/p>\n

You are unlikely to be able to simply shrink the database and recover any space this way. First we need to free up some space by deleting redundant data.<\/p>\n

6. Deleting redundant data<\/h3>\n

WSUS logs everything it does. \u00a0Over time, this can eat a lot of space in the database with surprisingly few workstations.<\/p>\n

The logs live in the table \u201ctbEventInstance\u201d.<\/p>\n

Delete all of these like this:<\/p>\n

    \n
  1. Click on the database name \u2018SUSDB\u2019<\/li>\n
  2. Click \u2018New Query\u2019<\/li>\n
  3. Type \u2018truncate table tbEventInstance\u2019 in to the Query Editor<\/li>\n
  4. Click the \u2018Execute\u2019 button \u2013 or press F5.<\/li>\n<\/ol>\n

    This removed 90% of the data in the database (several million rows) for me and did not affect WSUS operation or administration, apart from to speed up certain administration views which used old event data.<\/p>\n

    If you are concerned, and have the means, it would be prudent to back this file up first.\u00a03aIT can accept no liability for loss or damage to your systems resulting from not taking the appropriate precautions. To put that another way: This worked for me, your mileage may vary.<\/p>\n

    The file itself defaults to living here:<\/p>\n

    C:\\WSUS\\Database\\UpdateServicesDbFiles<\/p><\/blockquote>\n

    If you\u2019re less cavalier than me, and care about reporting on recent activity, you can substitute a \u201cdelete from \u2026 where\u201d for the \u201ctruncate\u201d above, and delete only messages older than a week or two.<\/p>\n

    7.\u00a0Shrink the DB file<\/h3>\n

    This is a slow step which you may want to run overnight. Where data has been deleted from the database, this will not be reflected in the file size until the file is shrunk. It is now effectively full of holes and needs defragging.<\/p>\n

    Do this:<\/p>\n

      \n
    1. Right click on the database name (\u201cSUSDB\u201d) on the left<\/li>\n
    2. From \u2018Tasks\u2019, select \u2018Shrink\u2019=>\u2019Files\u2019<\/li>\n
    3. In the wizard, change the \u201cShrink Action\u201d from \u201cRelease Unused Space\u201d to \u201cReorganise pages before releasing unused space. \u00a0This takes the holes out of the file.<\/li>\n
    4. Change the number in the \u201cShrink File To\u201d box to match the number it tells you the minimum can be. \u00a0In my case, it told me it could shrink it to 2Gb (from just over 30Gb)<\/li>\n
    5. Click \u201cOK\u201d and wait for a very long time<\/li>\n<\/ol>\n

      8. Restart WSUS and check all is well<\/h3>\n

      The next day, you should find you have a file on disk that is approximately the same size you told Management Studio to shrink it to.<\/p>\n

      Restart WSUS via the IIS Manager.\u00a0Check you can connect to it from the WSUS admin tool. Run any updates and do any approvals, then run the \u201creset\u201d command again from\u00a0here<\/a>\u00a0to persuade it to re-download any updates it needs.<\/p>\n

      If you think the database is going to grow out of hand again quite quickly, consider moving it to somewhere better than your system drive. Here<\/a>\u00a0are some reasonable instructions on how to do this using the sqlcmd.exe commandline tool to detach and reattach a database.\u00a0If you don\u2019t have this installed, you can run the SQL commands through Management Studio, or use \u201cDetach\u201d \/ \u201cReattach\u201d from the \u201cTasks\u201d context menu.<\/p>\n","protected":false},"excerpt":{"rendered":"

      Problem: Your susdb.dbf is huge. You’re maybe running out of space. Solution: (h\/t 3aIT) WSUS is both a very useful beast and a wild one: It allows you to manage a huge number of Windows workstations to keep your PCs Continue reading Shrinking WSUS Database (susdb)<\/span>→<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[81],"tags":[65,66,16],"class_list":["post-180","post","type-post","status-publish","format-standard","hentry","category-wsus","tag-susdb","tag-susdb-dbf","tag-wsus"],"_links":{"self":[{"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/posts\/180","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/comments?post=180"}],"version-history":[{"count":2,"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/posts\/180\/revisions"}],"predecessor-version":[{"id":182,"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/posts\/180\/revisions\/182"}],"wp:attachment":[{"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/media?parent=180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/categories?post=180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/tags?post=180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}