-
Maintenacne Plan creation


USE [msdb]
GO

DECLARE @sqlInstance varchar(20)
SET @sqlInstance = '$(instanceName)'

DECLARE @BackUpSubPlan sysname
SET @BackUpSubPlan = 'Back Up Database Task'
DECLARE @BackUpJobName sysname
SET @BackUpJobName = 'System databases.' + @BackUpSubPlan

DECLARE @ChkDbSubPlan sysname
SET @ChkDbSubPlan = 'Check Database Integrity Task'
DECLARE @ChkDbJobName sysname
SET @ChkDbJobName = 'System databases.' + @ChkDbSubPlan

DECLARE @RebuildIndexSubPlan sysname
SET @RebuildIndexSubPlan = 'Rebuild Index Task'
DECLARE @RebuildIndexJobName sysname
SET @RebuildIndexJobName = 'System databases.' + @RebuildIndexSubPlan

DECLARE @UpdStatsSubPlan sysname
SET @UpdStatsSubPlan = 'Update Statistics Task'
DECLARE @UpdStatsJobName sysname
SET @UpdStatsJobName = 'System databases.' + @UpdStatsSubPlan

DECLARE @HistClnSubPlan sysname
SET @HistClnSubPlan = 'History Cleanup Task'
DECLARE @HistClnJobName sysname
SET @HistClnJobName = 'System databases.' + @HistClnSubPlan

DECLARE @MaintClnSubPlan sysname
SET @MaintClnSubPlan = 'Maintenance Cleanup Task'
DECLARE @MaintClnJobName sysname
SET @MaintClnJobName = 'System databases.' + @MaintClnSubPlan

DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

BEGIN TRANSACTION

DECLARE @job_id BINARY(16)

/******   Create JobCategory [Database Maintenance]    ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
  EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
 
SET @job_id = NULL

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@BackUpJobName,
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'polkj11',
@job_id = @job_id OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [back up database task]    Script Date: 08/13/2015 10:17:03 ******/
DECLARE @command VARCHAR(max)
SET @command = '/Server ' + @sqlInstance +  '/SQL "Maintenance Plans\' + 'System databases' + '" /SET "\Package\'+@BackUpSubPlan+'.Disable";false'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@job_id, @step_name=@BackUpSubPlan,
@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'SSIS',
@command=@command,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @job_id, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

DECLARE @schedule_uid uniqueidentifier, @schedule_id int
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@job_id, @name=@BackUpJobName,
@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=20150813,
@active_end_date=99991231,
@active_start_time=200000,
@active_end_time=235959,
@schedule_uid=@schedule_uid OUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SELECT @schedule_id = schedule_id FROM msdb.dbo.sysschedules WHERE schedule_uid = @schedule_uid

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @job_id
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

INSERT sysmaintplan_subplans (
  subplan_id, subplan_name, subplan_description, plan_id, job_id, msx_job_id, schedule_id, msx_plan
) VALUES  (
  '2484EB0D-5195-4873-834F-526BCEAB44F1',
  @BackUpSubPlan,
  @BackUpJobName, --description
  '$(PlanId2)',
  @job_id,
  NULL,
  @schedule_id,
  0  -- msx_plan - bit
)
/*  ******************************************************************************************************/
 
SET @job_id = NULL

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@ChkDbJobName,
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'polkj11',
@job_id = @job_id OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [back up database task]    Script Date: 08/13/2015 10:17:03 ******/
SET @command = '/Server ' + @sqlInstance +  '/SQL "Maintenance Plans\' + 'System databases' + '" /SET "\Package\'+@ChkDbSubPlan+'.Disable";false'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@job_id, @step_name=@ChkDbSubPlan,
@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'SSIS',
@command=@command,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @job_id, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @schedule_uid = NULL
SET @schedule_id = NULL

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@job_id, @name=@ChkDbJobName,
@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=20150813,
@active_end_date=99991231,
@active_start_time=210000,
@active_end_time=235959,
@schedule_uid=@schedule_uid OUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SELECT @schedule_id = schedule_id FROM msdb.dbo.sysschedules WHERE schedule_uid = @schedule_uid

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @job_id
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

