Cannot reduce the size of susdb.mdf at all (WSUS 3.0 SP2)

[Jack]

Dear all,
It's good to find this newsgroup and I hope someone can shed me some
light on this problem that i have been struggling with for 2 days

SBS2K3 R2 Standard.

The susdb.mdf file took 5.7GB space on C: at the first place and the
built in WSUS 2.0 was actually not being used. I then upgraded to WSUS
3.0 SP2 (Windows Internal Database) and expected the Cleanup Wizard
would be able to reduce the size. Okay, here is what happened
afterwards
- Ran "Cleanup Wizard".
It took 24 hours to finish. However, this did not reduce the size
which was still 5.7GB
- Read tons of URLs
I realized that Cleanup Wizard would NOT actually reduce the size. I
need to "decline" unneeded updates first, then run two command tools
1. wsusutil.exe deleteunneededrevisions
2. wsusdebugtool.exe /Tool:PurgeUnneededFiles
- Declined updates
From Update Services MMC, I declined 19,000 unneeded updates
- Ran "wsusutil.exe deleteunneededrevisions"
This did not run at all because the parameter
"deleteunneededrevisions" has been invalid from WSUS 3.0 SP2 while it
only worked with WSUS 2.0.
Read lots of URLs again. I realized that this command could be
replaced by the "Cleanup Wizard". I also realized that there was a sql
maintenance script (Re-indexing) that I should run to optimize
susdb.mdf
- Ran the sql maintenance script
Quick and successful
- Ran the Cleanup Wizard
Quick and successful
- Ran "wsusdebugtool.exe /Tool:PurgeUnneededFiles"
Message said "Starting a state machine reset". Then, after a few
minutes "State machine reset completed"

After all the effort, the result is that susdb.mdf is still 5.7GB, not
even a single byte is reduced !!

Did i miss anything? Please help!

Thanks
jack

[Lawrence Garvin [MVP]]

Post by Jack
After all the effort, the result is that susdb.mdf is still 5.7GB, not
even a single byte is reduced !!
Did i miss anything?

Yes... "SQL Server database administration 101"... :-)

These tools will not *reduce* the physical size of a database, they will
only manipulate the data contained within the database.

What you really need to know is how much of that 5.7gb file is actually
being used,
and if appropriate, "Shrink" the file -- using SQL Server Management Studio.

Alternatively, granting that a 5.7gb database is probably excessive for an
SBS-server environment, and is likely an artifact of having a previously
installed instance of WSUS v2, you might also consider simply uninstalling
WSUS, removing the database, and installing a fresh instance of WSUS v3.
It'll take a bit more time, but you'll have a much more efficient database
as a result.

[Jack]

Post by Lawrence Garvin [MVP]

Post by Jack
After all the effort, the result is that susdb.mdf is still 5.7GB, not
even a single byte is reduced !!
Did i miss anything?

Yes... "SQL Server database administration 101"... :-)
These tools will not *reduce* the physical size of a database, they will
only manipulate the data contained within the database.
What you really need to know is how much of that 5.7gb file is actually
being used,
and if appropriate, "Shrink" the file -- using SQL Server Management Studio.
Alternatively, granting that a 5.7gb database is probably excessive for an
SBS-server environment, and is likely an artifact of having a previously
installed instance of WSUS v2, you might also consider simply uninstalling
WSUS, removing the database, and installing a fresh instance of WSUS v3.
It'll take a bit more time, but you'll have a much more efficient database
as a result.
--
Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2010)
My Blog:http://onsitechsolutions.spaces.live.com
Microsoft WSUS Website:http://www.microsoft.com/wsus
My MVP Profile:http://mvp.support.microsoft.com/profile/Lawrence.Garvin

Thanks Lawrence for the reply!

I actually did installed SSMSE 2005 that I forgot to mention. And i
connected to the WID successfully. (I ran the re-index script from
within the "inquery" successfully) However, I could not find something
like "Shrink" in SSMSE. Would it be because it was "Studio Express" or
it was WID?

I did some research before upgrading to WSUS 3.0. It seemed that
uninsalling WSUS 2.0 was not suggested because it was part of SBS R2.
That's why I did the upgrade rather than a fresh install of 3.0

Thanks
jack

[Jack]

Post by Lawrence Garvin [MVP]

Post by Jack
After all the effort, the result is that susdb.mdf is still 5.7GB, not
even a single byte is reduced !!
Did i miss anything?

