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

因一个数据库日志文件无限扩展，最终填满磁盘空间而导致数据库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”的错误。