• Hmmm, looks like this didn't copy/paste over correctly. Try this to create the job:

    USE [msdb]

    GO

    /****** Object: Job [DBA_Reports - Laptop Fragmentation] Script Date: 09/12/2011 09:40:57 ******/

    BEGIN TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    /****** Object: JobCategory [DBA Monitoring] Script Date: 09/12/2011 09:40:57 ******/

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA Monitoring' AND category_class=1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA Monitoring'

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

    END

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_Reports - Laptop Fragmentation',

    @enabled=1,

    @notify_level_eventlog=2,

    @notify_level_email=2,

    @notify_level_netsend=0,

    @notify_level_page=0,

    @delete_level=0,

    @description=N'Notifies laptop user that db maintenance will begin

    Checks level of fragmentation on each laptop

    Creates scripts to either redinex or reorganize

    Send mail with list of laptops with fragmentation

    Send mail with scripts to run

    Notifies users that db maintenance is completed

    Sends admin email notifying who was defragged today

    Sends admin email notifying who has not defragged for at least 4 weeks

    Send admin email on which indexs were defragged',

    @category_name=N'DBA Monitoring',

    @owner_login_name=N'sa',

    @notify_email_operator_name=N'<add your info>', @job_id = @jobId OUTPUT

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

    /****** Object: Step [Clean Up Temp tables] Script Date: 09/12/2011 09:40:58 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Clean Up Temp tables',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @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'TRUNCATE TABLE FragmentedTables

    TRUNCATE Table dbo.FragmentedTablesAfterDefragRun',

    @database_name=N'DBA_Reports',

    @flags=0

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

    /****** Object: Step [Call Fragmentation SPROC] Script Date: 09/12/2011 09:40:58 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Call Fragmentation SPROC',

    @step_id=2,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @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'exec SelectAllServersFragmentedTables

    ',

    @database_name=N'DBA_Reports',

    @output_file_name=N'E:\Morning Reports\FragmentationOutputReport.txt',

    @flags=4

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

    /****** Object: Step [Send email of who was defragged today] Script Date: 09/12/2011 09:40:58 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send email of who was defragged today',

    @step_id=3,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @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'EXEC msdb.dbo.sp_start_job ''DBA_Reports - Report Server Fragmentaion Defragged Today''',

    @database_name=N'DBA_Reports',

    @flags=0

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

    /****** Object: Step [send email for Laptops not Defragged for at least 4 weeks] Script Date: 09/12/2011 09:40:58 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'send email for Laptops not Defragged for at least 4 weeks',

    @step_id=4,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @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'EXEC msdb.dbo.sp_start_job ''DBA_Reports - Report Laptops not Defragged for at least 4 weeks''',

    @database_name=N'DBA_Reports',

    @flags=0

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

    /****** Object: Step Script Date: 09/12/2011 09:40:58 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'email what was defragged and initial numbers',

    @step_id=5,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_success_step_id=0,

    @on_fail_action=3,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=N'EXEC msdb.dbo.sp_start_job ''DBA_Reports - Report Servers Defragged Today''',

    @database_name=N'DBA_Reports',

    @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'schedule',

    @enabled=1,

    @freq_type=8,

    @freq_interval=62,

    @freq_subday_type=1,

    @freq_subday_interval=0,

    @freq_relative_interval=0,

    @freq_recurrence_factor=1,

    @active_start_date=20110523,

    @active_end_date=99991231,

    @active_start_time=131500,

    @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:

    GO

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook