SQL Server Performance Forum – Threads Archive
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
You could run SP_MSFOREACHDB ‘DBCC DBREINDEX’ and this is an undocumented stored procedure which performs the job on all the databases. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thank you for your responce!
But I wanted some unique script for all jobs I’m running: full backup, log backup , rebuild index etc…..
I want a script that will create a job and I will give a database name via jobs properties, not in script.
Actually I have: BEGIN — Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N’rebuild_indexes’, @owner_login_name = N’sa’, @description = N’No description available.’, @category_name = N’Database Maintenance’, @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback — Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N’run_sp’, @command = N’exec P_REBUILD_ALL_INDEXES’, @database_name = N’DB’, @server = N”, @database_user_name = N”, @subsystem = N’TSQL’, @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N”, @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback — Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N’sp_schedule’, @enabled = 1, @freq_type = 8, @active_start_date = 20000215, @active_start_time = 203000, @freq_interval = 31, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback — Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
DB Maintenance Planner is a nice option, but I prefer to creat jobs this way (via script)
If you require to perform maint. tasks for all the databases, better to use MAINT.PLANS which will ease the job and take care every aspect of optimization. Also creating seperate Maint.plans for backups and optimization and schedule accordingly will be fine, so far so good on all my servers. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Marina, Kindly do not post same question in multiple forums, I have deleted the other post and moving this thread to General DBA section. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Ok, thank you very much for you advice, I think I should change my jobs "creation" method.
Sorry for the same topic, I just was not sure about which section better convenient.
And if you require to run DBCC command against all the databases, refer the SP_MSFOREACHDB method as referred above. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
To rebuild indexes in a database I use a 2 stored procedure and a job. procedure_1
===========
Accepts as a parameter the table name (or ‘%’ for all tables). There are 2 options here 1 – put in a table datatype the name of all tables that are not system table and rebuild
all indexes for all ot them. 2 – put into a temp table the results set of DBCC SHOWCONTIG with TABLERESULTS, and
rebuild indexes according to the ScanDensity. In this case you could pass the
scan density as a parameter. In either case you would loop over the table you created and run DBCC DBREINDEX on each
one of them. procedure_2
=========== Accepts as a parameter the database name (or ‘%’ for all).
Put into a table all databases that are not ‘master’,’model’,’msdb’,’tempdb’
Loop over this table results and call for each database
exec procedure_1 ‘database_name’ Job
===
Now you can create a job that will simply call store procedure_2.
You can do it in one step with exec procedure_2 ‘%’, or in different steps with
a specific call to each database.
It might sound more complicated in a first glance, but I find it more simple flexibale
and easier to manage. Bambola.
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?
By scheduling them and using SP_START_JOB from QA or from EM goto Management –> Database Maintenance Plans —> Right click on the plan and schedule the package. And also using sp_add_maintenance_plan_job.
Refer to books online for more information on specified SPs. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I’m pretty new in Ms SQL, I was always working with Oracle most of the time!
and I would like to say that more I know about MS SQL more I like it!
Thank you!
Thats the charm behind MS SQL Server and you will gain equally.
Always take help of books online as a first hand information and anyway this forum is available to take you out from issues. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
–create stored procedure
1.use master
CREATE procedure dbo.mnt_OptimizeDatabases
As /*Procedure to optimize databases which are ‘ONLINE’*/
declare @dbname varchar(50), @dbid smallint
declare @svr varchar(50), @cmd nvarchar(255) declare dbnamecur cursor for
select [name], dbid, @@servername as server from dbo.sysdatabases
where name not in (‘master’,’tempdb’,’msdb’,’model’)
AND CONVERT(sysname, DatabasePropertyEx(name,
‘Status’))<>’OFFLINE’
open dbnamecur
Fetch next from dbnamecur into
@dbname, @dbid, @svr While @@fetch_status = 0
Begin select @cmd = ‘exec xp_sqlmaint ‘ + char(39) + ‘-S’ + char(32) + @svr + char(32) +
‘-D’ + char(32) + @dbname + char(32) + ‘ -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 ‘ + char(39)
— Execute command to place a record in table that holds backup names
exec sp_executesql @cmd
–Print ‘database ‘ + @dbname + ‘ has full recovery and has been backed up on ‘ + @svr Fetch next from dbnamecur into
@dbname, @dbid, @svr
END
close dbnamecur
deallocate dbnamecur
GO
2.schedule job to execute this script
NewDBA, thank you very much for your script!!!!!!!
]]>