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

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.

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.