Мурзилка написана по этим материалам, со всеми граблями

Powershell disable time limit IIS

Сие выполняем 1 раз

Get-Module WebAdministration
Import-Module WebAdministration
Get-ChildItem IIS:\AppPools | ? name -eq "WsusPool" |
select name, @{Name="Timeout"; Exp={$_.processmodel.idletimeout}}
Set-ItemProperty IIS:\AppPools\WsusPool -Name processModel.idleTimeout -Value "00:00:00"

Powershell disable memory limit IIS

Сие выполняем 1 раз

Get-ChildItem IIS:\AppPools | ? name -eq «WsusPool» |
select name, @{Name=»Memory»; Exp={$_.recycling.periodicrestart.privateMemory}}
Set-ItemProperty IIS:\AppPools\WsusPool -Name recycling.periodicrestart.privateMemory -Value 0

Необходимый софт

Далее нам понадобится установленные sqlcmd и запуск скриптов из командной строки запущенной от имени администратора, соответственно 1 и 2 скрипты сохранены под нужными именами, версия клиента 110/130/170 может отличаться, возможно, придется установить

MSSQL ODBC driver
Visual C++ Redistrib Pack
MsSqlCmdLnUtils

«C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\sqlcmd.exe» -I -S np:\.\pipe\MICROSOFT##WID\tsql\query -i c:\scripts\remote_query_timeout.sql

«C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\sqlcmd.exe» -I -S np:\.\pipe\MICROSOFT##WID\tsql\query -i c:\scripts\reindex_wsus.sql

disable mssql remote query limit

Сие выполняем 1 раз

—disable query limit
USE SUSDB;
GO
EXEC sp_configure ‘remote query timeout’, 0 ;
GO
RECONFIGURE ;
GO

mssql database reindex

выполняем по необходимости

тут

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

Powershell cleanup

$wsus= Get-WSUSServer -Name wsus-server-name -Port 8530
$wsus | Invoke-WsusServerCleanup -CleanupUnneededContentFiles
$wsus | Invoke-WsusServerCleanup -CleanupObsoleteComputers
$wsus | Invoke-WsusServerCleanup -CleanupObsoleteUpdates
$wsus | Invoke-WsusServerCleanup -DeclineExpiredUpdates
$wsus | Invoke-WsusServerCleanup -DeclineSupersededUpdates

Powershell delete older 30days out of sync computers

$wsus= Get-WSUSServer -Name wsus-server-name -Port 8530
$from =(Get-Date).AddDays(-400)
$to=(Get-Date).AddDays(-30)
$clients=Get-WsusComputer -UpdateServer $wsus -FromLastReportedStatusTime $from -ToLastReportedStatusTime $to
$clients|ogv
foreach($cli in $clients) 
{
 $wsus.GetComputerTargetByName($cli.FullDomainName)| foreach{$_.Delete()}
}