Yes... "SQL Server database administration 101"... :-)
These tools will not *reduce* the physical size of a database, they will
only manipulate the data contained within the database.
What you really need to know is how much of that 5.7gb file is actually
being used,
and if appropriate, "Shrink" the file -- using SQL Server Management Studio.
Alternatively, granting that a 5.7gb database is probably excessive for an
SBS-server environment, and is likely an artifact of having a previously
installed instance of WSUS v2, you might also consider simply uninstalling
WSUS, removing the database, and installing a fresh instance of WSUS v3.
It'll take a bit more time, but you'll have a much more efficient database
as a result.
--
Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2010)
My Blog:http://onsitechsolutions.spaces.live.com
Microsoft WSUS Website:http://www.microsoft.com/wsus
My MVP Profile:http://mvp.support.microsoft.com/profile/Lawrence.Garvin

Hey Lawrence! I found "Shrink" in SSMSE! It is doing the job now. It
seems that i will get 46% of 5.7GB released.

Thanks a lot!
jack

[Lawrence Garvin [MVP]]

"Jack" <***@gmail.com> wrote in message news:2d442915-b779-4071-ad2d-***@g8g2000pri.googlegroups.com...

Hey Lawrence! I found "Shrink" in SSMSE! It is doing the job now. It
seems that i will get 46% of 5.7GB released.

Excellent!

[Jack]

Post by Jack
Hey Lawrence! I found "Shrink" in SSMSE! It is doing the job now. It
seems that i will get 46% of 5.7GB released.
Excellent!
--
Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2010)
My Blog:http://onsitechsolutions.spaces.live.com
Microsoft WSUS Website:http://www.microsoft.com/wsus
My MVP Profile:http://mvp.support.microsoft.com/profile/Lawrence.Garvin

Hi Lawrence,

SUSDB.MDF shrinked from 5.7Gb to 2.4GB !
Today I delined ALL updates. I then ran WsusDebugTool and reindex
script. Then I did "shrink" in SSMSE again, however the db still
remains at 2.4GB. Do you happen to have any idea on this as well?

Thanks
jack

[Lawrence Garvin [MVP]]

Post by Jack
SUSDB.MDF shrinked from 5.7Gb to 2.4GB !
Today I delined ALL updates. I then ran WsusDebugTool and reindex
script. Then I did "shrink" in SSMSE again, however the db still
remains at 2.4GB. Do you happen to have any idea on this as well?

Well, first, as previously noted, and discussed elsewhere, the WSUSDebugTool
is not appropriate for use in a WSUS v3 environment.

Second.. declining updates and reindexing isn't going to "remove" anything
from the database at all.

The only way to remove update metadata from the content is by using the
Server Cleanup Wizard "Delete expired and revised updates" option; but even
running that option will have an insignificant impact on the size
utilization of the database. Furthermore, you do not want to continuously
try to shrink the database, nor maximize the shrinkage, as it will just
cause the database to have to re-grow the next time it needs more space --
which will adversely affect performance.

Shrinking a database to reduce the file size to 50% of the original is a
Good Thing.
Trying to shrink it to reduce the file size by 1% is purposeless.

[Jack]

Post by Lawrence Garvin [MVP]

Post by Jack
SUSDB.MDF shrinked from 5.7Gb to 2.4GB !
Today I delined ALL updates. I then ran WsusDebugTool and reindex
script. Then I did "shrink" in SSMSE again, however the db still
remains at 2.4GB. Do you happen to have any idea on this as well?

Well, first, as previously noted, and discussed elsewhere, the WSUSDebugTool
is not appropriate for use in a WSUS v3 environment.
Second.. declining updates and reindexing isn't going to "remove" anything
from the database at all.
The only way to remove update metadata from the content is by using the
Server Cleanup Wizard "Delete expired and revised updates" option; but even
running that option will have an insignificant impact on the size
utilization of the database. Furthermore, you do not want to continuously
try to shrink the database, nor maximize the shrinkage, as it will just
cause the database to have to re-grow the next time it needs more space --  
which will adversely affect performance.
Shrinking a database to reduce the file size to 50% of the original is a
Good Thing.
Trying to shrink it to reduce the file size by 1% is purposeless.
--
Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2010)
My Blog:http://onsitechsolutions.spaces.live.com
Microsoft WSUS Website:http://www.microsoft.com/wsus
My MVP Profile:http://mvp.support.microsoft.com/profile/Lawrence.Garvin

thanks a lot Lawrence, i will leave it as is for now