September 12, 2014 at 9:54 am
I wanted to create a job which would runs every other Sunday. This job would reorganize indexes which are less than 30% fragmented and it would rebuild all the indexes which are more than 30% fragmented. We only have 1 DB per instance and there are 4 instances on 1 server. What I did was that I got this rebuild index script from https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html. I just created a job with this script, but I actually don't know what I am doing. It's like I am sitting in the car with the keys in my hand but I don't know how to drive and how to start. Can someone look at the code and let me know if it will work and reorganize and rebuild indexes the way I want.
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Index Rebuild and Reorganize',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'EXECUTE dbo.IndexOptimize
@databases = ''USER_DATABASES'',
@FragmentationLow = NULL,
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 40,
@UpdateStatistics = ''ALL'',
@OnlyModifiedStatistics = ''Y''',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'MERCURYPRDDB1\dba', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'Index Rebuild and Reorganize', @server_name = N'MERCURYPRDDB1\ADOBE'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Index Rebuild and Reorganize', @step_name=N'Rebuild and Reorganize Indexes',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXECUTE dbo.IndexOptimize
@databases = ''USER_DATABASES'',
@FragmentationLow = NULL,
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 40,
@UpdateStatistics = ''ALL'',
@OnlyModifiedStatistics = ''Y''',
@database_name=N'ARSystem',
@output_file_name=N'Y:\Backup\Index Job Report\Report',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Index Rebuild and Reorganize',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'EXECUTE dbo.IndexOptimize
@databases = ''USER_DATABASES'',
@FragmentationLow = NULL,
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 40,
@UpdateStatistics = ''ALL'',
@OnlyModifiedStatistics = ''Y''',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'MERCURYPRDDB1\dba',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'Index Rebuild and Reorganize', @name=N'Rebuild and Reorganize Index',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=2,
@active_start_date=20140912,
@active_end_date=99991231,
@active_start_time=3000,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
September 12, 2014 at 3:17 pm
If you do the installation of the Maintenance Solution from Ola Hallengren, it will create the jobs with the defaults for you. All you have to do is then set the schedule for when you want them to run. Ola's jobs default to run against User Databases and another for System Databases. The settings for fragmentation are set for >5% and < 30%, reorganize, else if > 30% fragmented, then rebuild.
September 12, 2014 at 3:23 pm
New Born DBA (9/12/2014)
I wanted to create a job which would runs every other Sunday. This job would reorganize indexes which are less than 30% fragmented and it would rebuild all the indexes which are more than 30% fragmented. We only have 1 DB per instance and there are 4 instances on 1 server. What I did was that I got this rebuild index script from https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html. I just created a job with this script, but I actually don't know what I am doing. It's like I am sitting in the car with the keys in my hand but I don't know how to drive and how to start. Can someone look at the code and let me know if it will work and reorganize and rebuild indexes the way I want.
USE [msdb]@FragmentationLevel1 = 5,
@FragmentationLevel2 = 40,
Looking at the code you posted, you have it set for >5% and < 40% (reorg) else >40% (rebuild).
September 12, 2014 at 3:33 pm
To answer another side of it, it appears you have it set to run every 2 weeks (every other week) on Sunday at 12:30AM. Sorry I didn't put all of these in one response.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply