C windows wid data susdb mdf

Место на нашем WSUS сервере ежедневно таяло на глазах. Решили зачистить старые обновления для Windows 7, 2008 и прочих уже почти отсутствующих у нас операционок.

Для начала стоит попробовать традиционный метод: «Чистка базы WSUS через Server Cleanup Wizard».

Нашли интересный баг, когда проставляем все галки, то сервер наглухо зависает (у нас «провисел» почти сутки) но так ничего не очистилось. А самое интересное, свободные 20 гиг, которые ранее были свободны на диске, тоже куда-то делись 😦

Если галки выставлять поочередно, то что-то очищается.

Но когда ставим галку «Неиспользуемые обновления и редакции обновлений», вылетает: «Ошибка базы данных».

Попробовали сделать PowerShell скриптом (можно поочередно вбивать команды, можно вставить в cmd файл и запустить его):

PS1:

[reflection.assembly]::LoadWithPartialName(«Microsoft.UpdateServices.Administration») | out-null

$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer();

$cleanupScope = new-object Microsoft.UpdateServices.Administration.CleanupScope;

$cleanupScope.DeclineSupersededUpdates        = $true      

$cleanupScope.DeclineExpiredUpdates           = $true

$cleanupScope.CleanupObsoleteUpdates          = $true

$cleanupScope.CompressUpdates                 = $true

$cleanupScope.CleanupObsoleteComputers        = $true

$cleanupScope.CleanupUnneededContentFiles     = $true

$cleanupManager = $wsus.GetCleanupManager();

$cleanupManager.PerformCleanup($cleanupScope);

Опции:

DeclineSupersededUpdates — Отклонить замененные обновления.
DeclineExpiredUpdates — Отклонить просроченные обновления.
CleanupObsoleteUpdates — Удалить из базы неиспользуемые обновления.
CompressUpdates — Удалить из базы устаревшие ревизии обновлений.
CleanupObsoleteComputers — удалить компьютеры которые не соединялись за последние 30 дней.
CleanupUnneededContentFiles — Удаляет файлы отклоненных обновлений из папки WSUS.

или CMD:

@echo off

@echo Starting cleanup: %date% %time% >> d:scriptsWSUS_Cleanup.log

powershell.exe d:scriptsWSUS_Cleanup.ps1 >> d:scriptsWSUS_Cleanup.log

@echo Finished cleanup: %date% %time% >> d:scriptsWSUS_Cleanup.log

Но в итоге после некоторых раздумий сервер выдал:

Печаль…

Как поправить ошибку пока не понятно…

Вот здесь как раз человек объясняет ситуацию:

http://zetslash.blogspot.com/2017/06/wsus.html

Процедура запуска сценария будет разной для разных вариантов установки SUSDB (Windows Internal Database или SQL Server). Для того, чтобы определить, где развёрнута база данных WSUS необходимо на сервере WSUS проверить значение реестра SQLServerName в разделе HKLMSoftwareMicrosoftUpdate ServicesServerSetup. Если в значении присутствуют ##SSEE или ##WID, то база данных развёрнута на WID, а если вы видите в этом значении имя хоста или имя_сервераэкземпляр, то база данных развёрнута на SQL Server:

В нашем случае видим, что база WID:

Далее, в случае, если база данных развёрнута на WID нам необходимо использовать утилиту sqlcmd и планировщик задач Windows. Если SQL Server — можно запланировать выполнение сценария с помощью планов обслуживания (Maintenance Plans).

Windows Internal Database

Чтобы установить sqlcmd можно скачать и установить SQL Server Management Studio (SSMS) для версии вашей WID. Версию можно определить в лог-файле:

• В Windows Server 2012 — C:WindowsWIDLog — открываем error.log, где в самом начале файла указана используемая версия SQL, для которой нам нужно скачать SSMS Express.
• В Windows Server 2008 R2 и ниже — C:WindowsSYSMSISSEEMSSQL.2005MSSQLLOG — также открываем error.log и в самом начале файла смотрим версию.

Пытаюсь понять какая версия SQL у меня установлена:

Но ничего не понятно, и в файле ничего нет, хотя вроде должно быть.

В итоге устанавливаю консоль SQL Server Management Studio Express 2016 (она благополучно установилась):

Запускаем консоль  Management Studio с правами администратора.

Подключаемся к базе, указав следующее имя сервера:

• в Windows Server 2008 / R2  — \.pipemssql$microsoft##sseesqlquery
• в Windows Server 2012  / R2 — \.pipeMICROSOFT##WIDtsqlquery

Запустил пару скриптов из этого поста, но мне это никак не помогло:

http://zetslash.blogspot.com/2017/06/wsus.html

https://itblog.ru.net/ws/wsus/wsus-cleanup-powershell/

Еще раз запустил мастер очистки, оставил его на ночь и тут случилось чудо:

Самое печальное, что очистка прошла, а места больше на диске так и не стало.

Попробовал сделать через PowerShell:

