YourSQLDba的翻译(八)

YourSQLDba的翻译(八)

 YourSQLDba 记录一些信息关于他自己执行的任务加上每个维护任务的TSQL的信息。
如果有任何明确的错误发生，错误消息会被记录。日志能在YourSQLDba数据库提供的一个特别报告存储过程的帮助下轻松阅读
存储过程名称叫 [ShowHistory]
对于每个正在运行的日志备份,都会有email报告发到你的邮箱.这让管理员知道作业运行情况.如果运行当中有错误发生,
额外的email会发到你邮箱,并且邮件里有表明了非常重要的标志来提醒您.第一条消息会让你值得维护计划在正常运行.
之后你不需要去打开邮箱了,因为在调用[ShowHistory]存储过程就能看到所有的任务是否执行成功.
在出错的情况下,[ShowHistory]会过滤其他没有错误的消息,只显示有错误的消息

 

日志记录的错误信息比较少当备份数据库的时候.SQL2005里TSQL try-catch块会捕获这个.他捕获错误不过
只包含非常少的明确信息在里面,例如"数据库备份被意外中断".数据库或日志备份错误跟不存在介质或者非法路径
或者其他IO错误有关,导致捕获不到详细的错误信息.更糟糕的是，当两个错误同时发生，SQLSERVER只会抛出第一个错误
第二个错误就会捕获不了，让当前执行的批处理中断，进而终止备份进程

为了避免这个情况，YourSQLDba有一个特别的处理方法，通过使用xp_cmdshell运行SQLCMD，xp_cmdshell
有一些限制会让这个事情更复杂。
例如在执行一个字符串的时候，我们会这样执行
"C:\Program Files\Microsoft SQL Server\90\Tools\binn\SqlCmd.exe" -E -S.\ISQL2005 -Q"exec YourSQLDba.yExecNLog.ExecCmdFromLog @JobNo=30, @Seq=4663"

但是这样是执行不了的

需要这样写才对
Cd C:\Program Files\Microsoft SQL Server\90\Tools\binn\
exec YourSQLDba.yExecNLog.ExecCmdFromLog @JobNo=30, @Seq=4663

这个批处理文件使用xp_cmdshell+echo 命令+重定向批处理文件所创建。
只要他利用了xp_cmdshell调用批处理文件，我们就能得到所有错误信息文本

 

 

Logging
  

Updated : 2009-02-25

YourSQLDba logs some informative messages about its actions plus every T-SQL instructions it generates for its maintenance.  If any error occurs with a specific instruction, error message is recorded with it.  The log can be read with the help of a special reporting stored procedure supplied in YourSQLDba database : ShowHistory.  See section history reporting for more detail about the ease of use of this procedure.

For each run of YourSQLDba which is not just a log backup, an e-mail is sent to report it is running.  It lets the administrator knows that the job is done.  If any error occurs with during the process, another exceptional e-mail message with high importance flagged is sent.  The first message makes you react to the absence of regular maintenance, by the fact that being used to received, you suddenly don't.  You don't really need to open it, but it contains a call to ShowHistory with all parameters needed (ex: jobNo) to see everything this  job did.  In case of error an exceptional message is sent that also contains a call to ShowHistory with all the parameters set to filter only what went wrong into the job.  Both message shows what is the calling procedure with its parameters if it was called from SQL Server Agent.

Logging of informative error messages can become tricky when it comes to database backus.  SQL2005 T-SQL Begin Try - Catch block fail to the job for that purpose. It catches error but with an meaningless error message like "database backup is terminating abnormally".  Database or log backup error related to unavailable media or invalid path or any other IO error can't be caught with enough error detail to understand what happen.  Worse SQL Server seems to raise two errors so the second one get uncaught, making the current executing batch terminate, which stop the rest of the backup process.  Very bad.

To circumvent this YourSQLDba have a special procedure that runs a command through calling SQLCmd with the use of xp_cmbshell.  Xp_cmdshell itself have some restrictions that make things more complicated to use.  It is not able to run string that have more that one pair of double quotes.  For example it is impossible to execute this string:

"C:\Program Files\Microsoft SQL Server\90\Tools\binn\SqlCmd.exe" -E -S.\ISQL2005 -Q"exec YourSQLDba.yExecNLog.ExecCmdFromLog @JobNo=30, @Seq=4663"

So a batch file has to be created that contains this instruction sequence:

Cd C:\Program Files\Microsoft SQL Server\90\Tools\binn\
exec YourSQLDba.yExecNLog.ExecCmdFromLog @JobNo=30, @Seq=4663

The batch file is created using xp_cmdshell + echo command + redirection to the batch file.  Once it is created the xp_cmdshell call the batch file created.  Not very elegant but at least we get the full error message text.