当使用数据库管理功能下的维护计划, 建立索引失败的情况下, 使用代理执行作业的方式, 比较实用的, 代码如下:
- USE [msdb]
- GO
- /** Object: Job [索引重建] Script Date: 2018-2-8 16:29:40 **/
- BEGIN TRANSACTION
- DECLARE @ReturnCode INT
- SELECT @ReturnCode = 0
- /** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 2018-2-8 16:29:40 **/
- IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N[Uncategorized (Local)] AND category_class=1)
- BEGIN
- EXEC @ReturnCode = msdb.dbo.sp_add_category @class=NJOB, @type=NLOCAL, @name=N[Uncategorized (Local)]
- IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
- END
- DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N 索引重建,
- @enabled=1,
- @notify_level_eventlog=0,
- @notify_level_email=0,
- @notify_level_netsend=0,
- @notify_level_page=0,
- @delete_level=0,
@description=N 无描述,
- @category_name=N[Uncategorized (Local)],
- @owner_login_name=Nsa, @job_id = @jobId OUTPUT
- IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
- /** Object: Step [索引] Script Date: 2018-2-8 16:29:40 **/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N 索引,
- @step_id=1,
- @cmdexec_success_code=0,
- @on_success_action=1,
- @on_success_step_id=0,
- @on_fail_action=2,
- @on_fail_step_id=0,
- @retry_attempts=0,
- @retry_interval=0,
@os_run_priority=0, @subsystem=NTSQL,@command=N / 物料表索引 /
- ALTER INDEX [PK_BD_MATERIAL] ON [dbo].[T_BD_MATERIAL] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
- ALTER INDEX [IDX_BD_MTRL_FMASTERID] ON [dbo].[T_BD_MATERIAL] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER INDEX [IDX_BD_MTRL_COMBIN] ON [dbo].[T_BD_MATERIAL] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON), @database_name=N 数据库名称, @flags=0
- IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
- EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
- IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N 计划 1,
- @enabled=1,
- @freq_type=4,
- @freq_interval=1,
- @freq_subday_type=1,
- @freq_subday_interval=0,
- @freq_relative_interval=0,
- @freq_recurrence_factor=0,
- @active_start_date=20180208,
- @active_end_date=99991231,
- @active_start_time=230000,
- @active_end_time=235959, @schedule_uid=N4e5e768f-a2b5-4042-9766-ba8e5ebbd42e
- IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
- EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N(local)
- IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
- COMMIT TRANSACTION
- GOTO EndSave
- QuitWithRollback:
- IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
- EndSave:
- GO
索引重建任务
来源: http://www.bubuko.com/infodetail-2492221.html