WSUS 3, empty sql datafile due to tbXml table

[joker197cinque]

I have a WSUS 3 installation on a SQL Express 2005.

Datafile of SUSDB is near to 3GB and I am planning to shrink it.

How can I safely delete info/history ?
From WSUS console ? From SQL ?

Thanks.

[Lawrence Garvin [MVP]]

Post by joker197cinque
I have a WSUS 3 installation on a SQL Express 2005.
Datafile of SUSDB is near to 3GB and I am planning to shrink it.
How can I safely delete info/history ?
From WSUS console ? From SQL ?

Shrinking the SUSDB is not documented, nor is it likely to have any lasting
value.

History data in tables is managed by internal procedures and accessing those
procedures directly is not a supported activity.

Standard database maintenance operations do apply, though, so for db
maintenance, refer to SQL Server 2005 Books Online if you really do wish to
attempt a "shrink" of the database, but since the majority of space in the
database is client reporting data, it's likely to come right back.

(Note: One way you can reduce the amount of data is to reduce your client
detection frequency. If clients are detecting/reporting excessively, there's
excessive redundant event entries in the database that are providing no
value.)

Note: Your database size. This is the Number One reason why using SQL Server
Express Edition is *not* recommended for use with WSUS and exactly why you
*should* use the Windows Internal Database. Also, you'll find that database
performance with SQL Server Express Edition is less than what is available
with WID, because SQLExpress is also limited to use of one CPU core, and 1GB
RAM for buffering.

I would recommend doing the following:
(rather than investing futile time trying to shrink a continually growing
database -- 3GB is a *normal* size for a WSUS database in a small to midsize
organization; larger databases are not uncommon)


1. Make sure you are properly and regularly running the Server Cleanup
Wizard.

2. If you are, or running the SCW has no appreciable impact on the growth of
your database, then migrate your WSUS database back to the Windows Internal
Database, using the following:

a. Uninstall WSUS (keep the databases, content, and logs).
b. Move the SUSBD.mdf and SUSDB_log.ldf files to a safe location.
c. Uninstall SQL Server 2005 Express Edition (unless something else is
using it besides WSUS).
d. Reinstall WSUS, but select the option to use the Windows Internal
Database. This will install a fresh (empty) copy of the WSUS database in
~\WSUS\UpdateServicesDBFiles.
e. After installation, stop the Update Services service.
f. Using the procedures documented in the Operations Guide
[http://technet.microsoft.com/en-us/library/dd939918(WS.10).aspx], detach
the fresh (empty) copy of the WSUS database, and rename (or move) the
SUSDB.mdf and SUSDB_log.ldf files to reflect their status as fresh/empty
database files.
f. Move your SQL Express WSUS database to the
~\WSUS\UpdateServicesDBFiles folder.
g. Reattach the SUSDB database to the Windows Internal Database service.
h. Restart the Update Services service.

Now you have no size concerns on the database.

Post by joker197cinque
Thanks.