Today’s annoyance started earlier this week when I happened to notice that the server I run WSUS 3.0 SP2 on at home was starting to run a little low on disk space. A few minutes of checking revealed that yes, it was the WSUS directory that was the culprit (at over 120 GB). No worries, thought I. I’ll just run the WSUS server cleanup wizard and all will be well.
Checking on the server a couple hours after firing off the wizard revealed that very little progress had been made. The progress bar had moved perhaps 3% towards completion, and seemed to be hung on “deleting unused updates”. I thought that, perhaps the process was hung, perhaps something was holding a file open, or perhaps the server hiccuped. I stopped the process, rebooted the server, started it again, and went to bed.
By the next afternoon there was more progress – to perhaps 10 or 11%. Since I’m fairly patient, the server wasn’t in immediate danger of running out of space, and the process was progressing, I decided to wait it out. Four days later the process aborted at just under 60% completion.
OK, the “wait it out” method didn’t seem to be working. A few quick Google searches revealed many admins recommending that you run the cleanup wizard often (weekly) to prevent just such an occurrence caused by an overly large WSUS file store, and correspondingly large database. Thanks guys.
Since the “cleanup everything” method didn’t seem to be working, I tried individual options in the cleanup wizard to see what would work. “Decline superseded updates” worked without error. “Decline expired updates” and “Delete computers not contacting the server” also executed quickly and flawlessly. “Delete unneeded update files” took about 40 minutes, but it also executed, freeing up about 6 GB of space in the process.
Since the issue seemed to be one of efficiency (server was running too slowly to execute such a large process), I went looking to see what I could to to either have it not work as hard, or have less to do.
That in mind, I attacked was the disk itself. This server has been running for about 3 years now, and since my home sever supports a whopping 4 users, it doesn’t get a lot of preventative maintenance or performance tuning. WSUS uses the Windows Internal Database (formerly SQL Server 2005 Embedded) as the back end engine, and I’de never given it any attention. First, I took a look at the database files on the disk. I discovered the database was about 10 GB, and the log was almost as large at 8 GB. This made sense as again, the files had been growing on demand for nearly 3 years.
I downloaded SQL Server Management Studio Express to take a look at the utilization of the files. You can download the version for 2005, but I went ahead with 2008 R2 instead to stay closer to current. Installing the software was no problem, and then I just needed to connect to the database engine.
Once you open the management studio, there are 2 caveats to connecting to the internal database engine. First, the only configuration for connectivity is named pipes, so the server name needs to be in the format of: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query. Then, you need to be logged in as a server administrator and use Windows Authentication. You’ll get a logon failed error unless you execute the console with a “run as administrator”.
Once I had access to the database engine, I selected the WSUS database, and shrank the log and database files. I reclaimed 60% from the database and 97% from the log. Since autogrowth had over time created fragmentation and diminished performance, I then used SQL Server Configuration Manager to stop the Windows Internal Database, in preparation of defragging the server.
With SQL stopped, I also stopped the IIS Web Server, and the Update Services. WIth everything related to WSUS offline, I then defragged the disk with Defraggler.
Defragmentation took most of a day, after which I restarted the server. Upon restart I re-ran the wizard only selecting “Unused updates and update revisions”. The process still took about 4 hours to run, but it did finish, and freed up about 70 GB of space.
I’ll be automating the cleanup wizard tasks in the future to avoid having this happen again…