Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Create Maintenance Job with a Click without using a Wizard

By Robin Back,

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".

VARIABLE EXPLANATION COMMENT
@backuppath The path local (or on a remoteserver) that will hold the backups.  
@keep_databasebackup_days Number of days database backups should be kept on disk A full backup will be taken before deleting the old backup. The backup files are named differently every day.
@keep_transactionlog_days Number of days transactional backups should be kept on disk Old 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_northwind Are example database pubs or Northwind allowed to be installed or not If 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_plan Should the script check for existing maintenance plans Since you are about to create new maintenance jobs, there might be confusion to have more than i.e. one database backup.
@backup_mb_per_sek Estimation of how many mb the server backs up per second This 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_backup What time each day should database backup start Format has to be hhmmss.
@start_backup_trans What time each day should transaction backup start Format has to be hhmmss.

Note: will end 1 minute before database backup start
@backup_trans_hour How many hours between each transaction log backup  
@start_rebuild What time each Sunday should indexes be rebuilt Format has to be hhmmss.
@start_statistics What time each Sunday should statistics be updated Format has to be hhmmss.
@category_name What category shall the scheduled jobs have  
@description Description for all the scheduled jobs created  
@owner_login_name Name of the user that will own and execute the scheduled job If 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_eventlog Should the jobs write any record to the NT eventlog 0=never
1=on success
2=on failure
3=always
@workdir The 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 TYPE NAME STEP DESCRIPTION
System
User
BACKUP - database - (DBCC) 1. DBCC CHECKCATALOG - datbase Run DBCC CHECKCATALOG
    2. DBCC CHECKDB - database Run DBCC CHECKDB
    3. BACKUP - database - DATABASE Will 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 - database All database backups for this database older then @keep_databasebackup_days will be deleted.
User BACKUP - database - TRANSACTION 1. DELETE OLD TRANSACTION LOGS - database All transaction log backups for this database older then @keep_transactionlog_days will be deleted.
    2. BACKUP - database - TRANSACTION Will 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.
User REBUILD INDEX - database 1. REBUILD INDEX - database Will run the stored procedure REBUILD_INDEX in database and rebuild all indexes using the default fillfactor used to create the index.
User UPDATE STATISTICS - database 1. UPDATE STATISTICS - database Will 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
		from	dbo.sysobjects so
				inner join dbo.sysusers su on so.uid = su.uid
		where	so.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
		from	dbo.sysobjects so
				inner join dbo.sysusers su on so.uid = su.uid
		where	so.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.

Total article views: 9633 | Views in the last 30 days: 15
 
Related Articles
SCRIPT

Create SQL JOB to backup database and schedule it

This script create Job and backup the database.This job backup the database.This job is scheduled fo...

ARTICLE

Powershell Database Backup Script

Learn how to write a script for regular database backups using Powershell and SMO.

FORUM

Backup Schedules & Sizes Report

Backup Schedules & Sizes Report

FORUM

backup

scheduling the database backup

FORUM

hoe to stop creating a daily database backup file

stop creating a daily database backup file

Tags
administration    
miscellaneous    
monitoring    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones