-
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
-
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)'
-
-
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?
-
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 '
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|