INSERT sysmaintplan_subplans (
  subplan_id, subplan_name, subplan_description, plan_id, job_id, msx_job_id, schedule_id, msx_plan
) VALUES  (
  '056B83CB-0333-47F9-9A7C-ED2F4B485068',
  @ChkDbSubPlan,
  @ChkDbJobName, --description
  '$(PlanId3)',
  @job_id,
  NULL,
  @schedule_id,
  0  -- msx_plan - bit
  )
/*  ******************************************************************************************************/
 
SET @job_id = NULL


EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@RebuildIndexJobName,
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'polkj11', @job_id = @job_id OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [back up database task]    Script Date: 08/13/2015 10:17:03 ******/
SET @command = '/Server ' + @sqlInstance +  '/SQL "Maintenance Plans\' + 'System databases' + '" /SET "\Package\'+@RebuildIndexSubPlan+'.Disable";false'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@job_id, @step_name=@RebuildIndexSubPlan,
@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'SSIS',
@command=@command,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @job_id, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @schedule_uid = NULL
SET @schedule_id = NULL

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@job_id, @name=@RebuildIndexJobName,
@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=20150813,
@active_end_date=99991231,
@active_start_time=220000,
@active_end_time=235959,
@schedule_uid=@schedule_uid OUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SELECT @schedule_id = schedule_id FROM msdb.dbo.sysschedules WHERE schedule_uid = @schedule_uid

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @job_id
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

INSERT sysmaintplan_subplans (
  subplan_id, subplan_name, subplan_description, plan_id, job_id, msx_job_id, schedule_id, msx_plan
) VALUES  (
  'A5936E09-3718-4AB3-BA9C-CC3F0E1F1A7C',
  @RebuildIndexSubPlan,
  @RebuildIndexJobName, --description
  '$(PlanId4)',
  @job_id,
  NULL,
  @schedule_id,
  0  -- msx_plan - bit
  )
/*  ******************************************************************************************************/
 
SET @job_id = NULL

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@MaintClnJobName,
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'polkj11', @job_id = @job_id OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [back up database task]    Script Date: 08/13/2015 10:17:03 ******/
SET @command = '/Server ' + @sqlInstance +  '/SQL "Maintenance Plans\' + 'System databases' + '" /SET "\Package\'+@MaintClnSubPlan+'.Disable";false'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@job_id, @step_name=@MaintClnSubPlan,
@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'SSIS',
@command=@command,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @job_id, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @schedule_uid = NULL
SET @schedule_id = NULL

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@job_id, @name=@MaintClnJobName,
@enabled=1,
@freq_type=8,
@freq_interval=64,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20150813,
@active_end_date=99991231,
@active_start_time=220000,
@active_end_time=235959,
@schedule_uid=@schedule_uid OUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SELECT @schedule_id = schedule_id FROM msdb.dbo.sysschedules WHERE schedule_uid = @schedule_uid

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @job_id
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

INSERT sysmaintplan_subplans (
  subplan_id, subplan_name, subplan_description, plan_id, job_id, msx_job_id, schedule_id, msx_plan
) VALUES  (
  'CDF78EC4-D99B-466A-8FED-A53CFE1C6590',
  @MaintClnSubPlan,
  @MaintClnJobName, --description
  '$(PlanId1)',
  @job_id,
  NULL,
  @schedule_id,
  0  -- msx_plan - bit
  )
/*  ******************************************************************************************************/
 
SET @job_id = NULL

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@UpdStatsJobName,
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'polkj11', @job_id = @job_id OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [back up database task]    Script Date: 08/13/2015 10:17:03 ******/
SET @command = '/Server ' + @sqlInstance +  '/SQL "Maintenance Plans\' + 'System databases' + '" /SET "\Package\'+@UpdStatsSubPlan+'.Disable";false'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@job_id, @step_name=@UpdStatsSubPlan,
@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'SSIS',
@command=@command,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @job_id, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @schedule_uid = NULL
SET @schedule_id = NULL

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@job_id, @name=@UpdStatsJobName,
@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=20150813,
@active_end_date=99991231,
@active_start_time=230000,
@active_end_time=235959,
@schedule_uid=@schedule_uid OUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SELECT @schedule_id = schedule_id FROM msdb.dbo.sysschedules WHERE schedule_uid = @schedule_uid

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @job_id
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

INSERT sysmaintplan_subplans (
  subplan_id, subplan_name, subplan_description, plan_id, job_id, msx_job_id, schedule_id, msx_plan
) VALUES  (
  '93699B02-2347-41C2-88E2-F03FDE100EC5',
  @UpdStatsSubPlan,
  @UpdStatsJobName, --description
  '$(PlanId5)',
  @job_id,
  NULL,
  @schedule_id,
  0  -- msx_plan - bit
  )
/*  ******************************************************************************************************/
 
SET @job_id = NULL

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@HistClnJobName,
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'polkj11', @job_id = @job_id OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [back up database task]    Script Date: 08/13/2015 10:17:03 ******/
SET @command = '/Server ' + @sqlInstance +  '/SQL "Maintenance Plans\' + 'System databases' + '" /SET "\Package\'+@HistClnSubPlan+'.Disable";false'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@job_id, @step_name=@HistClnSubPlan,
@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'SSIS',
@command=@command,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @job_id, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @schedule_uid = NULL
SET @schedule_id = NULL

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@job_id, @name=@HistClnJobName,
@enabled=1,
@freq_type=8,
@freq_interval=64,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20150813,
@active_end_date=99991231,
@active_start_time=210000,
@active_end_time=235959,
@schedule_uid=@schedule_uid OUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SELECT @schedule_id = schedule_id FROM msdb.dbo.sysschedules WHERE schedule_uid = @schedule_uid

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @job_id
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

INSERT sysmaintplan_subplans (
  subplan_id, subplan_name, subplan_description, plan_id, job_id, msx_job_id, schedule_id, msx_plan
) VALUES  (
  '56F9FB48-2886-4D53-833A-4CED563404A3',
  @HistClnSubPlan,
  @HistClnJobName, --description
  '$(PlanId0)',
  @job_id,
  NULL,
  @schedule_id,
  0  -- msx_plan - bit
)

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

COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:


ALTER LOGIN [sa] DISABLE
GO



Comments

Post a Comment

Popular posts from this blog

Netezza Commands

-
Table Size  select tablename, used_bytes/pow(1024,4) as used_TB   from _v_table_storage_stat  where tablename  in (  select tablename from _v_table where tablename not like  '_VT_%' and tablename not like '_T_%'); Objects owned by a user: select o.objname, d.database, oc.classname, u.usename from _t_object o, _t_user u, _t_object_classes oc, _v_database d where u.usename='username' and u.usesysid=o.objowner and o.objclass=oc.objclass and o.objdb=d.objid; Tip: Use NZ_HELP to get   syntax of all commands Like -    nz_help | grep grant [nz@dfw86inzb-04 ~]$ nz_help | grep grant nz_db_group_access_listing           To show what groups have been granted access to what databases. nz_db_user_access_listing            To show what users have been granted access to what databases. nz_ddl_grant_group    ...
Read more

MS SQL Server Tips

-
Image
Unable to Shrink the DataFile If you are unable to shrink the datafile then try the following Error :  File ID 3 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Solution:   Add at least 2-3 MB to the data file and start shrinking again. Index Fragmentation Level SELECT distinct dbschemas.[name] as 'Schema', dbtables.[name] as 'Table',  dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count, i.rowcnt as 'totalrows' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats....
Read more

MongoDB Version Upgrade 3.4 to 3.6

-
Upgrade of Mongo 3.4 to 3.6 Please follow below steps to upgrade from Mongo 3.4 to 3.6 Pre-requisites: 1.        Make sure your Mongo version running on Mongo 3.4 2.        Connect to Mongo Shell and run the below command on the Primary Node a.        db.adminCommand( { setFeatureCompatibilityVersion: "3.4" } ) POC:PRIMARY> db.adminCommand( { setFeatureCompatibilityVersion: "3.4" } ) { "ok" : 1 } POC:PRIMARY> 3.        Make sure you have the Mongo 3.6 binaries ready Upgrade Procedure: 1.        Stop the Mongod service on one of the secondary node Sudo /etc/init.d/monogd stop 2.        Un-tar the Mongo.3.6.tz file to a location [root@dfwlncmgdb-06 kolguk01]# tar -xvf mongodb-linux-x86_64-rhel62-3.6.3.tgz 3.          Replace the...
Read more