Sunday, March 27, 2011

How To Create A Rebuild Job In SQL Server

Here is the script to create rebuild all indexes Job in a replication setup server.

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
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=N'JOB', @type=N'LOCAL', @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'Reindex Databasename',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'SA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [dbcc reindex] ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'dbcc reindex',
@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'dbcc dbreindex(''MSreplication_objects'')
go
dbcc dbreindex(''MSsubscription_properties'')
go
dbcc dbreindex(''MSsnapshotdeliveryprogress'')
go
dbcc dbreindex(''MSsavedforeignkeys'')
go
dbcc dbreindex(''MSsavedforeignkeycolumns'')
go
dbcc dbreindex(''MSsavedforeignkeyextendedproperties'')
go
dbcc dbreindex(''table1'')
go
dbcc dbreindex(''table2'')
go
dbcc dbreindex(''MSreplication_subscriptions'')
go
dbcc dbreindex(''MSsubscription_agents'')
go',
@database_name=N'DATABASENAME',
@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'sunday evening',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20101105,
@active_end_date=99991231,
@active_start_time=200000,
@active_end_time=235959

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
Identifying which Indexes to Rebuild. Reorganize and Rebuild Indexes. ALTER INDEX. Rebuilding Indexes. Rebuild indexes online with SQL Server 2005. How to find indexes which are candidate for rebuild. When to Rebuild Index. How to rebuild indexes quickly. how to setup rebuild index on sql server 2012. in sql server 2008. in sql server 2005. rebuild index sql server. rebuild index oracle. rebuild index in sql server 2005 rebuild index mssql. rebuild index sql 2005. rebuild index db2. mysql rebuild index. rebuild index oracle 10g

0 comments:

Post a Comment