ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Ubuntu系统定时作业无效果的排错

Ubuntu系统定时作业无效果的排错

原创 Linux操作系统 作者:cow977 时间:2019-12-15 19:04:42 0 删除 编辑

因一个数据库日志文件无限扩展,最终填满磁盘空间而导致数据库Hang住,为监控日志文件增长情况,创建一个定时作业以执行如下SQL命令来查询数据文件大小:

select f.name, file_id fileid, physical_name filename, g.name filegroup

     , iif(size < 128, cast(size * 8 as varchar) + ' KB'

         , iif(size / 128 < 1024, cast(size / 128 as varchar) + ' MB'

             , cast(size / 128 / 1024 as varchar) + ' GB')) size

     , iif(max_size=-1, 'Unlimited'

         , iif(max_size<128, convert(varchar(20), cast(max_size as bigint) * 8) + ' KB'

             , iif(max_size / 128 < 1024, cast(max_size / 128 as varchar) + ' MB'

                 , cast(max_size / 128 / 1024 as varchar) + ' GB'))) maxsize

     , iif(is_percent_growth=1, cast(growth as varchar(3)) + '%'

         , iif(growth < 128, convert(varchar(20), growth * 8) + ' KB'

             , convert(varchar(20), growth / 128) + ' MB')) growth

     , f.type_desc usage

  from sys.database_files f left join sys.filegroups g on f.data_space_id=g.data_space_id

创建作业:

csyy@server02:~$ crontab -l

25 * * * * sqlcmd -S . -U sa -P 'Password' -i MonitEmrFileSize.sql -o checkfilesize/`date +%Y%m%d.%H%M.txt`

 

csyy@server02:~$ cat MonitEmrFileSize.sql

use emr22

go

select cast(f.name as varchar(20)) name, cast(file_id as varchar(6)) fileid

     , cast(physical_name as varchar(40)) filename, cast(g.name as varchar(10)) filegroup

     , cast(iif(size < 128, cast(size * 8 as varchar) + ' KB'

         , iif(size / 128 < 1024, cast(size / 128 as varchar) + ' MB'

             , cast(size / 128 / 1024 as varchar) + ' GB')) as varchar(10)) size

     , cast(iif(max_size=-1, 'Unlimited'

         , iif(max_size<128, convert(varchar(20), cast(max_size as bigint) * 8) + ' KB'

             , iif(max_size / 128 < 1024, cast(max_size / 128 as varchar) + ' MB'

                 , cast(max_size / 128 / 1024 as varchar) + ' GB'))) as varchar(10)) maxsize

     , cast(iif(is_percent_growth=1, cast(growth as varchar(3)) + '%'

         , iif(growth < 128, convert(varchar(20), growth * 8) + ' KB'

             , convert(varchar(20), growth / 128) + ' MB')) as varchar(10)) growth

     , cast(f.type_desc as varchar(10)) usage

  from sys.database_files f left join sys.filegroups g on f.data_space_id=g.data_space_id

go

exit

过了定时作业执行时间,未生成结果文件。

1 、一开始怀疑是普通用户没有执行定时作业的权限,

将作业任务改为root用户来执行,但还是没有效果;

2 、检查cron服务状态,提示“未安装MTA,放弃输出”,;

root@server02:/home/csyy# service cron status

● cron.service - Regular background program processing daemon

   Loaded: loaded (/lib/systemd/system/cron.service; enabled; vendor preset: enabled)

   Active: active (running) since Tue 2019-11-26 22:38:26 CST; 2 days ago

     Docs: man:cron(8)

 Main PID: 3690 (cron)

    Tasks: 1

   Memory: 119.8M

      CPU: 12.711s

   CGroup: /system.slice/cron.service

            └─3690 /usr/sbin/cron -f

 

Nov 29 15:55:01 server02 CRON[171781]: (root) CMD (MonitEmrFileSize.sh > checksize/`date +)

Nov 29 15:55:01 server02 CRON[171769]: pam_unix(cron:session): session closed for user root

Nov 29 15:55:01 server02 CRON[171770]: (CRON) info (No MTA installed, discarding output)

Nov 29 15:55:01 server02 CRON[171770]: pam_unix(cron:session): session closed for user root

Nov 29 15:55:01 server02 CRON[171773]: (CRON) info (No MTA installed, discarding output)

Nov 29 15:55:01 server02 CRON[171773]: pam_unix(cron:session): session closed for user csyy

Nov 29 15:55:01 server02 CRON[171772]: (CRON) info (No MTA installed, discarding output)

Nov 29 15:55:01 server02 CRON[171772]: pam_unix(cron:session): session closed for user csyy

Nov 29 15:55:01 server02 CRON[171771]: (CRON) info (No MTA installed, discarding output)

Nov 29 15:55:01 server02 CRON[171771]: pam_unix(cron:session): session closed for user root

原因:系统未安装MTA,所以在Job作业的命令中不能有标准输出和标准错误输出,否则作业会将输出重定向到邮件,导致触发“未安装MTA”的错误。

3 、将CRON中的命令最后加上“ >/dev/null 2>&1”后,任务还是未有结果;

csyy@server02:~$ crontab -l

25 * * * * sqlcmd -S . -U sa -P 'Password' -i MonitEmrFileSize.sql -o checkfilesize/`date +%Y%m%d.%H%M.txt`>/dev/null 2>&1

4 、将CRON中的命令作为一个shell命令,在CRON中执行此shell命令,并在最后加上“ >/dev/null 2>&1”后,定时任务终于有了结果;

csyy@server02:~$ crontab -l

25 * * * * ShellCmd.sh >/dev/null 2>&1

 

csyy@server02:~$ cat ShellCmd.sh

sqlcmd -S . -U sa -P 'Password' -i MonitEmrFileSize.sql -o checkfilesize/`date +%Y%m%d.%H%M.txt`

解释:第3步后,虽然从Shell命令的角度看,标准输出和标准错误输出已经全部进行了重定向,命令已经没有了输出,但在命令中还嵌套了一个Shell命令date,标准输出和标准错误输出未进行重定向,导致触发“未安装MTA”的错误。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/81227/viewspace-2668693/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-02

  • 博文量
    699
  • 访问量
    759112