Jobs are a great automation tool in SQL Server and like every great resource should be used with care and planing. Follow following base lines to get more out of this facility.
- Avoid overlapping jobs on the same SQL Server instance. Ideally, each job should run separately at different times.
- When creating jobs, be sure to include error trapping, log job activity, and set up alerts so you know instantly when a job fails.Create a special SQL Server login account whose sole purpose is to run jobs, and assign it to all jobs.
- If your jobs include Transact-SQL code, ensure that it is optimized to run efficiently.
- Periodically (daily, weekly, or monthly) perform a database reorganization on all the indexes on all the tables in all your database. This will rebuild the indexes so that the data is no longer logically fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance. Reindexing tables will also update column statistics.
- Don’t reindex your tables when your database is in active production, as it can lock resources and cause your users performance problems. Reindexing should be scheduled during down times, or during light use of the databases.
- At least every two weeks, run DBCC CHECKDB on all your databases to verify database integrity.
- Avoid running most DBCC commands during busy times of the day. These commands are often I/O intensive and can reduce performance of the SQL Server, negatively affecting users.
- If you rarely restart the mssqlserver service, you may find that the current SQL Server log gets very large and takes a long time to load and view. You can truncate (essentially create a new log) the current server log by running DBCC ERRORLOG. Set this up as a weekly job.
- Script all jobs and store these scripts in a secure area so they can be used if you need to rebuild the servers.
Chosen from SQL Server DBA best practices By Brad M.mcGehee