Shrink WSUS database

I discovered, that WSUS database and log file are very huge (WSUS.mdf and WSUS_log.ldf). Here's what I did to reduce the size:

Facts:

1. WSUS uses Windows Internal Database

2. To do some command against this database engine, you need SQL server management studio

3. It's very easy to install SSMS using Chocolatey

4. It's very easy to install Chocolatey using powershell

5. PowerShell might need some adjustments

Actual To-Do list:

1a. install Chocolatey (open PowerShell as an administrator)

iwr https://chocolatey.org/install.ps1 -UseBasicParsing | iex

1b. install Chocolatey (open cmd as an administrator)

powershell -exe unrestricted "iwr https://chocolatey.org/install.ps1 -UseBasicParsing | iex"

2. If this command throws certificate error, please use his command before (also in PowerShell)

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

3. Now open cmd.exe as Administrator and install SSMS

cinst sql-server-management-studio -y

4. Next thing is to open SSMS as system (you might need to restart computer before opening SSMS).

psexec -s -i cmd

and in the opened windows paste the path to the ssms executable

"C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe"

5. If you don't have psexec installed, use this line to install PSTools

cinst pstools -y

6. To connect to WID, you need to connect to this "server":

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

7. To shrink all databases, use this query in the New Query window

EXEC sp_MSForEachDB 'select ''?'' as [Database]; ALTER DATABASE [?] SET RECOVERY SIMPLE; DBCC SHRINKDATABASE (''?'' , 0)'

Profit. 40GB more free disk space