スクリプト センターにある Re-index the WSUS 3.0 Database っていう SQL はデータベースのインデックスを再構成するためのメンテナンス スクリプトだけれど、こいつを流すと OS の表示が Windows 10 から Vista に戻っちゃうよ。
tbComputerTargetDetail テーブルの OSDescription 列の内容が戻っちゃうんだよね。
なので先日のスクリプトと合体させてみた。
tbComputerTargetDetail テーブルの OSDescription 列の内容が戻っちゃうんだよね。
なので先日のスクリプトと合体させてみた。
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 -- 不具合対応 ここから -- Windows 8.1 の PC 情報を修正 UPDATE [SUSDB].[dbo].[tbComputerTargetDetail] SET [OSDescription] = 'Windows 8.1' WHERE [OSMajorVersion] = '6' AND [OSMinorVersion] = '3' AND [OldProductType] = '1' AND ([OSDescription] <> 'Windows 8.1' or [OSDescription] IS NULL) -- Windows Server 2012 R2 の PC 情報を修正 UPDATE [SUSDB].[dbo].[tbComputerTargetDetail] SET [OSDescription] = 'Windows Server 2012 R2' WHERE [OSMajorVersion] = '6' AND [OSMinorVersion] = '3' AND [OldProductType] <> '1' AND ([OSDescription] <> 'Windows Server 2012 R2' or [OSDescription] IS NULL) -- Windows 10 の PC 情報を修正 UPDATE [SUSDB].[dbo].[tbComputerTargetDetail] SET [OSDescription] = 'Windows 10' WHERE [OSMajorVersion] = '10' AND [OSMinorVersion] = '0' AND [OldProductType] = '1' AND ([OSDescription] <> 'Windows 10' or [OSDescription] IS NULL) -- 不具合対応 ここまで -- --Update all statistics PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121) EXEC sp_updatestats PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121) GOまぁまぁいいんじゃない?
コメント