{"id":2258,"date":"2021-10-14T10:00:18","date_gmt":"2021-10-14T08:00:18","guid":{"rendered":"https:\/\/www.apvnorge.no\/?page_id=2258"},"modified":"2021-10-14T11:06:08","modified_gmt":"2021-10-14T09:06:08","slug":"wsus-database-maintenance","status":"publish","type":"page","link":"https:\/\/www.apvnorge.no\/no\/it-knowledge-base\/microsoft-wsus\/wsus-database-maintenance\/","title":{"rendered":"WSUS Database Maintenance"},"content":{"rendered":"<div id=\"apvno-2064734782\" class=\"apvno-before-content apvno-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-7003427967427457\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:970px;height:250px;\" \ndata-ad-client=\"ca-pub-7003427967427457\" \ndata-ad-slot=\"6272152047\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div>\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<ul class=\"wp-block-list\"><li><strong><a href=\"https:\/\/www.apvnorge.no\/it-knowledge-base\/microsoft-wsus\/\" data-type=\"page\" data-id=\"2245\">Back to Microsoft WSUS<\/a><\/strong><\/li><li><strong><a href=\"https:\/\/www.apvnorge.no\/it-knowledge-base\/\">Back to IT Knowledge Base<\/a><\/strong><\/li><\/ul>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">To Re-index the WSUS 3.0 Database, connect SQL Management Studio via:<br>\\.\\pipe\\MSSQL$MICROSOFT##SSEE\\sql\\query<br>and execute the following script:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\/<br>This sample T-SQL script performs basic maintenance tasks on SUSDB<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Identifies indexes that are fragmented and defragments them. For certain<br>tables, a fill-factor is set in order to improve insert performance.<br>Based on MSDN sample at <a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/ms188917.aspx\" target=\"_blank\" rel=\"noreferrer noopener\">http:\/\/msdn2.microsoft.com\/en-us\/library\/ms188917.aspx<\/a><br>and tailored for SUSDB requirements<\/li><li>Updates potentially out-of-date table statistics.<br>\/<\/li><\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">USE SUSDB;<br>GO<br>SET NOCOUNT ON;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8212; Rebuild or reorganize indexes based on their fragmentation levels<br>DECLARE @work_to_do TABLE (<br>objectid int<br>, indexid int<br>, pagedensity float<br>, fragmentation float<br>, numrows int<br>)<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">DECLARE @objectid int;<br>DECLARE @indexid int;<br>DECLARE @schemaname nvarchar(130);<br>DECLARE @objectname nvarchar(130);<br>DECLARE @indexname nvarchar(130);<br>DECLARE @numrows int<br>DECLARE @density float;<br>DECLARE @fragmentation float;<br>DECLARE @command nvarchar(4000);<br>DECLARE @fillfactorset bit<br>DECLARE @numpages int<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8212; Select indexes that need to be defragmented based on the following<br>&#8212; * Page density is low<br>&#8212; * External fragmentation is high in relation to index size<br>PRINT &#8216;Estimating fragmentation: Begin. &#8216; + convert(nvarchar, getdate(), 121)<br>INSERT @work_to_do<br>SELECT<br>f.object_id<br>, index_id<br>, avg_page_space_used_in_percent<br>, avg_fragmentation_in_percent<br>, record_count<br>FROM<br>sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, &#8216;SAMPLED&#8217;) AS f<br>WHERE<br>(f.avg_page_space_used_in_percent &lt; 85.0 and f.avg_page_space_used_in_percent\/100.0 * page_count &lt; page_count &#8211; 1) or (f.page_count &gt; 50 and f.avg_fragmentation_in_percent &gt; 15.0)<br>or (f.page_count &gt; 10 and f.avg_fragmentation_in_percent &gt; 80.0)<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">PRINT &#8216;Number of indexes to rebuild: &#8216; + cast(@@ROWCOUNT as nvarchar(20))<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">PRINT &#8216;Estimating fragmentation: End. &#8216; + convert(nvarchar, getdate(), 121)<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SELECT @numpages = sum(ps.used_page_count)<br>FROM<br>@work_to_do AS fi<br>INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id<br>INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8212; Declare the cursor for the list of indexes to be processed.<br>DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8212; Open the cursor.<br>OPEN curIndexes<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8212; Loop through the indexes<br>WHILE (1=1)<br>BEGIN<br>FETCH NEXT FROM curIndexes<br>INTO @objectid, @indexid, @density, @fragmentation, @numrows;<br>IF @@FETCH_STATUS &lt; 0 BREAK;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SELECT<br>@objectname = QUOTENAME(o.name)<br>, @schemaname = QUOTENAME(s.name)<br>FROM<br>sys.objects AS o<br>INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id<br>WHERE<br>o.object_id = @objectid;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SELECT<br>@indexname = QUOTENAME(name)<br>, @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END<br>FROM<br>sys.indexes<br>WHERE<br>object_id = @objectid AND index_id = @indexid;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation &lt; 30.0) SET @command = N&#8217;ALTER INDEX &#8216; + @indexname + N&#8217; ON &#8216; + @schemaname + N&#8217;.&#8217; + @objectname + N&#8217; REORGANIZE&#8217;; ELSE IF @numrows &gt;= 5000 AND @fillfactorset = 0<br>SET @command = N&#8217;ALTER INDEX &#8216; + @indexname + N&#8217; ON &#8216; + @schemaname + N&#8217;.&#8217; + @objectname + N&#8217; REBUILD WITH (FILLFACTOR = 90)&#8217;;<br>ELSE<br>SET @command = N&#8217;ALTER INDEX &#8216; + @indexname + N&#8217; ON &#8216; + @schemaname + N&#8217;.&#8217; + @objectname + N&#8217; REBUILD&#8217;;<br>PRINT convert(nvarchar, getdate(), 121) + N&#8217; Executing: &#8216; + @command;<br>EXEC (@command);<br>PRINT convert(nvarchar, getdate(), 121) + N&#8217; Done.&#8217;;<br>END<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8212; Close and deallocate the cursor.<br>CLOSE curIndexes;<br>DEALLOCATE curIndexes;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">IF EXISTS (SELECT * FROM @work_to_do)<br>BEGIN<br>PRINT &#8216;Estimated number of pages in fragmented indexes: &#8216; + cast(@numpages as nvarchar(20))<br>SELECT @numpages = @numpages &#8211; sum(ps.used_page_count)<br>FROM<br>@work_to_do AS fi<br>INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id<br>INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">PRINT &#8216;Estimated number of pages freed: &#8216; + cast(@numpages as nvarchar(20))<br>END<br>GO<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8211;Update all statistics<br>PRINT &#8216;Updating all statistics.&#8217; + convert(nvarchar, getdate(), 121)<br>EXEC sp_updatestats<br>PRINT &#8216;Done updating statistics.&#8217; + convert(nvarchar, getdate(), 121)<br>GO<\/p>\n<div id=\"apvno-2261628926\" class=\"apvno-after apvno-entity-placement\"><script>\r\n_adsys_id = 33949;\r\n_adsys_size = 1;\r\n<\/script>\r\n<script src=\"https:\/\/d.wedosas.net\/d.js\"><\/script>    \r\n<\/br>\r\n<div>\r\n<script async src=\"https:\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\r\n<!-- AD-4 -->\r\n<ins class=\"adsbygoogle\"\r\n     style=\"display:inline-block;width:970px;height:250px\"\r\n     data-ad-client=\"ca-pub-7003427967427457\"\r\n     data-ad-slot=\"6272152047\"><\/ins>\r\n<script>\r\n     (adsbygoogle = window.adsbygoogle || []).push({});\r\n<\/script><\/div>\r\n<a href=\"https:\/\/affiliates.ssl.com\/820-3-1-13.html\" target=\"_blank\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" style=\"border:0px\" src=\"https:\/\/i0.wp.com\/affiliates.ssl.com\/media\/banners\/SSL-banner-UC-certificate-2-v5.png?resize=640%2C79&#038;ssl=1\" width=\"640\" height=\"79\" alt=\"Microsoft Exchange SAN UCC SSL\"><\/a><\/div>","protected":false},"excerpt":{"rendered":"<p>Back to Microsoft WSUS Back to IT Knowledge Base To Re-index the WSUS 3.0 Database, connect SQL Management Studio via:\\.\\pipe\\MSSQL$MICROSOFT##SSEE\\sql\\queryand execute the following script: \/This sample T-SQL script performs basic maintenance tasks on SUSDB Identifies indexes that are fragmented and defragments them. For certaintables, a fill-factor is set in order to improve insert performance.Based on [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":2245,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"om_disable_all_campaigns":false,"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"class_list":["post-2258","page","type-page","status-publish","hentry"],"translation":{"provider":"WPGlobus","version":"3.0.2","language":"no","enabled_languages":["en","no","cz"],"languages":{"en":{"title":true,"content":true,"excerpt":false},"no":{"title":false,"content":false,"excerpt":false},"cz":{"title":false,"content":false,"excerpt":false}}},"aioseo_notices":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P9T0bk-Aq","jetpack-related-posts":[],"jetpack_likes_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.apvnorge.no\/no\/wp-json\/wp\/v2\/pages\/2258","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.apvnorge.no\/no\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.apvnorge.no\/no\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.apvnorge.no\/no\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.apvnorge.no\/no\/wp-json\/wp\/v2\/comments?post=2258"}],"version-history":[{"count":2,"href":"https:\/\/www.apvnorge.no\/no\/wp-json\/wp\/v2\/pages\/2258\/revisions"}],"predecessor-version":[{"id":2273,"href":"https:\/\/www.apvnorge.no\/no\/wp-json\/wp\/v2\/pages\/2258\/revisions\/2273"}],"up":[{"embeddable":true,"href":"https:\/\/www.apvnorge.no\/no\/wp-json\/wp\/v2\/pages\/2245"}],"wp:attachment":[{"href":"https:\/\/www.apvnorge.no\/no\/wp-json\/wp\/v2\/media?parent=2258"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}