数据库快照复制,前天调整为仅仅在晚上进行一次,但是今天早上发现数据库服务器爆慢,因为我昨天下班前开了一个跟踪,正好看到无尽的循环进程,调用对象是fn_repl32bitstringhash,查了半天也没发现这个函数是干嘛的,为什么变成了无尽的循环。
另一个症状就是复制的目标数据库表对象数据正在不断地被清空、填充。后续的其他复制作业都报告成功,事实上却没有成功订阅。禁用作业后停止,收工执行一切正常。跟踪到的具体脚本如下,不断循环:select @binary_string = convert(varbinary(8000), @string), @binary_string_len = datalength(@binary_string), @hash_value = 0, @hash_value_byte1 = 31, @hash_value_byte2 = 41, @hash_value_byte3 = 59, @hash_value_byte4 = 26, @hash_randomizer_byte1 = 53, @hash_randomizer_byte2 = 58, @hash_randomizer_byte3 = 97, @hash_randomizer_byte4 = 93, @hash_randomizer_byte5 = 23, @hash_randomizer_byte6 = 84, @hash_randomizer_byte7 = 62, @curr_byte_pos = 1, @curr_hash_value_pos = 1, @curr_hash_randomizer_pos = 1
while @curr_byte_pos <= @binary_string_len
select @curr_byte = convert(tinyint, substring(@binary_string,@curr_byte_pos,1))
if @curr_byte = 0
select @curr_hash_value_byte = case @curr_hash_value_pos when 1 then @hash_value_byte1 when 2 then @hash_value_byte2 when 3 then @hash_value_byte3 when 4 then @hash_value_byte4 end
select @curr_hash_randomizer_byte = case @curr_hash_randomizer_pos when 1 then @hash_randomizer_byte1 when 2 then @hash_randomizer_byte2 when 3 then @hash_randomizer_byte3 when 4 then @hash_randomizer_byte4 when 5 then @hash_randomizer_byte5 when 6 then @hash_randomizer_byte6 when 7 then @hash_randomizer_byte7 end
select @curr_hash_value_byte = @curr_hash_value_byte ^ @curr_hash_randomizer_byte ^ @curr_byte
select @curr_hash_value_pos = (@curr_hash_value_byte % 4) + 1
select @curr_hash_randomizer_pos = (@curr_hash_value_byte % 7) + 1
if @curr_hash_value_pos = 1
select @hash_value_byte1 = @hash_value_byte1 ^ @curr_hash_value_byte
select @curr_byte_pos = @curr_byte_pos + 1
while @curr_byte_pos <= @binary_string_len
select @curr_byte = convert(tinyint, substring(@binary_string,@curr_byte_pos,1))
if @curr_byte = 0
select @curr_byte_pos = @curr_byte_pos + 1
continue
while @curr_byte_pos <= @binary_string_len
select @curr_byte = convert(tinyint, substring(@binary_string,@curr_byte_pos,1))
if @curr_byte = 0 遇到到过的达人指点下,多谢。
另一个症状就是复制的目标数据库表对象数据正在不断地被清空、填充。后续的其他复制作业都报告成功,事实上却没有成功订阅。禁用作业后停止,收工执行一切正常。跟踪到的具体脚本如下,不断循环:select @binary_string = convert(varbinary(8000), @string), @binary_string_len = datalength(@binary_string), @hash_value = 0, @hash_value_byte1 = 31, @hash_value_byte2 = 41, @hash_value_byte3 = 59, @hash_value_byte4 = 26, @hash_randomizer_byte1 = 53, @hash_randomizer_byte2 = 58, @hash_randomizer_byte3 = 97, @hash_randomizer_byte4 = 93, @hash_randomizer_byte5 = 23, @hash_randomizer_byte6 = 84, @hash_randomizer_byte7 = 62, @curr_byte_pos = 1, @curr_hash_value_pos = 1, @curr_hash_randomizer_pos = 1
while @curr_byte_pos <= @binary_string_len
select @curr_byte = convert(tinyint, substring(@binary_string,@curr_byte_pos,1))
if @curr_byte = 0
select @curr_hash_value_byte = case @curr_hash_value_pos when 1 then @hash_value_byte1 when 2 then @hash_value_byte2 when 3 then @hash_value_byte3 when 4 then @hash_value_byte4 end
select @curr_hash_randomizer_byte = case @curr_hash_randomizer_pos when 1 then @hash_randomizer_byte1 when 2 then @hash_randomizer_byte2 when 3 then @hash_randomizer_byte3 when 4 then @hash_randomizer_byte4 when 5 then @hash_randomizer_byte5 when 6 then @hash_randomizer_byte6 when 7 then @hash_randomizer_byte7 end
select @curr_hash_value_byte = @curr_hash_value_byte ^ @curr_hash_randomizer_byte ^ @curr_byte
select @curr_hash_value_pos = (@curr_hash_value_byte % 4) + 1
select @curr_hash_randomizer_pos = (@curr_hash_value_byte % 7) + 1
if @curr_hash_value_pos = 1
select @hash_value_byte1 = @hash_value_byte1 ^ @curr_hash_value_byte
select @curr_byte_pos = @curr_byte_pos + 1
while @curr_byte_pos <= @binary_string_len
select @curr_byte = convert(tinyint, substring(@binary_string,@curr_byte_pos,1))
if @curr_byte = 0
select @curr_byte_pos = @curr_byte_pos + 1
continue
while @curr_byte_pos <= @binary_string_len
select @curr_byte = convert(tinyint, substring(@binary_string,@curr_byte_pos,1))
if @curr_byte = 0 遇到到过的达人指点下,多谢。
纯量SQL函数貌似是繁体翻译。