修复错误表分区

t1292582463

--------------修复时间消耗具体由物理配置及表数据量决定，建议停止业务量操作修复，以免造成错误数据
-----------------------------------------------------------------------------------------------------------------查询错误分区
SELECT ROW_NUMBER() OVER(ORDER BY t1.fldGuid) AS num,t.name AS functions, tp.name AS schemes,CAST(t1.fldGuid AS NVARCHAR(256)) AS fldGuid, t2.value, t4.name AS fliegroup, t5.name AS flie
  INTO #InfoTable
  FROM sys.partition_functions t
  LEFT JOIN sys.partition_schemes tp ON tp.function_id = t.function_id
  OUTER APPLY(SELECT fldGuid FROM 表) t1 -------------此处由于是根据特点表字段进行分区，故查询特点表字段进行查询表错误分区,根据时间可建立时间临时表,其他表字段同理查询
  LEFT JOIN sys.partition_range_values t2 ON t2.function_id = t.function_id AND t2.value = t1.fldGuid
  LEFT JOIN sys.destination_data_spaces t3 ON t3.partition_scheme_id = tp.data_space_id AND t3.destination_id = t2.boundary_id
  LEFT JOIN sys.filegroups t4 ON t4.data_space_id = t3.data_space_id
  LEFT JOIN sys.database_files t5 ON t5.data_space_id = t4.data_space_id
WHERE CHARINDEX((CASE SQL_VARIANT_PROPERTY(t2.value, 'BaseType')WHEN 'smalldatetime' THEN CONVERT(NVARCHAR (10), CAST(t2.value AS SMALLDATETIME), 120)ELSE CAST(t2.value AS NVARCHAR (MAX))END), t4.name) < 1 OR t2.value IS NULL
ORDER BY value ;
-----------------------------------------------------------------------------------------------------------------变量参数
DECLARE @filepath NVARCHAR (MAX) ;
DECLARE @dbname NVARCHAR(MAX)
SET @dbname = DB_NAME() ;
SELECT TOP 1 @filepath = REPLACE(filename, '.mdf', '')FROM master.dbo.sysdatabases WHERE name = @dbname ;
-----------------------------------------------------------------------------------------------------------------new
DECLARE @i INT,@j INT
SET @i = 1
SELECT @j = COUNT(1) FROM #InfoTable
WHILE @i <= @j
BEGIN
        DECLARE @value SQL_VARIANT,@functions sysname,@schemes sysname,@fldGuid NVARCHAR(256),@fliegroup sysname,@flie sysname
        SELECT @functions = functions,@schemes = schemes,@fldGuid = fldGuid,@value = value,@fliegroup = fliegroup,@flie = flie FROM #InfoTable WHERE num = @i
        DECLARE @gropname NVARCHAR(MAX)
        DECLARE @filename NVARCHAR(MAX)
        DECLARE @schemesname NVARCHAR(MAX)
        DECLARE @functionsname NVARCHAR(MAX)
        SET @gropname = (@dbname + N'_ByAreaGroup_' + @fldGuid)
        SET @filename = (@dbname + N'_ByArea_' + @fldGuid)
        SET @schemesname = CAST(@schemes AS NVARCHAR(MAX))
        SET @functionsname = CAST(@functions AS NVARCHAR(MAX))
        IF NOT EXISTS(SELECT TOP 1 1 FROM sys.filegroups WHERE name = @gropname)
        BEGIN
                --添加文件组及文件
                DECLARE @sqlgroup NVARCHAR(MAX)
                SET @sqlgroup = N' ALTER DATABASE [' + @dbname + N'] ADD FILEGROUP [' + @gropname  + N'] '
                --SELECT @sqlgroup
                EXEC(@sqlgroup)
                DECLARE @sqlfile NVARCHAR(MAX)
                SET @sqlfile = N' ALTER DATABASE [' + @dbname + N'] ADD FILE (name=N''' + @filename + N''',FILENAME=N''' + @filepath + N'_ByArea_' + @fldGuid + N'.ndf'',size=5mb,filegrowth=5mb) TO FILEGROUP [' + @gropname + N']'
                --SELECT @sqlfile
                EXEC(@sqlfile)
        END
        --指定下一个分区
        DECLARE @sqlschemes NVARCHAR(MAX)
        SELECT @sqlschemes = N' ALTER PARTITION SCHEME ['+@schemesname+'] NEXT USED ['+ @gropname +']'
        --SELECT @sqlschemes
        EXEC(@sqlschemes)
         
        IF EXISTS(SELECT TOP 1 1 FROM sys.partition_functions t INNER JOIN sys.partition_range_values t2 ON t2.function_id = t.function_id WHERE t.name = @functionsname AND (CASE SQL_VARIANT_PROPERTY(t2.value, 'BaseType')WHEN 'smalldatetime' THEN CONVERT(NVARCHAR (10), CAST(t2.value AS SMALLDATETIME), 120)ELSE CAST(t2.value AS NVARCHAR (MAX))END) = @fldGuid)         
        BEGIN
                --合并分区
                DECLARE @sqlmerge NVARCHAR(MAX)
                SET @sqlmerge = 'ALTER PARTITION FUNCTION '+@functionsname+'() MERGE RANGE('''+@fldGuid+''')'
                --SELECT @sqlmerge
                EXEC(@sqlmerge)

                --拆分分区
                DECLARE @sqlfunctions1 NVARCHAR(MAX)
                SET @sqlfunctions1 = N'ALTER PARTITION FUNCTION ['+@functionsname+']() SPLIT RANGE(N'''+@fldGuid+''')'
                --SELECT @sqlfunctions1
                EXEC(@sqlfunctions1)
        END ELSE
        BEGIN
                --拆分分区
                DECLARE @sqlfunctions2 NVARCHAR(MAX)
                SET @sqlfunctions2 = N'ALTER PARTITION FUNCTION ['+@functionsname+']() SPLIT RANGE(N'''+@fldGuid+''')'
                --SELECT @sqlfunctions2
                EXEC(@sqlfunctions2)
        END
        SET @i = @i + 1
END

DROP TABLE #InfoTable

pevos

学习一下。