[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1829
  • Last Modified:

Need Help Shrinking WSUS 3.0 file on SBS2003

Hello,

I am running SBS2003 Standard Edition SP2.  My network is very small - just three clients.  One part of my backup strategy is daily backups of SBS using the built-in backup software to a Dell removable hard drive.  The problem is that these daily files are too large (about 22GB).  I can longer fit five of these files, along with my file backups (roughly 65GB using BackUp Exec) on one removable hard drive.

I have moved the WSUS 3.0 files (roughly 12GB worth) on another partition using wsusutil.exe.  I checked my C drive and found the SUSDB.mdf file that is 5.2GB in size.  If I understand correctly, this one file is almost 25% of my daily backups.  I have read that this database file can be re-indexed/compacted to less than half this size.  I do not understand the instructions in the other posts well enough to implement them.

If I could reduce this one file down to 2.5GB in size, for instance, it would reduce my backup space requirements by 13GB.  This would be a HUGE help in my media rotation plan.

Note: I do not have SQL Server Management Studio Express installed on this server.  If you think I need it, please provide a link to the correct version.  

Also, besides shrinking the file, is there some way I can tell the SBS backup utility to simply skip the file?  My idea is that I have one scheduled event for Mondays when I do a full backup including SUSDB.mdf.  Then a second scheduled event for Tues/Wed/Thur/Frid backs up SBS while skipping the one file.  This could save another 10GB or more.  Or would this mess me up if I ever needed to run a restore?

Thanks so much!
0
MrChip2
Asked:
MrChip2
  • 4
  • 2
2 Solutions
 
MrChip2Author Commented:
Wow Matt,

That was fast!  I have a few questions:  

1. How do I triple check that I have WSUS 3.0 and not 2.0?  I want to be sure I don't create a huge problem.

2. How do I check if I am running Windows Internal Database?  I had an IT consultant initially configure the server three years ago.  He is no longer available to ask.

3. I have never used T-SQL or Visual Basic before.  The page on your link shows many lines of Visual Basic.  Is that something that appears when I run the script or do I need to do something in VB?

4. It says "Note: Microsoft SQL Server 2005 Command Line Query Utility requires Microsoft SQL Server Native Client, also available on this page."  Do I also need to install this or is it already on my system?

5. It says I need to run the following command:

sqlcmd –I -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query –i <scriptLocation>\WsusDBMaintenance.sql

This is completely Greek to me.  Is there any way I can give you file /directory information so you could tell me exactly what I need to type?  Is this risky if you have never used T-SQL before?

Sorry for the all questions. :)
0
 
MrChip2Author Commented:
Matt,

I need to sign off for 4 hours of so.  Thanks again for your help.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
connectexCommented:
1. How do I triple check that I have WSUS 3.0 and not 2.0?  I want to be sure I don't create a huge problem.

The version of "Windows Server Updates Services" should be listed under Control Panel->Add/Remove Programs. The first version of SBS to have WSUS included was SBS 2003 R2. So unless he installed it ad hoc it should be 3.0.

2. How do I check if I am running Windows Internal Database?  I had an IT consultant initially configure the server three years ago.  He is no longer available to ask.

The internal database is the default installation for WSUS. For an SBS installation it's doubtful they used SQL server. But the point is sqlcmd.exe won't be already installed. So try it running it from a command prompt. If it's not found then you'll need to install it.

3. I have never used T-SQL or Visual Basic before.  The page on your link shows many lines of Visual Basic.  Is that something that appears when I run the script or do I need to do something in VB?

Actually it's a SQL script. You just need to open notepad and paste the lines in to it. Then save it under the name WsusDBMaintenance.sql.

4. It says "Note: Microsoft SQL Server 2005 Command Line Query Utility requires Microsoft SQL Server Native Client, also available on this page."  Do I also need to install this or is it already on my system?

It should be already installed.

5. It says I need to run the following command:

sqlcmd –I -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query –i <scriptLocation>\WsusDBMaintenance.sql

You need to open a command prompt and type (or paste) the line in to it. Just make sute you update <scriptlocation> to the right location where you stored the WsusDBMaintenance.sql file.



0
 
MrChip2Author Commented:
Hi Matt,

Sorry for the long delay in responding.  Thank you again - we are getting close.  I confirmed that I have WSUS 3.2 installed.  I then created the WsusDBMaintenance.sql file in a text editor per your instructions and added in the location of the file in the script.  I then ran a search for the sqlcmd.exe file on my server's root directory.  It found the file in five locations!

1. c:\windows\SQL9_KB948109_ENU
2. c:\windows\SQL9_KB970892_ENU
3. c:\windows\SQLTools9_KB948109_ENU
4. c:\windows\SQLTools9_KB970892_ENU
5. c:\program files\microsoft sql server\90\tools\bin

I opened a command prompt on location #5, I copied the whole long string above, pasted it, and got an error message that SQLCMD '-I" Unknown option.  

Is it possible that my sqlcmd that was already installed is an older version?

Am I supposed to type in something different at the cmd prompt or copy the sqlcmd file to the same folder of the database I am trying to reindex?

Sorry for being such a newbie with this stuff!
0
 
MrChip2Author Commented:
Hi Matt,

I have been keeping this question open because other things have prevented me from trying out your solution.  I hope to try it out in the next week or so, but decided to close the question.  I will post a follow-up on the results.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now