[reflection.assembly]::LoadWithPartialName(«Microsoft.UpdateServices.Administration»)` | out-null

$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer();

$cleanupScope = new-object Microsoft.UpdateServices.Administration.CleanupScope;

$cleanupScope.DeclineSupersededUpdates = $true

$cleanupScope.DeclineExpiredUpdates = $true

$cleanupScope.CleanupObsoleteUpdates = $true

$cleanupScope.CompressUpdates = $true

$cleanupScope.CleanupObsoleteComputers = $true

$cleanupScope.CleanupUnneededContentFiles = $true

$cleanupManager = $wsus.GetCleanupManager();

$cleanupManager.PerformCleanup($cleanupScope);

Вероятно необходима реиндексация базы.

Попробовал реиндексировать вот таким скриптом:

USE SUSDB;

GO

SET NOCOUNT ON;

— Rebuild or reorganize indexes based on their fragmentation levels

DECLARE @work_to_do TABLE (

objectid int

, indexid int

, pagedensity float

, fragmentation float

, numrows int

)

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @numrows int

DECLARE @density float;

DECLARE @fragmentation float;

DECLARE @command nvarchar(4000);

DECLARE @fillfactorset bit

DECLARE @numpages int

— Select indexes that need to be defragmented based on the following

— * Page density is low

— * External fragmentation is high in relation to index size

PRINT ‘Estimating fragmentation: Begin. ‘ + convert(nvarchar, getdate(), 121)

INSERT @work_to_do

SELECT

f.object_id

, index_id

, avg_page_space_used_in_percent

, avg_fragmentation_in_percent

, record_count

FROM

sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘SAMPLED’) AS f

WHERE

(f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count — 1)

or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0)

or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)

PRINT ‘Number of indexes to rebuild: ‘ + cast(@@ROWCOUNT as nvarchar(20))

PRINT ‘Estimating fragmentation: End. ‘ + convert(nvarchar, getdate(), 121)

SELECT @numpages = sum(ps.used_page_count)

FROM

@work_to_do AS fi

INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id

INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

— Declare the cursor for the list of indexes to be processed.

DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do

— Open the cursor.

OPEN curIndexes

— Loop through the indexes

WHILE (1=1)

BEGIN

FETCH NEXT FROM curIndexes

INTO @objectid, @indexid, @density, @fragmentation, @numrows;

IF @@FETCH_STATUS < 0 BREAK;

SELECT

@objectname = QUOTENAME(o.name)

, @schemaname = QUOTENAME(s.name)

FROM

sys.objects AS o

INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE

o.object_id = @objectid;

SELECT

@indexname = QUOTENAME(name)

, @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END

FROM

sys.indexes

WHERE

object_id = @objectid AND index_id = @indexid;

IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)

SET @command = N’ALTER INDEX ‘ + @indexname + N’ ON ‘ + @schemaname + N’.’ + @objectname + N’ REORGANIZE’;

ELSE IF @numrows >= 5000 AND @fillfactorset = 0

SET @command = N’ALTER INDEX ‘ + @indexname + N’ ON ‘ + @schemaname + N’.’ + @objectname + N’ REBUILD WITH (FILLFACTOR = 90)’;

ELSE

SET @command = N’ALTER INDEX ‘ + @indexname + N’ ON ‘ + @schemaname + N’.’ + @objectname + N’ REBUILD’;

PRINT convert(nvarchar, getdate(), 121) + N’ Executing: ‘ + @command;

EXEC (@command);

PRINT convert(nvarchar, getdate(), 121) + N’ Done.’;

END

— Close and deallocate the cursor.

CLOSE curIndexes;

DEALLOCATE curIndexes;

IF EXISTS (SELECT * FROM @work_to_do)

BEGIN

PRINT ‘Estimated number of pages in fragmented indexes: ‘ + cast(@numpages as nvarchar(20))

SELECT @numpages = @numpages — sum(ps.used_page_count)

FROM

@work_to_do AS fi

INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id

INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

PRINT ‘Estimated number of pages freed: ‘ + cast(@numpages as nvarchar(20))

END

GO

—Update all statistics

PRINT ‘Updating all statistics.’ + convert(nvarchar, getdate(), 121)

EXEC sp_updatestats

PRINT ‘Done updating statistics.’ + convert(nvarchar, getdate(), 121)

GO

Но  снова место на диске так и не появилось.

Попробовал еще один скрипт:

DECLARE IndexCursor CURSOR FOR

SELECT sys.indexes.name AS IndexName

,sys.objects.name AS TableName

FROM sys.indexes

INNER JOIN sys.objects

ON sys.indexes.object_id = sys.objects.object_id

WHERE sys.objects.type = ‘U’

AND sys.indexes.is_disabled = 0

AND NOT sys.indexes.name IS NULL

ORDER BY TableName ASC

,IndexName ASC;

DECLARE @IndexName nvarchar(max), @TableName nvarchar(max);

DECLARE @ExecSql nvarchar(max);

OPEN IndexCursor;

FETCH NEXT FROM IndexCursor INTO @IndexName, @TableName;

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @TableName + ‘.’ + @IndexName;

SET @ExecSql = ‘ALTER INDEX [‘ + @IndexName + ‘] ON [‘ + @TableName + ‘] REBUILD;’;

EXEC (@ExecSql);

FETCH NEXT FROM IndexCursor INTO @IndexName, @TableName;

END

CLOSE IndexCursor;

DEALLOCATE IndexCursor;

Но все это добавило лишь несколько мегабайт свободного места.

Скорее всего больше нечего реиндексировать.

ЭТО ВСЕ, ЧТО УДАЛОСЬ ПОЧЕРПНУТЬ  ПО ДАННОМУ ВОПРОСУ.

P.S.

Если правил на WSUS не много и можно их потом быстро настроить, то можно переставить WSUS с нуля или резать по живому, это уже кому как виднее и удобнее…

Попробуем резать по живому.

Как корректно и правильно «зачистить» папку WSUSContent я так не нашел, а именно она сейчас занимает все место на диске.

Останавливаем службу в IIS.

Удаляем все, что есть в папке WSUSContent, сразу видим, что диск опустел:

Запускаем службу

Нажимаем Синхронизировать сейчас, но ничего не происходит, так как в базе осталась информация что все обновления на месте, хотя по факту их уже нет.

Чтобы повторно установить WSUS с чистой базой данных, то есть предыдущей конфигурации:

Запустите Windows Powershell в качестве администратора и используйте следующие команды:

• Uninstall-WindowsFeature -Name UpdateServices,Windows-Internal-Database -Restart
• Повторите перезагрузку, удалите ВСЕ в C:WindowsWID(для Win 2012 r2).
• Затем запустите следующую команду для повторной установки WSUS:

Install-WindowsFeature UpdateServices -Restart

Кажется, что удаление WSUS а также WID Database опция фактически не удаляет базу данных WID.

• База данных WID можно удалить, удалив Windows Internal Database особенность.
• Вам также потребуется вручную удалить файл C:windowsWIDDatasusdb.mdf перед повторной установкой все снова.

Надеемся WSUS 4.0 не за горами и версия без подобных «багов» все же появится в продакшине, хотя, как мне показалось, Microsoft почему-то уже давно «забил» на WSUS.

Всем хорошей работы!!!

13.02.2019 —

Posted by |
ms windows server 2016

For all versions from 2008 and higher, the removal procedure is relatively the same, if not ‘the same’.

WSUS 3.0 was 2008/2008R2. WSUS 4/6 (named 4, but versioned as 6.x) was 2012/2012R2. WSUS on 2016 is ‘versioned’ as 10.x

AD does NOT use ‘the WID’ so it is safe to remove the WID and traces of it (so long as nothing else uses it, which from your initial post suggests that nothing else is using it). You can check by going to C:WindowsWIDData and seeing what databases are
in there. SUSDB is WSUS and then you’ll have master, tempdb, model and msdb. Also, I recommend AGAINST using SQL Express as SQL Express has a hard limit on the size of the database but the WID does not.

I’d suggest using the downstream replica method to ‘migrate’ to a new server. This way you don’t have to redo everything (from approvals to re-downloading all the updates).

To remove WSUS completely, you need to:

1. Remove WSUS Role and Windows Internal Database (WID) Feature.
2. Remove C:WSUS or where ever the WSUSContent folder resides.
3. Remove C:WindowsWID (specifically: delete the SUSDB.mdf and SUSDB_log.ldf in C:WindowsWIDData). If you don’t remove the WID role and its files on a reinstall, it will re-attach to the same database.
4. In IIS, remove the ‘WSUS Administration’ website and the ‘WsusPool’ Application Pool if they still exist.

<the instructions 5 and 6 don’t apply to you as you’re not re-installing on the same server, however I’ve included them for other people’s troubleshooting help>

5. Restart the server and re-add the WSUS And WID Roles. Let it install, and then restart the server again.
6. MAKE SURE .NET 4.7 IS NOT INSTALLED (it comes as a KB number for your server OS, not an add/remove programs installation.) The WSUS post-installer is not compatible with .NET 4.7 and will always error out. Once WSUS is installed and working, .NET 4.7 can
be reapplied and WSUS should still work.

===========

Once done,

Please have a look at the WSUS Automated Maintenance (WAM) system. It is an automated maintenance system for WSUS, the last system you’ll ever need to maintain WSUS!

https://community.spiceworks.com/scripts/show/2998-wsus-automated-maintenance-formerly-adamj-clean-wsus

What it does:

1. Add WSUS Index Optimization to the database to increase the speed of many database operations in WSUS by approximately 1000-1500 times faster.
2. Remove all Drivers from the WSUS Database (Default; Optional).
3. Shrink your WSUSContent folder’s size by declining multiple types of updates including by default any superseded updates, preview updates, expired updates, Itanium updates, and beta updates. Optional extras: Language Packs, IE7, IE8, IE9, IE10, Embedded,
NonEnglishUpdates, ComputerUpdates32bit, WinXP.
4. Remove declined updates from the WSUS Database.
5. Clean out all the synchronization logs that have built up over time (configurable, with the default keeping the last 14 days of logs).
6. Compress Update Revisions.
7. Remove Obsolete Updates.
8. Computer Object Cleanup (configurable, with the default of deleting computer objects that have not synced within 30 days).
9. Application Pool Memory Configuration to display the current private memory limit and easily set it to any configurable amount including 0 for unlimited. This is a manual execution only.
10. Checks to see if you have a dirty database, and if you do, fixes it. This is primarily for Server 2012 WSUS, and is a manual execution only.
11. Run the Recommended SQL database Maintenance script on the actual SQL database.
12. 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 so don’t over think it. There are some prerequisites and instructions at the top of the script. After installing the prerequisites and configuring the variables for your environment
(email settings only if you are accepting all the defaults), simply run:

.Clean-WSUS.ps1 -FirstRun

If you wish to view or increase the Application Pool Memory Configuration, or run the Dirty Database Check, 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
Microsoft MVP — Windows and Devices for IT

• Marked as answer by

  Monday, February 5, 2018 3:17 AM

• What exact process did you follow to uninstall/reinstall, and what type of database are you using?

  A quick google search found a suggestion here Opens a new window Opens a new window if you’re using a Windows Internal Database:

  It seems that uninstalling «WSUS» and «WID Database» option does not actually remove the WID database. The WID database can be removed by uninstalling the «Windows Internal Database» feature. You will also need to manually delete the file C:windowsWIDDatasusdb.mdf before re-installing everything again.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Hi Gabrielle

   When I did the last uninstall I removed everything and had to reinstall the IIS and GUI portion through cmd prompt

  Now when I try to uninstall the WID database it is asking to uninstall the other components to I just want a clean install of WSUS so that I can push updates through

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Log file is located at C:Usersa-deyrAppDataLocalTemp2tmpE8D9.tmp
  Post install is starting
  Fatal Error: Unable to open the physical file «C:WindowsWIDDataSUSDB.mdf». Operating system error 2: «2(The system cannot find the file specified.)».
  Could not restart database «SUSDB». Reverting to the previous status.
  ALTER DATABASE statement failed.
  File activation failure. The physical file name «C:WindowsWIDDataSUSDB_log.ldf» may be incorrect.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  OverDrive

  
  

  This person is a verified professional.

  Verify your account
  to enable IT peers to see that you are a professional.

  mace

  WSUS Expert

  • check
    390
    Best Answers
  • thumb_up
    555
    Helpful Votes

  Too bad you didn’t come here first before uninstalling — I would have told you that my script fixes 95% of the issues with WSUS.

  Gabrielle.L is right — Remove the WSUS & Windows Internal Database roles, remove the C:WindowsWID folder, reboot, re-add the roles.

  Then, to keep you sane for WSUS Maintenance:

  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. Shrink your WSUSContent folder’s size by declining superseded updates.
  2. It will clean out all the synchronization logs that have built up over time (configurable, with the default keeping the last 14 days of logs).
  3. Remove all Drivers from the WSUS Database.
  4. Remove declined updates from the WSUS Database
  5. Run the server cleanup wizard.
  6. Lastly, but most important, it will run the recommended maintenance script on the actual SQL database.

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

  Follow the instructions at the top of the script, but essentially run .Clean-WSUS.ps1 -FirstRun and then set a scheduled task to run the script with the -ScheduledRun daily at a time you want.

  
  0 of 1 found this helpful
  thumb_up
  thumb_down
  

• 

  I am looking at the script how can I get this to run copy it over Onedrive. I am just frustrated now with the issues.

   Can I just remove the WID only from Roles on Windows Server 2012 R2

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• richarddey wrote:

  Log file is located at C:Usersa-deyrAppDataLocalTemp2tmpE8D9.tmp

  Well, have a look at the log file. It might help you find the appropriate errors

  At this point I’d make an educated guess that your WID is hosed. Is there anything else running on this server? If not, I’d suggest you flatten it and start over — it will likely be less complicated for you than attempting to repair the database.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• richarddey wrote:

  I am looking at the script how can I get this to run copy it over Onedrive. I am just frustrated now with the issues.

   Can I just remove the WID only from Roles on Windows Server 2012 R2

  Oh, it’s still installed? Yes, if it’s not used for anything else on the server, follow the steps I included in my first post — uninstall WID then manually delete the leftover components.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Just attached a log file if that will help a little

  attach_file
  Attachment
  
  Log.txt
  57.5 KB
  
  

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Ok when I goto  remove roles other components are greyed out to be removed as well do I want to remove these to I don’t want to lose the GUI’s again if I remove the roles.

   If I take the check mark off everything will this only remove the WSUS Database role

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Oh one other thing I still have the Post -install waiting to run will this be removed when the roles are uninstalled

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Unfortunately I can not redo server I have applications running on it. If I remove the WSUS it says I have remove .NET 3.5 and 4.5 and WEB Server and the components under it. Is ok to do to remove them to

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• richarddey wrote:

  Unfortunately I can not redo server I have applications running on it. If I remove the WSUS it says I have remove .NET 3.5 and 4.5 and WEB Server and the components under it. Is ok to do to remove them to

  Depends on what else is on the server.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Well I only have Sophos Enterprise console on Server. I have started the removal of Roles and feature see what happens

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  OverDrive

  
  

  This person is a verified professional.

  Verify your account
  to enable IT peers to see that you are a professional.

  mace

  WSUS Expert

  • check
    391
    Best Answers
  • thumb_up
    555
    Helpful Votes

  You will be fine then.  You’re re-installing them too, so.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  I hope so believe me.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  I think I had two removals going cause now the server is saying can’t complete updates undoing changes pulling my hair out here I just want it installed

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Is there anything I need to remove in the features to have the NEXT button bold example uncheck >NET 3.5 or GUI

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Screen Shot attached

  attach_file
  Attachment
  
  WSUS Features Capture.PNG
  49.9 KB
  
  

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  OverDrive

  
  

  This person is a verified professional.

  Verify your account
  to enable IT peers to see that you are a professional.

  mace

  WSUS Expert

  • check
    391
    Best Answers
  • thumb_up
    555
    Helpful Votes

  remove check beside Windows Internal Database

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Ok will do

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Ok it is saying removing WID now thank you

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  OverDrive

  
  

  This person is a verified professional.

  Verify your account
  to enable IT peers to see that you are a professional.

  mace

  WSUS Expert

  • check
    391
    Best Answers
  • thumb_up
    555
    Helpful Votes

  Remove the WSUS & Windows Internal Database roles, remove the C:WindowsWID folder, remove the old WSUS Folder wherever it’s located, reboot, re-add the roles.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Ok here is the screen shot no restart needed

  Can I add feature again for WSUS or check the WID folder and remove this first

  attach_file
  Attachment
  
  Removal Capture.PNG
  27.7 KB
  
  

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  OverDrive

  
  

  This person is a verified professional.

  Verify your account
  to enable IT peers to see that you are a professional.

  mace

  WSUS Expert

  • check
    391
    Best Answers
  • thumb_up
    555
    Helpful Votes

  Remove WID folder and old WSUS Folders FIRST!

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Ok will do thank you

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Ok it restarted the Server and I see the following still there sorry

  attach_file
  Attachment
  
  Roles and Features Capture.PNG
  13.3 KB
  
  

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  I did remove the WSUS and WID folder now

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  OverDrive

  
  

  This person is a verified professional.

  Verify your account
  to enable IT peers to see that you are a professional.

  mace

  WSUS Expert

  • check
    391
    Best Answers
  • thumb_up
    555
    Helpful Votes

  Re-add the roles. See what you get.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Ok finally the WSUS is gone and all folders are deleted it is just a matter of adding the role back to Server as if it was a fresh install correct

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  OverDrive

  
  

  This person is a verified professional.

  Verify your account
  to enable IT peers to see that you are a professional.

  mace

  WSUS Expert

  • check
    390
    Best Answers
  • thumb_up
    555
    Helpful Votes

  yes. Then setup my script to run on a daily basis and you won’t have these issues again.

  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. Shrink your WSUSContent folder’s size by declining superseded updates.
  2. It will clean out all the synchronization logs that have built up over time (configurable, with the default keeping the last 14 days of logs).
  3. Remove all Drivers from the WSUS Database.
  4. Remove declined updates from the WSUS Database
  5. Run the server cleanup wizard.
  6. Lastly, but most important, it will run the recommended maintenance script on the actual SQL database.

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

  Follow the instructions at the top of the script, but essentially run .Clean-WSUS.ps1 -FirstRun and then set a scheduled task to run the script with the -ScheduledRun daily at a time you want.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Ok silly question how do I get the script on the server.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  OverDrive

  
  

  This person is a verified professional.

  Verify your account
  to enable IT peers to see that you are a professional.

  mace

  WSUS Expert

  • check
    391
    Best Answers
  • thumb_up
    555
    Helpful Votes

  Download it, save it, follow the pre-requisites (Read them and follow)

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Sorry I just saw the get code sorry about that

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Ok I have done the Role for WSUS now and running the Post-Installation and the following error was encountered now

   Log file is located at C:Usersa-deyrAppDataLocalTemptmp2BD.tmp
  Post install is starting
  Fatal Error: Cannot start service W3SVC on computer ‘.’.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Ok last question I found the issue

  I have added the Groups now going to add the Servers to each group

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  I have installed WSUS on the new server windows 2012 r2 and have copied the WSUSContents over from the old server I see them all there. I want to copy the approvals etc that were done on the old server so that it reflects on the new server I see 12000 updates on new server and only 267 on the old one

   How do I get this to be a mirror of the old WSUS Server.

   This export import commands I believe I done these already when I copied the WSUSContents over. What about Database part Help please

  I could use a little more help here, the database part is confusing here. What do I copy over to new server besides the WSUSContents which I did

  Thank you for your help almost there now. Just want to get the same approvals and declines from the old server

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• After you copy the WSUSContent over open a command prompt in the WSUS Tools folder.  Run

  WSUSUtil reset

  This command will tell the WSUS server that each update download must be revalidated and redownloaded if not in place already.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  I did this should i see a message or should i check the update services

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  oh the other thing to i have a directory named WSUS_database and under this is the WSUSContent etc. is that ok to do will the wsusutil reset still run being there

  
  Was this post helpful?
  thumb_up
  thumb_down
  

In this tutorial, I will walk you through how to reset the WSUS service by recreating the SUSDB database on WID (Windows Internal Database) and the updates storage folder (WsusContent / UpdateServicesPackages).

This type of operation can normally be done “without too much risk”, resetting WSUS will see us lose the history of updates on the computers, that is why before performing this operation, it is necessary to recommended to back up the server.

There are several reasons for resetting the WSUS database:

• Data cleaning
• Service optimization
• …

To proceed with resetting WSUS services, there are two solutions that I will explain to you in this article:

• Manually reset the database
• Uninstall and reinstall the WSUS role

Solution 1: reset SUSDB database

This solution consists of deleting the WSUS files and the database and then relaunching the WSUS configuration wizard.

To perform the operation, you have to download HeidiSQL on the WSUS server (you can use the portable version).

1. On the WSUS server, open a PowerShell command prompt as administrator.

2. The first step will be to check the services used by the WSUS role, enter the command below to display the status of the services.

If the WSUS service is functioning correctly, the 3 services are normally functioning.

3. Now, you have to stop the services in order to be able to delete the files, enter one of the commands below depending on your environment.

4. Check that the services are stopped.

5. We will start by deleting the files from the database located in the folder C:WindowsWIDData.

Still in PowerShell, enter the command below :

Once the order is placed, the files are no longer present.

6. Now we are going to delete the files the WSUS update files, on my server, it is in the C: WSUS folder. Enter the command below, adapting to your installation.

7. Recreate the folder where the update files will be stored, in my case C:WSUS.

8. Restart the services required for WSUS to function.

9. It is now necessary to delete the database of the instance, because for the moment only the files have been deleted. Open HeidiSQL as administrator, configure a new connection comment on the capture below, indicating as Name or IP : \.pipeMICROSOFT##WIDtsqlquery.

10. Run the following query to delete the SUSDB database from the WID instance.

11. Start the post-install configuration, using the cmdlets below, adapting the CONTENT_DIR parameter to your WSUS environment.

If you have an error, restart the server.

12. Launch the WSUS console to open the Windows Update Services setup wizard.

The procedure is the same as for a new installation : WSUS – Installation and configuration – Windows Server Update Service – Page 3 of 7 – RDR-IT (rdr-it.com)

Solution 2 : Uninstall and install the WSUS role

The second solution is to uninstall and (re) install WSUS on the same server.

In order to save time, for this operation, I will explain how to do it in PowerShell.

1. Open a PowerShell window as administrator.

2. Run the following cmdlet to uninstall WSUS.

3. Restart the server

It is possible to add the -Restart parameter to do it during the uninstallation

4. After restarting the server, delete the folder containing the Windows Update files

5. Delete files from the WID database

6. Install WSUS

All that remains is to configure WSUS again after restarting the server if it is necessary.

7. Launch the WSUS administration console to open the configuration wizard.

The steps to remove WSUS and reinstall WSUS are pretty standard but they do have some variances on how WSUS was installed in the first place.

To remove WSUS completely, you need to:

1. Remove WSUS Role
   1. You can remove the role through the GUI using Server Manager or
   2. You can use an Administrative PowerShell prompt and run:

   Remove-WindowsFeature -Name UpdateServices,UpdateServices-DB,UpdateServices-RSAT,UpdateServices-API,UpdateServices-UI -IncludeManagementTools

2. Remove the Database WSUS was using (SUSDB.mdf and SUSDB_log.ldf).
   1. If you were using the Windows Internal Database (WID), specifically delete the SUSDB.mdf and SUSDB_log.ldf in C:WindowsWIDData (or C:WindowsSYSMSISSEEMSSQL.2005MSSQLData for Server 2008/2008 R2)
      1. 1. If the WID was only used for WSUS, you should remove the WID feature in Server Manager to fully clean up the installation. When you do remove the WID Feature, make sure to remove the entire C:WindowsWID folder too.
         2. If you want to remove WSUS and KEEP the WID, and plan on NOT to reinstall WSUS, you must first detach the SUSDB database from SQL before removing the mdf and ldf files. The easiest way is to use SQL Server Management Studio to connect to the WID, and then right click on the SUSDB database > Tasks > Detach.
         3. You can remove it through PowerShell from an Administrative PowerShell prompt by:
            Remove-WindowsFeature -Name UpdateServices-WidDB
         4. If you’re using Server 2008 / Server 2008 R2, use the following PowerShell command from an Administrative PowerShell prompt to remove the WID if it was used ONLY for WSUS:

      if ($env:PROCESSOR_ARCHITECTURE -eq 'x86') { msiexec.exe /x {CEB5780F-1A70-44A9-850F-DE6C4F6AA8FB } callerid=ocsetup.exe }
      if ($env:PROCESSOR_ARCHITECTURE -eq 'AMD64') { msiexec.exe /x {BDD79957-5801-4A2D-B09E-852E7FA64D01} callerid=ocsetup.exe }

   2. If you were using a remote SQL Server instance, detach the database from the remote server and physically delete the SUSDB.mdf and SUSDB_log.ldf.
   3. If you were using a local SQL Server instance (Standard or Express [See why you should not use Express edition for WSUS]) detach the database from the local server instance and physically delete the SUSDB.mdf and SUSDB_log.ldf.
3. In IIS, remove the ‘WSUS Administration’ website and the ‘WsusPool’ Application Pool if they still exist.
4. If you don’t plan on re-installing WSUS, remove the custom compression module that WSUS adds to IIS. For more information on this, visit Mark Berry’s blog post.

   & "$env:windirSystem32inetsrvappcmd.exe" set config -section:system.webServer/httpCompression /-[name='xpress']

5. Remove the “C:Program FilesUpdate Services” folder.
6. Remove the WSUS Content folder wherever you had it previously installed (eg. C:WSUS, or D:WSUS)
7. Restart the server.

WSUS should now be completely gone from your system. Now you should be able to re-install the WSUS role, and if necessary, the Windows Internal Database (WID) role too.

To Install WSUS:

1. Re-add the WSUS Role
   1. Re-add the WID feature if applicable
2. Restart the server again.
3. MAKE SURE .NET 4.7 IS NOT INSTALLED FOR SERVERS OLDER THAN 2019 (it comes as a KB number for your server OS, not an add/remove programs installation.) The WSUS post-installer on prior versions to 2019 is not compatible with .NET 4.7 and will always error out. Once WSUS is installed and working, .NET 4.7 can be reapplied and WSUS should still work. The idea here is that you do not want to remove the integral .NET component to Windows (eg. .NET 4.5) as it will remove options like Server Manager and other features. What you want to do is make sure the integral component (eg. .NET 4.5) is NOT upgraded to .NET 4.7 through Windows Update patches.
4. Run the post-installation configuration.
   1. From an administrative command prompt:
      1. For the Windows Internal Database:

         "C:Program FilesUpdate ServicesToolswsusutil.exe" postinstall CONTENT_DIR=C:WSUS

      2. For any other SQL Database location:

         "C:Program FilesUpdate ServicesToolswsusutil.exe" postinstall SQL_INSTANCE_NAME="HOSTNAME" CONTENT_DIR=C:WSUS

         or

         "C:Program FilesUpdate ServicesToolswsusutil.exe" postinstall SQL_INSTANCE_NAME= "HOSTNAMEINSTANCE" CONTENT_DIR=C:WSUS

Troubleshooting Steps If You Still Have Problems:

• If you have issues running the post-installation configuration, dis-join the server from the domain, and restart. Try the post-installation steps again. If it works, the issue is a policy on your domain that is causing the issues. You can then re-join the server to the domain.
• If you are having issues because the C:Program FilesUpdate ServicesTools folder doesn’t exist, use a local administrator account (not a domain admin account) on the WSUS server to perform the uninstall, restart, and reinstall.
• If you are having issues running the post-installation, relating to “UnauthorizedAccessException: Attempted to perform an unauthorized operation.” which would be found in the postinstall log, the issue may be that the group policy Computer ConfigurationWindows SettingsSecurity SettingsLocal PoliciesManage Auditing and Security Log doesn’t have the Local Administrators group specified. Add the Local Administrators group (Showing up as “Administrators”) to this policy and force an update to the group policies with gpupdate /force
• If you still experience issues installing WSUS, check the Component Based Servicing (CBS) log (C:WindowsLogsCBSCBS.log) for details. It is possible that your system is missing an update that is preventing you from installing WSUS. If you are missing an update, make sure to run it from an Elevated Command/PowerShell Prompt, and not just double click the MSU file after you download it.

How to Tell if the WID Instance Carries More Than Just the SUSDB Database

To tell if the WID carries more than the SUSDB database, you’ll need to install SQL Server Management Studio (SSMS) and connect to the WID instance to browse the databases. To do this, open SSMS by using right click, “Run as administrator” and in the database server copy/paste

WID2008

np:\.pipeMSSQL$MICROSOFT##SSEEsqlquery

WID2012+

np:\.pipeMICROSOFT##WIDtsqlquery

Keep the setting for use Windows Authentication and click connect. It should connect successfully to the WID SQL instance. Then expand Databases and you should see SUSDB and any other databases on this instance.

Обновлено 14.08.2017

Всем привет сегодня хочу рассказать, как установить WSUS с SQL базой в Windows Server 2012R2. Ранее я уже описывал как установить WSUS на Windows Server 2012R2, и данный пост показывает лишь альтернативный метод решения для сервер обновлений, Так что выбор исключительно на ваше усмотрение, но хочу отметить, что в случае с сиквелом, у вас будет ряд преимуществ, и в скорости работы и в плане масштабируемости и отказоустойчивости, в такой реализации, вам легко будет перенести роль на другой сервер, затратив на это совсем не много времени.

Перед тем как начать развертывание вам нужно установить MS SQL 2012, с той лишь разницей что из компонентов достаточно выбрать Database Engine и средства управления. SQL может быть как на отдельной машине так и там где будет WSUS.

Для службы WSUS требуется одна из перечисленных ниже баз данных.

• Внутренняя база данных Windows (WID)
• Microsoft SQL Server 2012 Стандартный выпуск
• Microsoft SQL Server 2012 Выпуск Enterprise Edition
• Microsoft SQL Server 2012 Выпуск Express Edition
• Microsoft SQL Server 2008 R2 SP1 Стандартный выпуск
• Microsoft SQL Server 2008 R2 SP1 Выпуск Enterprise Edition
• Microsoft SQL Server 2008 R2 SP1 Выпуск Express Edition

После установки сиквела запускаем на сервере Управление-Добавить роли и компоненты

Оставляем Установка ролей или компонентов, жмем далее

Выбираем сервер из пула, если их там несколько

Выбираем Службы Windows Server Update, мастер покажет вам что будуд доставлены еще роли и компоненты, такие как IIS.

Далее

Далее

Далее будет вводная информация о WSUS, в которой вам расскажут, что должен быть один сервер как минимум, кто будет тянуть от MS обновления.

Ставим галку база данных, для подключения к внешней БД.

• WID (Windows Internal database)  — встроенная база Windows
• Database – будет использоваться локальная или внешняя база данных на SQL Server для WSUS

База WID по умолчанию называется SUSDB.mdf и хранится в каталоге windir%widdata. Эта база поддерживает только  Windows аутентификацию (но не SQL). Именем инстанса базы данных WSUS будет server_nameMicrosoft##WID.

Внутреннюю базу  Windows (Windows Internal Database) рекомендуется использовать, если:

• Организация не имеет и не планирует покупать лицензии на SQL Server.
• Не планируется использовать балансировку нагрузки на WSUS (NLB WSUS).
• Если планируется развернуть множество WSUS (например, в филиалах). В этом случае на вторичных серверах рекомендуется использовать встроенную базу WSUS.

Базу WID нельзя администрировать с помощью стандартных графических консолей или средств управления (только cli).

Отметим, что в SQL Server 2008/2012 Express  имеет ограничение на размер БД – 10 Гб. Скорее всего это ограничение достигнуто не будет (например, размер базы WSUS на 2000 клиентов – около 3 Гб). Ограничение  Windows Internal Database – 524 Гб.

Задаем место на диске где будут хранится обновления советую выделить хотя бы гигабайт 300

Далее указываем название сервера с SQL и жмем проверка подключения.

Видим подключение прошло успешно

После чего начнется установка IIS, тут все можно оставить по умолчанию

Установить.

После установки жмем закрыть

Далее в верхнем углу вы увидите знак восклицания, который означает что нужно произвести донастройку WSUS роли.

Для наглядности перейдем в пункт WSUS, и вы увидите сообщение Службы Windows Server Update Services — требуется настройка на VIRT85

Если нажать подробнее, то откроется сообщение в котором предложат нажать Запуск послеустановочных заданий.

Начнется конфигурация сервера

Видим, что все хорошо настроилось.

Теперь давайте посмотрим, какая БД у нас создалась в SQL, открываем Management Studio

Видим БД SUSDB

Теперь открываем оснастку WSUS,  Средства-Службы Windows Server Update Services

Откроется мастер настройки Windows Server Update Services

Если хотите MS улучшить WSU То ставьте галку отправлять данные

Базовый процесс развертывания службы WSUS включает в себя сервер внутри корпоративного брандмауэра, который обслуживает частную интрасеть. Для загрузки обновлений сервер WSUS подключается к центру обновления Майкрософт. Это называетсясинхронизация. В ходе синхронизации служба WSUS определяет, появились ли доступные свежие обновления с момента последней синхронизации. Если это первая синхронизация WSUS, то все обновления становятся доступными для загрузки.

Для получения обновлений от Майкрософт сервер WSUS по умолчанию использует порт 8530 для протокола HTTP и порт 8531 для протокола HTTPS. Если выход из вашей сети в Интернет защищен корпоративным брандмауэром, то необходимо будет открыть порты на сервере, который непосредственно подключается к центру обновления Майкрософт. Если вы планируете использовать настраиваемые порты для данного подключения, то необходимо открыть эти порты вместо вышеупомянутых. Вы можете настроить несколько серверов WSUS так, чтобы они синхронизировались с родительским WSUS-сервером.

На следующем рисунке представлен простой сценарий для сервера WSUS, по которому администратор может настраивать сервер под управлением WSUS, находящийся внутри корпоративного брандмауэра. Сервер выполняет синхронизацию содержимого непосредственно с Центром обновления Майкрософт, а затем распространяет обновления на клиентские компьютеры.

Несколько серверов WSUS