Lead lock, rebuild task
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.
