我建立一个发布后,要把一个100M的数据库做发布项,但在初始化快照时总是报上面的错误,不知为何?
希望大家帮帮忙!!在代理程序程序设置里我将 Querytimeout 设成3000 后,现在不报上面的错了。但在快照代理程序运行过程中,在大量复制项目[aa]的快照时 老出错。这里aa是一个不定的表。
请高手指教
希望大家帮帮忙!!在代理程序程序设置里我将 Querytimeout 设成3000 后,现在不报上面的错了。但在快照代理程序运行过程中,在大量复制项目[aa]的快照时 老出错。这里aa是一个不定的表。
请高手指教
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GOexec sp_resetstatus
gosp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
然后重启服务,看置疑是否去掉。如果去掉了,赶紧备份,注意不要冲掉以前的备份。
再然后是查置疑的原因,看是不是空间不够了。这是CSDNM经理给出的
2.合并代理程序最常见的问题就是连接超时,这并不是一个问题和缺陷,即使是好的优化分区也会发生这样的问题,设置QUERYTIMEOUT的值也许是最有效的方法。还有可作的就是对你的QUERY和INDEX进行优化。
3.SET LOCK_TIMEOUT为一固定的值,不要是-1(一直等待)比如50ms
4.在SQL SERVER ENTERPRISE MANAGERMENT 选择REPLICATION MONITOR--AGENT(代理程序)--MERGE AGENTS(合并代理程序)--ERROR DETAIL...(错误详细信息)。CHECK提示的错误信息。
5.通过PROFILER跟踪,和通过SP_LOCK,SP_WHO2,SP_MONITOR...CHECK是否有锁死的OBJECTS.
我把QUERYTIMEOUT设成300000了,仍然出那样的问题。现在我总结了一下:
一般是到整个初始化快照最后完成时,出错在最后一个表的大量复制过程中。如果把最后一个表单独发布,一切正常。如果不让最后一个表发布,则错误出在倒数第二个表,也就是新的最后发布的表。
-- By: TIME3\Administrator
-- Server: TIME3BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'REPL-Snapshot') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'REPL-Snapshot' -- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'TIME3-JoyBook30-JoyBook30-1')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''TIME3-JoyBook30-JoyBook30-1'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'TIME3-JoyBook30-JoyBook30-1'
SELECT @JobID = NULL
END BEGIN -- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'TIME3-JoyBook30-JoyBook30-1', @owner_login_name = N'TIME3\Administrator', @description = N'No description available.', @category_name = N'REPL-Snapshot', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 0, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Log Reader Agent startup message.', @command = N'sp_MSadd_snapshot_history @perfmon_increment = 0, @agent_id = 1, @runstatus = 1,
@comments = ''Starting agent.''', @database_name = N'distribution', @server = N'TIME3', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Run agent.', @command = N'-Publisher [TIME3] -PublisherDB [JoyBook30] -Distributor [TIME3] -Publication [JoyBook30] -ReplicationType 2 -DistributorSecurityMode 1 ', @database_name = N'distribution', @server = N'TIME3', @database_user_name = N'', @subsystem = N'Snapshot', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 10, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'Detect nonlogged agent shutdown.', @command = N'sp_MSdetect_nonlogged_shutdown @subsystem = ''Snapshot'', @agent_id = 1', @database_name = N'distribution', @server = N'TIME3', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 2, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Replication agent schedule.', @enabled = 1, @freq_type = 8, @active_start_date = 20030304, @active_start_time = 1100, @freq_interval = 64, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
以上是这个作业。 最后一个表是codebind
怎么考出最后一个表的命令。
MS SQL SERVER ENTERPRISE EDITION.
下午有消息:-)
Bulk copied snapshot data for article '[codeBind]'[0 rows]
Bulk copied snapshot data for article '[codeBind]'[0 rows]
Bulk copied snapshot data for article '[codeLost]'[2 rows]
Bulk copying snapshot data for article '[codeBind]'
Bulk copied snapshot data for article '[codeMedi]'[0 rows]
Bulk copying snapshot data for article '[codeLost]'快照代理程序的参数:
BcpBatchSize 100000
HistoryVerboseLevel 2
LoginTimeout 15000
MaxBcpthreads 2
Querytimeout 300000