SQL Server Maintenance Scripts : YourSqlDba & SQL Server Maintenance Solution

Posted By Sagar Patil

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.


Default Maintenance

YourSQLDba automated part is expressed as two tasks in the SQL Server Agent:

  • YourSQLDba_FullBackups_And_Maintenance
  • YourSQLDba_LogBackups.

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:

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

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)[1]', 'int') as PageCount,
          ExtendedInfo.value('(/ExtendedInfo/Fragmentation)[1]', 'decimal(10,4)') as Fragmentation
from commandlog
where command is not null 
and ExtendedInfo.value('(/ExtendedInfo/PageCount)[1]', 'int') is not null
and ExtendedInfo.value('(/ExtendedInfo/Fragmentation)[1]', 'decimal(10,4)') is not null

3.  Script to shrink all user SQL databases.

SELECT name FROM sys.databases WHERE is_read_only=0 AND STATE=0
  AND name NOT IN ('master','model','tempdb','msdb')
  EXEC SP_dboption @db,'trunc. log on chkpt.','true' 
  DBCC shrinkdatabase (@db)

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
          set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
          fetch next from LogFiles into @iLogFile
        set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
        --print @tsql
        close LogFiles
        DEALLOCATE LogFiles

exec sp_msforeachdb @ssql

Leave a Reply

You must be logged in to post a comment.

One Response to “ Websphere Basics ”

  1. ‘If deployment manager is down,How can the session backup information transfered to other cluster members? Is web server necessary?

    Not necessarily, no. The WAS plugin will automatically fail over requests for a down server to some other server in the same cluster. It’s up to you to configure session persistence so that the session is available to any other server in the cluster.”

    here my question is -is DMGR be clusterd ?If so ,how can i check the other clusterd member of DMGR and their status ?

Top of Page

Top menu