SQLServerCentral Article

Create Maintenance Job with a Click without using a Wizard

,

I have found, working at company using a few hundred SQL servers, how much time I spent to track if a database has required database- and transaction log backups. We also had a few different ways of getting reports of the jobs to know if they had run

successfully or not. We all know the importance of standards, don't we?

At our company we use a monitoring product called Tivoli

(www.tivolia.com) to check that i.e. that SQL server is accessible and that disks are not filled up and so on. I come to the

conclusion that our dba group should use this tool for monitoring our standard jobs for database maintenance, and also to get our Control Center to call our emergency service whenever anything goes wrong.

We have a policy to use SQL Server Agent for backing up both databases and transaction logs (when needed), we also provide a complete rebuild of all indexes once a week (or more often if any customer would like to), and an update of the statistics if for any reason we can't let SQL server handle this feature itself. Tivoli can also report to us whenever a job has not been run as scheduled for any reason. I have stripped the code from our special things that Tivoli needed so that you can use it in your

environment.

Now we take one server at the time and replace any existing maintenance job with the new ones. Doing this we will for sure have control of the maintenance of all the sql-servers out there that we have contracts to take care of. It's a enormously job to change all of the jobs with

different times so that transactional backups does not cross any database backup and so on. Also to have indexes rebuilt run on different times so that the server does not have to rebuild indexes for more then one database at the time, or even different days for

performance reasons. That is why I have developed this script for saving time, and to avoid any typos or other human

mistakes, no one does those - right?

Before running the script

The script has been tested on SQL 7 and SQL 2000:

create_maintenance_jobs.sql

Change parameters

First time you run the script I suggest that you take a close look at all the parameters described and make necessary changes before running the script. The section for changing the parameters are found in the script under "Variables that are ok to change".

