SQL Server Maintenance Scripts : YourSqlDba & SQL Server Maintenance Solution
If you are looking for a good TSQL packaged scripts which will do usual database maintenance activities then you are on right page. I have detailed 2 such projects which will help you to setup maintenance job in less than an hour.
Please use it at your own risk. I would highly recommend running it on test setups before adding at prod/useful boxes.
1. YourSqlDba : T-Sql Database script that automate database maintenance using SQL Agent an DB Mail. Performs optimizations (index, statistics), integritry check, databases backups (complete and tx logs).
What it does…
- Automate full backups and recurrent log backups.
- Perform update statistics (spread all of them over a specified period of time, by default a week)
- Selectively reorganize or rebuild index that show a fragmentation thresold
- Check database integrity
- Keep an up-to-date backup of MSDB which record all backup history (at the end of each full or log backup cycle)
- Clean-up history log (backup, Sql Agent, Sql maintenance)
- Recycle SQL Server error log every day (keep up to 30 archives)
- Provides simplified sp utilities to do manual backup, duplicate database, and restore database.
- Schedule agent task for maintenance
- Configure database mail for maintenance reporting
- Send e-mail that regular notify maintenance activity
- Send exceptional e-mail for notifying problematic maintenance issues
- Disable immediately databases with integrity problem
- Disable databases which failed to be backup more than 3 consecutives times (manual backup can reset the failure count)
- Connect network drives visible to database engine for backup purposes, and make them auto-reconnect at startup.
How it does it ?
- Everything is stored in a database named YourSQLDba
- Stored procedures
- Internal table (logs, and informational tracking info)
- It uses SQL Server Agent to run maintenance job
- Two job are created that use the same procedure with different parameters
- One for daily maintenance (integrity test, update statistics, reorganize or rebuild, full backups)
- One for log backups
- Job backups reset log size that grow beyond a given threshold
- It communicates to operator or Dba or whatever else is responsible (SQL Agent operator) through database mail
How to Install it?
Download script from here
Compile the script on database. You will notice 2 default jobs added as part of a install.
YourSQLDba automated part is expressed as two tasks in the SQL Server Agent:
Each task contains a single step which call YourSQLDba_DoMaint, but with different parameters.
YourSQLDba_LogBackups is quicker to explain, as it deal exclusively with log backups.
- They are performed continuouly all around the clock, at every 15 minutes (thanls to SQL Server Agent Scheduler). Some may wonder how it avoid problems like running into a concurrent attempt to do log backup at the same time there is an ongoing database backup on the same database. YourSQLDba use a feature introduced in SQL2000 to avoid, this. This is called application locks (See sp_getapplock). A log backup for a given database give up immediately, if it can’t obtain the application lock. YourSQLDba defines for both log and complete maintenance for a given database. However complete maintenance wait at least 5 minutes before giving up waiting for a lock. We suppose that 5 minutes is a reasonable time to wait for a log backup, since log backup done at every 15 minutes doesn’t have too much time to become too large, and hence long to backup.
- At log backup time, after the log is emptied by the log backup, log file that have grown beyond a certain size are shrunk. This target size is calculated this way (sum of size of primary data file + sum of size of files in default file group + 1/10 sum of the size of files outside default file group). If log size that is greater than 40% of this computation, we assumed that shrunk time has come. Why compute on 1/10 of the file outside of default file group ? Usually special filegroup are used to stored blob, or historical data, or to do table and index partionning. We have a big volume in these filegroups for which only a fraction of the content is usually changed. Log ratio size relatively to these big file need to be smaller, contrary to primary data file of default filegroup.
YourSQLDba_FullBackups_And_Maintenance needs more explanations as it performs more tasks.
- Log cleanup are performed
- Sql Server current is recycled (it means archived) and a new log is generated.
- Logs like SQL Agent backup history are limited to 30 days.
- YourSQLDba log history is limited to 30 days.
- Mail items log is limited to 30 days
- Agent job history is limited to 30 days.
- By default the procedure YourSQLDba_DoMaint is called with the option to do the following tasks.
- Check database integrity. If a database is found with any integrity problem, it is put immediately in emergency mode
- Update distribution statistics. Updates are done using full scan, but process is spread by default (parameter) on seven days.
- Reorganize or Rebuild index depending on their real level of fragmentation, base on a threshold specified internally into the procedure
- Perform full database backup
- Full database backup files older that one day (parameter) are suppressed from disk backup directory
- Log backup files older that seven day (parameter) are suppressed from disk backup directory
- A full backup file of the database is created and named this way dbname_[yyyy-mm-dd_hhhmimsss_dw].bak where dbname is the name of the database, yyyy-mm-dd HHhMImSSs_dw a timestamp plus the day name of the week and .Bak extension is the regular extension used for full database backup.
- A log backup file is created and named about the same as a full backup file except the extension which is .Trn for transaction log backups.
- Every backup cycle, whether it is full backup or transaction log backup cycle, is followed by a complete backup of MSDB. Msdb keeps tracks of all backups done and makes it easier to do other restores once it is restored since all the backup history become accurate and up-to-date.
- Backup file name and locations are retained into a table into YourSQLDba. It allows YourSQLDba to reuse the backup log file for a given database, to continue to append other log backups to the same file. It greatly reduce the number of files produced by the backup procedure, by using the same log backup file for all the day.
2. SQL Server Maintenance Solution by ola.hallengren.com
The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. The solution is based on stored procedures, functions, the sqlcmd utility, and SQL Server Agent jobs.
Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need.
Learn more about using the SQL Server Maintenance Solution:
- DatabaseBackup: SQL Server Backup
- DatabaseIntegrityCheck: SQL Server Integrity Check
- IndexOptimize: SQL Server Index and Statistics Maintenance
Helpful SQL Scripts
SQL Server Maintenance Solution logs the command executed in table dbo.CommandLog
1. Find the most expensive “SQL server maintenance” job for last one month
SELECT command,starttime, DATEDIFF(MINUTE,starttime,endtime) as duration FROM dbo.CommandLog WHERE starttime BETWEEN GETDATE()-30 AND GETDATE() ORDER BY 3 DESC
2. Find list of reorg/rebuilt indexes & their fragmentation details
select command,starttime, DATEDIFF(MINUTE,starttime,endtime) as "Duration In Mins", ExtendedInfo.value('(/ExtendedInfo/PageCount)', 'int') as PageCount, ExtendedInfo.value('(/ExtendedInfo/Fragmentation)', 'decimal(10,4)') as Fragmentation from commandlog where command is not null and ExtendedInfo.value('(/ExtendedInfo/PageCount)', 'int') is not null and ExtendedInfo.value('(/ExtendedInfo/Fragmentation)', 'decimal(10,4)') is not null ORDER BY 3 DESC
3. Script to shrink all user SQL databases.
DECLARE @db VARCHAR(255) DECLARE c CURSOR FOR SELECT name FROM sys.databases WHERE is_read_only=0 AND STATE=0 AND name NOT IN ('master','model','tempdb','msdb') OPEN c FETCH c INTO @db WHILE @@FETCH_STATUS=0 BEGIN EXEC SP_dboption @db,'trunc. log on chkpt.','true' DBCC shrinkdatabase (@db) FETCH NEXT FROM c INTO @db END CLOSE c DEALLOCATE c
4. Shrink log files for all databases
declare @ssql nvarchar(4000) set @ssql= ' if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin use [?] declare @tsql nvarchar(4000) set @tsql = '''' declare @iLogFile int declare LogFiles cursor for select fileid from sysfiles where status & 0x40 = 0x40 open LogFiles fetch next from LogFiles into @iLogFile while @@fetch_status = 0 begin set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) '' fetch next from LogFiles into @iLogFile end set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql --print @tsql exec(@tsql) close LogFiles DEALLOCATE LogFiles end' exec sp_msforeachdb @ssql