YourSQLDba的翻译(七)

YourSQLDba的翻译(七)

YourSQLDba是可定制化的，不过在定制之前，你需要知道YourSQLDba默认做了什么
YourSQLDba 自动部分包括两个任务 在SQLSERVER代理里
（1）YourSQLDba_FullBackups_And_Maintenance
（2）YourSQLDba_LogBackups. 

每个任务包含单独的步骤，每个步骤都会调用YourSQLDba_DoMaint存储过程，不过只是调用时候的参数不一样已而
YourSQLDba_LogBackups任务可以快点解释完，它负责日志备份
他们执行不间断的日志备份大概每15分钟，这个是由SQLSERVER代理来调度的。
一些人会问：他怎么避免在运行的时候数据库遇到高并发的时候备份事务日志出问题。
YourSQLDba使用了一个已经SQL2000介绍过的特性来避免这种情况。这个特性叫应用程序锁（存储过程 sp_getapplock）
如果数据库获取不到应用程序锁，他就会放弃当前的日志备份。YourSQLDba对给定数据库定义了日志和维护。
但是完整维护任务要等至少5分钟在放弃等待一个锁之前。我们建议5分钟是一个合适等待日志备份，当日志备份在15分钟内做完这样不会有太多时间令备份变大

在日志备份的时候，当日志备份完毕后日志是空的，日志文件增长的那部分会被收缩。这个大小是这样被计算的:
主数据文件+默认文件组的总和大小+默认文件组的额外文件的1/10。如果日志大小比计算公式得出的要大，我们会假定
收缩文件时间已经到来。为什麽不计算1/10文件在默认文件组里？通常，特别的文件组被用来存储BLOB或者历史数据
或者做表分区、索引分区之用。我们有一个大的列记录了内容的改变。日志的比率大小跟这些需要缩小的大文件有关系，跟主数据文件和默认文件组也有关系

YourSQLDba_FullBackups_And_Maintenance任务需要更多的详细解释他所执行的任务

 

执行日志清理
SQLSERVER的errorlog是循环的，并且会有新日志产生
像SQL 代理备份历史的日志会被限制在30日里，超过30日就清理
YourSQLDba的日志历史被限制在30日里
邮件日志被限制在30日里
代理作业历史被限制在30日里

默认YourSQLDba_DoMaint存储过程会被调用，并且根据不同的参数做下面介绍的不同的任务
检查数据库完整性。如果数据库被发现任何问题，YourSQLDba会把数据库设置为紧急状态
更新分发统计。更新时候会用全表扫描，每个星期更新一次统计信息
重组或重建索引根据他们碎片级别和根据存储过程里定义的临界值
执行完整数据库备份
完整数据库备份文件超过一日的存放时间就会从备份文件夹里删除，这个可以通过参数进行配置
日志备份文件超过七日的存放时间就会从备份文件夹里删除，这个可以通过参数进行配置
一个完整数据库备份文件在创建的时候会被命名为dbname_[yyyy-mm-dd_hhhmimsss_dw].bak
dbname是数据库名，[yyyy-mm-dd_hhhmimsss_dw]是一个时间
日志备份文件在创建的时候会被命名为同完整备份文件一样的命名格式，只是文件后缀名是.trn
每个备份循环，不管是完整备份还是日志备份，都会记录在MSDB数据库。MSDB数据库会跟踪所有
已完成的备份并使还原更加容易
备份文件名和位置存放在YourSQLDba的一张表里，这张表允许YourSQLDba重用备份日志文件对于特定数据库
继续追加日志备份。这极大减少了备份过程文件的产生，只要一直使用同样的日志备份文件

 

Default Maintenance
  

Updated : 2009-02-25

YourSQLDba is customizable, but before to do it, may be you should consider what it already does by default.

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.

 

转载于:https://www.cnblogs.com/lyhabc/archive/2013/06/12/3132590.html