Answered by:
How to connect to the SUSDB to clean it up for KB2720211

Question
-
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
-
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.
- Marked as answer by Clarence Zhang Friday, August 17, 2012 2:11 AM
Tuesday, August 14, 2012 8:07 AM -
I found out that it is not a internal database but 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
-
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 -
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 -
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 -
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.
- Edited by Clarence Zhang Wednesday, August 8, 2012 6:54 AM
Wednesday, August 8, 2012 6:47 AM -
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 -
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 -
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.
- Marked as answer by Clarence Zhang Friday, August 17, 2012 2:11 AM
Tuesday, August 14, 2012 8:07 AM -
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 -
I found out that it is not a internal database but 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 -
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 -
I was able to get into the database via the following method
login Computername\instance
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.GarvinTuesday, September 18, 2012 8:25 PM -
Bom dia,
Tiveram solução para o erro? Estrou enfrentando problemas.
Marcelo Brandao
Tuesday, May 10, 2016 2:50 PM -
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 -
Thank you! that one worked for me!
Jose Angel Rivera
Monday, November 7, 2016 7:12 PM -
This worked for me on Server 2012 r2 using SSMS 17Monday, May 22, 2017 5:52 PM
-
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.orgTuesday, May 23, 2017 2:19 AM -
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
-
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
-
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 ITWednesday, May 16, 2018 1:46 AM -
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 ITWednesday, May 16, 2018 1:56 AM