DEFINITIONS

Definitions More Info.
Definition ID2.008
TitleSQL
CategoryNOTES
DefinitionFulltext Index maintenance bakim
Definition DescriptionUSE [DBA]
GO
/****** Object: StoredProcedure [dbo].[sp_FullTextIndexMaintenance] Script Date: 3/9/2020 11:10:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[sp_FullTextIndexMaintenance]

@fragmentation TINYINT = 1
,@rebuild BIT = 0 -- 1: Rebuild, 0: Reorganize
,@exec BIT = 0


AS
SET NOCOUNT ON

--DECLARE @fragmentation TINYINT = 2
--DECLARE @rebuild BIT = 0 -- 1: Rebuild, 0: Reorganize
--DECLARE @exec BIT = 1

DECLARE @cmd NVARCHAR(MAX)
DECLARE @cmd_main NVARCHAR(MAX)
DECLARE @DBs TABLE
(
ID INT PRIMARY KEY IDENTITY(1,1),
DbName NVARCHAR(500),
CatalogName NVARCHAR(500)
)

SET @cmd = '
USE [?];
;WITH tablenames AS (
SELECT DISTINCT
OBJECT_NAME([table_id]) AS TableName
--,COUNT([fragment_id]) AS Fragments
FROM [?].sys.fulltext_index_fragments
WHERE status = 4
OR status = 6
GROUP BY OBJECT_NAME([table_id])
HAVING COUNT([fragment_id]) > ' + CAST(@fragmentation as NVARCHAR(2)) + '
),
ftc AS (
SELECT fc.name AS CatalogName, OBJECT_NAME(fi.object_id) AS TableName, fi.object_id FROM sys.fulltext_indexes fi
JOIN sys.fulltext_catalogs fc
ON fi.fulltext_catalog_id = fc.fulltext_catalog_id
)
SELECT ''?'', CatalogName FROM tablenames
JOIN ftc
ON tablenames.TableName = ftc.TableName

'

INSERT INTO @DBs
EXEC sys.sp_MSforeachdb @cmd

SELECT @cmd_main = COALESCE(@cmd_main + '','') + 'USE [' + DbName + '];
ALTER FULLTEXT CATALOG [' + CatalogName + '] ' + (CASE WHEN (@rebuild = 1) THEN 'REBUILD;' ELSE 'REORGANIZE;' END) + '

' FROM @DBs

IF (@cmd_main IS NULL)
BEGIN
PRINT 'No action.'
END
ELSE
BEGIN
IF (@exec = 1)
BEGIN
PRINT '------/ sp_FullTextIndexMaintenance Started: ' + CONVERT(NVARCHAR(19),GETDATE(), 104) + ' ' + CONVERT(NVARCHAR(16),GETDATE(), 108)
EXEC (@cmd_main)
PRINT '------/ sp_FullTextIndexMaintenance Finished: ' + CONVERT(NVARCHAR(19),GETDATE(), 104) + ' ' + CONVERT(NVARCHAR(16),GETDATE(), 108)
END

ELSE
SELECT @cmd_main
END
RecordBycunay
Record Date09-03-2020 13:01:52
Düzenle
Kopyala
Sil