Shulou(Shulou.com)06/01 Report--

When you fail to build an index using the maintenance plan under the database management function, it is more practical to use the agent to execute the job. The code is as follows:

Method 1:

USE [msdb]

GO

/ * * Object: Job [Index Reconstruction] 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

IF (@ @ ERROR 0 OR @ ReturnCode 0) GOTO QuitWithRollback

END

DECLARE @ jobId BINARY (16)

EXEC @ ReturnCode = msdb.dbo.sp_add_job @ job_name=N' Index Reconstruction'

@ enabled=1

@ notify_level_eventlog=0

@ notify_level_email=0

@ notify_level_netsend=0

@ notify_level_page=0

@ delete_level=0

@ description=N' has no description.'

@ category_name=N' [Uncategorized (Local)]'

@ owner_login_name=N'sa', @ job_id = @ jobId OUTPUT

IF (@ @ ERROR 0 OR @ ReturnCode 0) GOTO QuitWithRollback

/ * * Object: Step [Index] Script Date: 2018-2-8 16:29:40 * /

@ 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=N'TSQL',@command=N'/ Bill of Materials Index /

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' Database name', @ 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' Plan 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=N'4e5e768f-a2b5-4042-9766 Muhammad 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

Method 2:

Right-click the index, select rebuild, open database tracking, find the build statement, and paste the code into the execution plan. It is recommended to use this method.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.