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
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:
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".
|@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|
|@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.
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.
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.
|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:
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:
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.
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
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
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:
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.
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.
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?
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.
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