Results 1 to 6 of 6

Thread: job creation

  1. #1
    Join Date
    Jun 2003
    Location
    Israel
    Posts
    17

    job creation

    Hello to all!
    I have a procedure "rebuild_index" and I would like to create a job running that procedure.
    May someone send me a script (template) how to create this job, the most important:
    When I click on job's properties->Steps->Edit->General->command I could change the database name, because I have a lot of servers and databases, so this way I could change only DB name in properties (not changing db name in job script)
    Thank you very much for your help!


    create proc p_rebuild_index as
    declare @name varchar(100),
    @string varchar(200)
    declare c1 cursor for select name from sysobjects where type = 'U'
    open c1
    fetch c1 into @name
    while @@fetch_status = 0
    begin
    set @string = 'dbcc dbreindex([' + rtrim(@name ) + '],"",85)'
    execute (@string)
    --print @string
    fetch c1 into @name
    end
    close c1
    deallocate c1

    GO

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    1. you can create maintenance plan for this. that is very easy.
    or

    2. you can add this statement in tht job which will reindex all the tables in the database your select.

    set quoted_identifier off
    go
    exec sp_msforeachtable 'dbcc dbreindex ("?"," ",85)'

  3. #3
    Join Date
    Jun 2003
    Location
    Israel
    Posts
    17
    Thanx!!!!!!

  4. #4
    Join Date
    Jun 2003
    Location
    Israel
    Posts
    17
    Sorry for the stupid question:

    Example:
    sp_add_maintenance_plan N'plan_name' , 'plan_id' OUTPUT /creates a new, but empty, maintenance plan
    sp_add_maintenance_plan_db N 'plan_id' , 'database_name' / associates a database(s) with a maintenance plan
    sp_add_maintenance_plan_job N 'plan_id' , 'job_id' /associates a maintenance plan with an existing job(s).

    How do I execute my maintenance plan?

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    refere BOL for xp_sqlmaint

    it would appear like the below example. you can set it up as a job. Always when your create maintenance with schedule, sql server will create all the jobs for you.

    --For backup
    EXECUTE master.dbo.xp_sqlmaint N'-PlanID FD009C69-D7BC-41C7-A156-2FA24A2073F0 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 3DAYS -CrBkSubDir -BkExt "BAK"'

    --For DBCC
    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 827C1D0C-585B-4737-965C-EE44D6E0C328 -Rpt "d:\MSSQL70\LOG\DBCC and Reindex2.txt" -DelTxtRpt 1WEEKS -WriteHistory -CkDB '

    --For Reindex
    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 827C1D0C-585B-4737-965C-EE44D6E0C328 -Rpt "d:\MSSQL70\LOG\DBCC and Reindex0.txt" -DelTxtRpt 1WEEKS -WriteHistory -RebldIdx 20 '

  6. #6
    Join Date
    Jun 2003
    Location
    Israel
    Posts
    17
    Thank you!!!! Great!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •