locked
How to connect to the SUSDB to clean it up for KB2720211 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Good Day,

    Before I begin I need to explain one thing I am NOT a SQL guy I know very little about it.  With that being said here is my problem and I hope somebody can help me through this.

    I have a SCE 2010 setup which has WSUS on it.  Running on Windows 2008R2 SP1  I have found the artical about how to prep the database before installing the update.  After a lot of talking I was able to find WSUS so I can lauch that and go into it so I can run the clean up wizard. (Have not done this yet as I want to clean up the database first)  My problem is connecting to the database.  We are using a internal database and after some looking around I found the database called SUSDB with SUSDB_log too.

    When I go into SQL Management Studio (it says SQL Server 2008) I click on compact and I type in the following path

    C:\Program files\Microsoft SQL Server\MSSQL10.ESSENTIALS\MSSQL\Data\SUSDB

    This is the path that it shows when I look at the databaswe from explorer to get to the database.  The database is there in the data folder

    When I try to connect I get the following error

    Cannot connect to C:\Program files\Microsoft SQL Server\MSSQL10.ESSENTIALS\MSSQL\Data\SUSDB

    Additional Information

    The database file cannot be found, check the path to the database [Data Source = C:\Program files\Microsoft SQL Server\MSSQL10.ESSENTIALS\MSSQL\Data\SUSDB]

    (SQL Server Compact ADO.NET Data Provider)

    I am not even sure if I have the right password but right now I can't even connect to the database to even try.

    So what I need is help in typing in the path for the database.  Do I need to put something in quotes? Am I missing something in the path?

    Any help or insight would be greatly appreciated

    Thank You

    Adam Raff

    Friday, August 3, 2012 2:39 PM