VARIABLEEXPLANATIONCOMMENT
@backuppathThe path local (or on a remoteserver) that will hold the backups. 
@keep_databasebackup_daysNumber of days database backups should be kept on diskA full backup will be taken before deleting the old backup. The backup files are named differently every day.
@keep_transactionlog_daysNumber of days transactional backups should be kept on diskOld transaction logs will be deleted before backing up the current one (different from database backups there backups are deleted after current backup. This is because there should always be at least one database backup on disk.
@check_pubs_northwindAre example database pubs or Northwind allowed to be installed or notIf yes (1) you will be prompted to delete those databases before

being able to run the script (if one or both are present).

If no (0) still no maintenance jobs will be created for those databases.

@check_maintenance_planShould the script check for existing maintenance plansSince you are about to create new maintenance jobs, there might be confusion to have more than i.e. one database backup.
@backup_mb_per_sekEstimation of how many mb the server backs up per secondThis parameter is used for schedule up so that the different jobs do not conflict with each other. The parameter does not take any notice of how much of the used part of the files, just how much space the files allocate on disk.
@start_backupWhat time each day should database backup startFormat has to be hhmmss.
@start_backup_transWhat time each day should transaction backup startFormat has to be hhmmss.

Note: will end 1 minute before database backup start

@backup_trans_hourHow many hours between each transaction log backup 
@start_rebuildWhat time each Sunday should indexes be rebuiltFormat has to be hhmmss.
@start_statisticsWhat time each Sunday should statistics be updatedFormat has to be hhmmss.
@category_nameWhat category shall the scheduled jobs have 
@descriptionDescription for all the scheduled jobs created 
@owner_login_nameName of the user that will own and execute the scheduled jobIf the user is a member of the sysadmin role the user rights of the user that run SQL Server Agent will be used. If not a member of sysadmin role the proxy user (if present) will be used. The user that executes the scheduled job has to have write access to both the backup folder as well as to the folder @workdir\LOG.
@notify_level_eventlogShould the jobs write any record to the NT eventlog0=never
1=on success
2=on failure
3=always
@workdirThe script will check the registry for the SQL Installation path, normally: "C:\Program Files\Microsoft SQL Server\MSSQL".The account that execute the script has to have read permissions in the registry.

If changing this variable, make sure you un-comment that row in the script.

Permissions

Make sure that all permissions are met in the description of the parameters. The user that executes the script also might to have the correct permissions to create the @workdir directory and it's subfolders "JOBS" and "LOG" if not present on disk.

Running the script

Objects in the databases

The first thing the script will do is to check that anything that the script creates do not already exist at the server. If there for example are a scheduled job with the same name as any of the ones that will be created, you will be prompted to delete the scheduled job (or simply rename it). The same goes for the stored procedures that will be created in all the user databases. Make sure to read each line carefully before running the output in another window, or as I would suggest, delete (or rename) everything by hand. The output might look someting like:

-- 1. Delete stored procedure 'REBUILD_INDEX' in database 'LSIPT100'
use LSIPT100
drop proc REBUILD_INDEX
go
-- 2. Delete stored procedure 'REBUILD_INDEX' in database 'DOCUMENTS'
use DOCUMENTS
drop proc REBUILD_INDEX
go

What do the script create

Folders on disk

The server fetch the registry value where of SQL Server was installed (by default "C:\Program Files\Microsoft SQL Server\MSSQL"). If this path does not exist at the server, it will be created. Also there are two sub-folder that are created by default when installing SQL-server and that is "JOBS" and "LOG", these will also be created if they not exist.

Scheduled Jobs

All scheduled jobs will stop if any of the step fails, and reports the error as set in the parameters @notify_level_eventlog. No scheduled jobs will be created for the example databases pubs and Northwind. Note that "database" stands for the name of the database the scheduled job affects.

DATABASE TYPENAMESTEPDESCRIPTION
System
User
BACKUP - database - (DBCC)1. DBCC CHECKCATALOG - datbaseRun DBCC CHECKCATALOG
  2. DBCC CHECKDB - databaseRun DBCC CHECKDB
  3. BACKUP - database - DATABASEWill preform a full backup of the database to disk.

The filename will describe what database it's used for and what day the backup started:
database_BACKUP_20030718.BAK

Note that no backup will be perfomed if any error is found in one of the two dbcc checks.

  4. DELETE OLD DATABASE BACKUPS - databaseAll database backups for this database older then @keep_databasebackup_days will be deleted.
UserBACKUP - database - TRANSACTION1. DELETE OLD TRANSACTION LOGS - databaseAll transaction log backups for this database older then @keep_transactionlog_days will be deleted.
  2. BACKUP - database - TRANSACTIONWill perform a transaction log backup of the database to disk. The scheduled job will be created but disabled if database option "Truncate Log On Checkpoint" is enabled.

The filename will describe what database it's used for and what day the backup started:
database_BACKUP_TRANSACTION_20030718.TRN

Note that script will append all transaction log backups for each day in one file per day.

UserREBUILD INDEX - database1. REBUILD INDEX - databaseWill run the stored procedure REBUILD_INDEX in database and rebuild all indexes using the default fillfactor used to create the index.
UserUPDATE STATISTICS - database1. UPDATE STATISTICS - databaseWill run the stored procedure UPDATE_STATISTICS in database and update all the statistics for the database.

Note that this job will only be created if for any reason the options has been disabled for SQL-server to

perform this by itself.

Scheduled Job Category

If the scheduled job category set in @category_name does not exist, it will be created.

Stored Procedures

REBUILD_INDEX

The stored procedure will re-create all the indexes in the database using the fillfactor used when creating the index.

create procedure REBUILD_INDEX
as
declare @tablename varchar(255)
declare @tableowner varchar(255)
declare @tablename_header varchar(600)
declare @sql varchar(600)
declare tnames_cursor CURSOR FOR
select'tablename'=so.name,
'tableowner'=su.name
fromdbo.sysobjects so
inner join dbo.sysusers su on so.uid = su.uid
whereso.type = 'U'
open tnames_cursor
fetch next from tnames_cursor into @tablename, @tableowner
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
select @tablename_header = '***** Updating ' + rtrim(upper(@tablename)) + ' (' + convert(varchar, getdate(), 20) + ') *****'
print @tablename_header
select @sql = 'dbcc dbreindex ( ''' + @tableowner + '.' + @tablename + ''','''',0 )'
exec ( @sql )
end
fetch next from tnames_cursor into @tablename, @tableowner
end
print ''
print ''
print '***** DBReindex have been updated for all tables (' + convert(varchar,getdate(),20) + ') *****'
close tnames_cursor
deallocate tnames_cursor

UPDATE_STATISTICS

The stored procedure update all the statistics in the database. Note that this stored procedure will only be created if for any reason the options has been disabled for SQL-server to

perform this by itself.

create procedure UPDATE_STATISTICS
as
declare @tablename varchar(255)
declare @tableowner varchar(255)
declare @tablename_header varchar(600)
declare @sql varchar(600)
declare tnames_cursor CURSOR FOR
select'tablename'=so.name,
'tableowner'=su.name
fromdbo.sysobjects so
inner join dbo.sysusers su on so.uid = su.uid
whereso.type = 'U'
open tnames_cursor
fetch next from tnames_cursor into @tablename, @tableowner
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
select @tablename_header = '***** Updating ' + rtrim(upper(@tablename)) + ' (' + convert(varchar, getdate(), 20) + ') *****'
print @tablename_header
select @sql = 'update statistics ' + @tableowner + '.' + @tablename
exec ( @sql )
end
fetch next from tnames_cursor into @tablename, @tableowner
end
print ''
print ''
print '***** Statistics has been updated for all tables (' + convert(varchar,getdate(),20) + ') *****'
close tnames_cursor
deallocate tnames_cursor

Tables

Following tables will be created in tempdb and dropped when the script finish. Note that if one of the tables already exist in tempdb it will be dropped without any notification:

temporary_table_directory

temporary_table_db

temporary_table_dbsize

temporary_table_sproc

Logs

Each step in every scheduled job will generate a log-file in the default SQL-server installation folder "LOG".

The name convention is the name of the step followed by the file extension ".LOG". All white spaces in the name are replaced with an

underscore "_". All the steps are set to overwrite any existing logfile with the same name. The

easiest way to access the correct log is to right-click the job, select the Steps tab. Double click the

desired step and select the Advanced tab and click the button View.

After running the script

Check the jobs

Browse through the scheduled job to see that everything looks like you expect it to look.

Check job schedule

Double check that the time schedule the job will be executed are what you expect it to be.

Test run all jobs

You might call me schizophrenic, but I always see to that all the scheduled jobs really run without any errors.

Database backups

Does the database backup exist where you said it should? You even might want to make a test-restore of the database backup.

Transaction log backup

Does the transaction log backup exist where you said it should? You even might want to make a test-restore of the transaction log backup.

Check logs

The errorlogs might look overkill, but they are really helpful whenever anything has gone wrong, and you simply don't want to re-run anything if it's not necessarily to do so. Might be for example a index rebuild that take very long time to run, or that you only can run it off office-hours.

After a week or two

Check database and transaction log backups

Do only the backup exist on disk that should exist according to the variables @keep_databasebackup_days and @keep_transactionlog_days was set to?

Re-schedule jobs

Since the variable @backup_mb_per_sek was an estimation, you might have to re-schedule some of the jobs, if you feel that it's not ok for some of the jobs to conflict with each other.

SUMMARY

You should of course customize the jobs created or the script to meet your companys need. I have, as mentioned before, stripped the script from everything that I not find useful for everyone. You could for example set the jobs to notify you by email or net send (Notification tab in scheduled job properties).

Note that the script uses the undocumented extended stored procedures "xp_regread" and "xp_fileexist". This article of Alexander Chigrik provides an

explanation of these two procedures among with some other undocumented extended stored

procedures.

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating