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”的錯誤。