Answers

  • Question
    Sign in to vote
    2
    Sign in to vote

    Hi,

    If you are using Windows Internal Database for the WSUS database, you need to use the sqlcmd utility. For more information about the sqlcmd utility, see sqlcmd Utility.

    To use this script with Windows Internal Database, you should run the following command:

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

    Reindex the WSUS Database:http://technet.microsoft.com/en-us/library/dd939795(v=ws.10)

    Sql forum:http://social.msdn.microsoft.com/Forums/en/category/sqlserver

    regards,

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, August 14, 2012 8:07 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I found out that it is not a internal database but 2008 database that comes with WSUS.

    The Windows Internal Database is the "2008 database that comes with WSUS".

    Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
    Product Manager, SolarWinds
    Microsoft MVP - Software Distribution (2005-2012)
    My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin

    • Marked as answer by adamraff Tuesday, September 18, 2012 4:59 PM
    Friday, August 24, 2012 12:57 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    so I can run the clean up wizard. (Have not done this yet as I want to clean up the database first)

    The Server Cleanup Wizard IS the tool for cleaning up the database.

    My problem is connecting to the database.

    Connecting to the Windows Internal Database requires the use of a Named Pipes connection. The connection string you want is:

    \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

    Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
    Product Manager, SolarWinds
    Microsoft MVP - Software Distribution (2005-2012)
    My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin

    • Proposed as answer by anybody.else Friday, October 20, 2017 6:47 AM
    Friday, August 3, 2012 5:40 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Garvin,

    Thank YOu so much for replying.  As I said before not familar with SQL so I need to take this in little steps if you do not mind.

    I am assumiing that you enter the following string

    \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

    in SQL Management Studio under the path for the database.  Is that correct?

    Friday, August 3, 2012 10:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Good Day,

    At this point I am not sure what I am doing wrong please see below.  Just to make this clear I am not even getting to the point of authentication yet I can't even connect to the database.

    I tried you pip command in SQL Server 2008 and I got the following

    Server type SQL Server compact Edition

    Database file \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

    Authentication SQL Server Compact Edition Authentication

    I typed in the password that I think that it is

    It’s response was the following

    Cannot connect to \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query.

    SQL server compact does not support opening database files on a netowrk share.  (System.windows.Forms

    I the decided to use the other method which is using Database Engine

    Under Server Name  I used the same command that I used above

    Authentication SQL Server Authentication

    SU

    password

     

    Connect  to \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

    A network related or instance-specific error occurred while establishing a connection to SQL Server.  the server was not found or was not accessible.  Verify that the instance name is correct and that the SQL server is configured to allow remote connections. (provider: Named Pipes Provider. Eror: 40 - Could no open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

    Not sure where to even go from here should I take this to the SQL forum?

    thank You

    Adam Raff

    Monday, August 6, 2012 1:26 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank YOu so much for replying.  As I said before not familar with SQL so I need to take this in little steps if you do not mind.

    I am assumiing that you enter the following string

    \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

    in SQL Management Studio under the path for the database.  Is that correct?

    What kind of DB did you use when you set up WSUS? If you use the internal DB like you said above,then you didn't do anything in the management studio,and i never suggest user who didn't familiar with the DB to change anything by the management studio.To clean it up for kb2720211,pls refer to  http://blogs.technet.com/b/sus/archive/2012/06/20/wsus-kb2720211-common-issues-encountered-and-how-to-fix-them.aspx

    Also note that:You can't use SQL authentication with WSUS, which supports only windows authentication.For more info:http://technet.microsoft.com/en-us/library/cc708452(v=ws.10)

    Using the Wsus doesn't need to do more concerns about the DB configuration.Other details about SQL server,the best resource should be the SQL forum:)


    regards,

    Clarence
    Technet subscriber support
    If you are Technet subscription user and have any feedback on our support quality,pls send your feed back here.

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.




    Wednesday, August 8, 2012 6:47 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I would like to confirm what is the current situation? If there is anything that I can do for you, please do not hesitate to let me know, and I will be happy to help.

    Regards,

    Clarence

    TechNet Subscriber Support

    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    .

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Monday, August 13, 2012 3:37 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I still have the problem and to be honest I am just stuck on how to connect to the database to run the script  I have read the artical that people put out and I have looked over what you said but in all of this it does not say where to enter the path.

    I am running SCE 2010 the database is internal I see the SUSDB in the directory.

    I know that I need to enter a script but I am not sure where to enter the script so that I can reindex the WSUS database there is a script and it looks like I need to excute it from a command so I am assuming that I need to get into SQL Managment studio then excute the script.  After more digging I found the following

    I need sqlcmd which needs to be installed as I do not see it on the server.  I need to run sqlcmd which or so I think to run the script.  I tried to install that but now it says

    "Missing Native client" and it won't let me install.

    So there you go hope you can point me in the right direction at this time.

    thanks

    Adam

    Monday, August 13, 2012 6:20 PM
  • Question
    Sign in to vote
    2
    Sign in to vote

    Hi,

    If you are using Windows Internal Database for the WSUS database, you need to use the sqlcmd utility. For more information about the sqlcmd utility, see sqlcmd Utility.

    To use this script with Windows Internal Database, you should run the following command:

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

    Reindex the WSUS Database:http://technet.microsoft.com/en-us/library/dd939795(v=ws.10)

    Sql forum:http://social.msdn.microsoft.com/Forums/en/category/sqlserver

    regards,

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, August 14, 2012 8:07 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I found out that it is not a internal database but 2008 database that comes with WSUS.  I was able t connect and applied the script I posted that in another group becuse I got some errors and want to make sure that it stil worked

    Thanks for your help.

    Adam Raff

    Thursday, August 23, 2012 7:42 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I found out that it is not a internal database but 2008 database that comes with WSUS.

    The Windows Internal Database is the "2008 database that comes with WSUS".

    Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
    Product Manager, SolarWinds
    Microsoft MVP - Software Distribution (2005-2012)
    My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin

    • Marked as answer by adamraff Tuesday, September 18, 2012 4:59 PM
    Friday, August 24, 2012 12:57 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
     

    I wanted to take the time to let you know that we are up and running again.  I was able to get into the database via the following method

    login Computername\instance

    That got me in without an issue.  I then copied the script using the copy command the copy appeared to add stuff to the script.  I entered everything in by hand and ran the script that way and that worked.  Then ran the WSUS cleanup wizard then ran the latest update that MS pushed out on the system (I did not run it from windows update I downloaded it and then ran it)  I let a day go by to make sure that the WSUS server pushed the updates to the clients and it looks good  I am able now to go to the windows update site for my servers and get updates and my clients are getting updates from the WSUS.

    Anyway thanks for all the help and input.

    Adam Raff

    Tuesday, September 18, 2012 4:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I was able to get into the database via the following method

    login Computername\instance

    Then your WSUS server is not using the Windows Internal Database.

    Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
    Product Manager, SolarWinds
    Microsoft MVP - Software Distribution (2005-2012)
    My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin

    Tuesday, September 18, 2012 8:25 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Bom dia,

    Tiveram solução para o erro? Estrou enfrentando problemas.

    Marcelo Brandao

    Tuesday, May 10, 2016 2:50 PM
  • Question
    Sign in to vote
    2
    Sign in to vote

    To open the Windows Internal Database on Server 2012+ using SQL Management Studio, connect to the following

    \\.\pipe\MICROSOFT##WID\tsql\query

    • Proposed as answer by balboa41 Monday, November 7, 2016 7:11 PM
    Friday, June 10, 2016 11:35 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you!  that one worked for me! 

    Jose Angel Rivera

    Monday, November 7, 2016 7:12 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    This worked for me on Server 2012 r2 using SSMS 17
    Monday, May 22, 2017 5:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Can I offer a little bit of an alternate that works far better - May I suggest my WSUS Cleanup script that does way more as it automates a proper maintenance routine for WSUS.


    Have a peek at my Adamj Clean-WSUS script. It is the last WSUS Script you will ever need.


    http://community.spiceworks.com/scripts/show/2998-adamj-clean-wsus

    What it does:

    1. Remove all Drivers from the WSUS Database.
    2. Shrink your WSUSContent folder's size by declining superseded updates.
    3. Remove declined updates from the WSUS Database.
    4. Clean out all the synchronization logs that have built up over time (configurable, with the default keeping the last 14 days of logs).
    5. Compress Update Revisions.
    6. Remove Obsolete Updates.
    7. Computer Object Cleanup (configurable, with the default of deleting computer objects that have not synced within 30 days).
    8. Application Pool Memory Configuration to display the current private memory limit and easily increase it by any configurable amount.
    9. Run the Recommended SQL database Maintenance script on the actual SQL database.
    10. Run the Server Cleanup Wizard.

    It will email the report out to you or save it to a file, or both.

    Although the script is lengthy, it has been made to be super easy to setup and use. There are some prerequisites and instructions at the top of the script. After installing the prerequisites and configuring the variables for your environment, simply run:

    .\Clean-WSUS.ps1 -FirstRun

    and thenq

    .\Clean-WSUS.ps1 -InstallTask

    If you wish to view or increase the Application Pool Memory Configuration, you must run it with the required switch. See Get-Help .\Clean-WSUS.ps1 -Examples

    If you're having trouble, there's also a -HelpMe option that will create a log so you can send it to me for support.

    Adam Marshall, MCSE: Security
    http://www.adamj.org

    Tuesday, May 23, 2017 2:19 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for this! The named pipe for server 2008 is different! Now I can connect but I am getting a login error with a domain admin account in SQL Server Management Studio. Any ideas?
    Tuesday, May 15, 2018 11:21 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    The problem often is that the database is no longer responding to queries because it has reached it's maximum allowed size. So the issue is how to get past THAT for anything else to work.
    Tuesday, May 15, 2018 11:22 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    Thanks for this! The named pipe for server 2008 is different! Now I can connect but I am getting a login error with a domain admin account in SQL Server Management Studio. Any ideas?
    Run SSMS using the Right click "Run as administrator" method and it will work fine using Windows Authentication with a local admin account.

    Adam Marshall, MCSE: Security
    http://www.adamj.org
    Microsoft MVP - Windows and Devices for IT

    Wednesday, May 16, 2018 1:46 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    The problem often is that the database is no longer responding to queries because it has reached it's maximum allowed size. So the issue is how to get past THAT for anything else to work.

    If you're using WSUS Automated Maintenance (Formerly Adamj Clean-WSUS), you don't have that issue.

    A big question if you're experiencing a maximum size for the database - it means to me that you've installed WSUS using a SQL Express database, rather than the WID. SQL Express has a hard limit while WID does not have any limits for database size. The downside to WID is that you can't access it over a network - it must be locally accessed; but for most users, WID is the way to go. There is only a small percentage that would require a full blown SQL instance.

    Adam Marshall, MCSE: Security
    http://www.adamj.org
    Microsoft MVP - Windows and Devices for IT

    Wednesday, May 16, 2018 1:56 AM