I jobs di Sql Server permettono di pianificare (o “schedulare”) determinate attività nel corso del tempo da parte del motore di Sql Server. Normalmente possiamo interrogare il sistema tramite una comoda interfaccia accessibile da Management Studio (Sql Server Agent / Job Activity Monitor). In questo articolo vedremo quali sono le tabelle di sistema coinvolte; preciso subito che l’utilità pratica di questo articolo e dello script presentato è abbastanza bassa, tuttavia esso costituisce la base per i prossimi due articoli, decisamente più interessanti ed utili.
Le tabelle (ribadisco: tabelle, non viste, in questo caso) interessate, tutte contenute nel database di sistema msdb, sono tre:
| Tabella | Descrizione |
|---|---|
dbo.SysJobs |
Riporta i dati principali del job, tra cui il suo id, il nome, l’eventuale descrizione, il flag abilitato. |
dbo.SysJobSteps |
Riporta i dati dei singoli step che costituiscono il job, tra cui l’id del job, quello dello step, il nome dello step, il risultato dell’ultima esecuzione (il valore 1 indica che l’esecuzione è andata bene), la data dell’ultima esecuzione e l’ora dell’ultima esecuzione; si noti che data ed ora sono espressi con dati di tipo int. |
dbo.SysJobSchedules |
Riporta i dati della prossima esecuzione del job, tra cui l’id del job, la data della prossima esecuzione prevista e l’ora della prossima esecuzione prevista; anche in questo caso, data ed ora sono espressi con dati di tipo int. |
Dal momento che la chiave delle tre tabelle è l’Id del job, come prima cosa lo script identifica quest’ultimo, quindi interroga le tabelle. La prima SELECT restituisce il nome del job, il risultato dell’ultima esecuzione, la data e l’ora della prossima esecuzione.La seconda SELECT restituisce il nome dello step, la data, l’ora, il risultato e la durata dell’ultima esecuzione.
DECLARE @name sysname; SET @name = 'Caricamento Clienti e Fornitori'; DECLARE @jobId uniqueidentifier; SELECT @jobId = job_id FROM msdb.dbo.SysJobs WHERE [name] LIKE '%' + @name + '%' SELECT [name], [enabled], [Next_run_date], [Next_run_time] FROM msdb.dbo.SysJobs LEFT JOIN msdb.dbo.SysJobSchedules ON SysJobSchedules.job_id = SysJobs.job_id WHERE SysJobs.job_id = @jobId SELECT [step_id], [step_name], [last_run_date], [last_run_time], [last_run_outcome], [last_run_duration] FROM msdb.dbo.SysJobSteps WHERE job_id = @jobId
Si noti che la JOIN della prima SELECT è stata definita LEFT: questo per ottenere i dati del job anche se non fosse stata pianificata un’esecuzione. Il risultato ottenuto ha questo aspetto:
name enabled Next_run_date Next_run_time -------------------------------------- ------- ------------- ------------- Caricamento Clienti e Fornitori 1 20130503 104400 (1 row(s) affected) step_id step_name last_run_date last_run_time last_run_outcome last_run_duration ----------- ------------------------------------------ ------------- ------------- ---------------- ----------------- 1 Caricamento Fornitori 20130503 104400 1 7 2 Caricamento Clienti 20130503 104407 1 45 3 Esportazione Clienti in file CSV 20130503 104452 1 10 (3 row(s) affected)
Volendo farsi un po’ di sano masochismo, potremmo voler rendere più leggibili quei campi next_run_date, next_run_time, last_run_date, last_run_time; la modifica necessaria è concettualmente molto semplice: basta convertire i campi *_run_date in tipo datetime ed aggiungergli le ore, i minuti ed i secondi riportati nei campi *_run_time. Il risultato è qui sotto: lo script risultante, se non altro, oltre all’uso per il quale era nato, ne presenta anche un’altro: contarne le parentesi può rivelarsi un efficace rimedio contro l’insonnia (a sua volta generata dallo sforzo di metterle…).
DECLARE @name sysname; SET @name = 'Caricamento Clienti e Fornitori'; DECLARE @jobId uniqueidentifier; SELECT @jobId = job_id FROM msdb.dbo.SysJobs WHERE [name] LIKE '%' + @name + '%' SELECT [name] , [enabled] , [Next_Run] = DateAdd( hour , Convert( int , Left( Convert( char(6), [Next_run_time] ), 2) ) , DateAdd( minute , Convert( int , Substring( Convert( char(6), [Next_run_time] ), 3 , 2 ) ), DateAdd( second, Convert( int, Right( Convert( char(6), [Next_run_time] ), 2) ), Convert( datetime, Convert( char(8), [Next_run_Date] ) ) ) ) ) FROM msdb.dbo.SysJobs LEFT JOIN msdb.dbo.SysJobSchedules ON SysJobSchedules.job_id = SysJobs.job_id WHERE SysJobs.job_id = @jobId SELECT [step_id] , [step_name] , [last_run] = DateAdd( hour , Convert( int , Left( Convert( char(6), [last_run_time] ), 2) ) , DateAdd( minute , Convert( int , Substring( Convert( char(6), [last_run_time] ), 3 , 2 ) ), DateAdd( second, Convert( int, Right( Convert( char(6), [last_run_time] ), 2) ), Convert( datetime, Convert( char(8), [last_run_Date] ) ) ) ) ) , [last_run_outcome] , [last_run_duration] FROM msdb.dbo.SysJobSteps WHERE job_id = @jobId
Forse però il risultato ottenuto è valso lo sforzo:
name enabled Next_Run -------------------------------------- ------- ----------------------- Caricamento Clienti e Fornitori 1 2013-05-03 10:44:00.000 (1 row(s) affected) step_id step_name last_run last_run_outcome last_run_duration ----------- ------------------------------------------ ----------------------- ---------------- ----------------- 1 Caricamento Fornitori 2013-05-03 10:44:00.000 1 7 2 Caricamento Clienti 2013-05-03 10:44:07.000 1 45 3 Esportazione Clienti in file CSV 2013-05-03 10:44:52.000 1 10 (3 row(s) affected)
Ok, anche se esula dal discorso “Jobs di Sql Server”, esiste un modo più pratico per convertire quei malefici valori in un campo datetime: prima di tutto, bisogna tenere in considerazione il fatto che il tipo datetime è un numero decimale, la cui parte intera rappresenta la data (anno, mese, giorno), quella decimale rappresenta l’orario (ora, minuti, secondi, millesimi). Ciò comporta che è possibile sommare la componente decimale calcolata in maniera opportuna alla componente intera anch’essa calcolata in maniera opportuna, ottenendo così il valore giusto: l’enfasi posta su quella “maniera opportuna” è dovuta al fatto che non possiamo semplicemente sommare i valori dei due campi così come sono, ma dobbiamo “trasformali” opportunamente.
La conversione della data è relativamente semplice, mentre per la componente relativa all’orario occorre applicare una formula facilmente reperibile sul web. Il risultato è quello sottostante: ho spezzato su due righe il calcolo del campo data di ogni SELECT al solo scopo di rendere il tutto più leggibile.
DECLARE @name sysname; SET @name = 'Caricamento Clienti e Fornitori'; DECLARE @jobId uniqueidentifier; SELECT @jobId = job_id FROM msdb.dbo.SysJobs WHERE [name] LIKE '%' + @name + '%' SELECT [name] , [enabled] , [Next_Run] = Convert( datetime, RTrim( [next_run_date] ) ) + ( ( [next_run_time] * 9 + [next_run_time] % 10000 * 6 + [next_run_time] % 100 * 10 ) / 216e4 ) FROM msdb.dbo.SysJobs LEFT JOIN msdb.dbo.SysJobSchedules ON SysJobSchedules.job_id = SysJobs.job_id WHERE SysJobs.job_id = @jobId SELECT [step_id] , [step_name] , [last_run] = Convert( datetime, RTrim( [last_run_date] ) ) + ( ( [last_run_time] * 9 + [last_run_time] % 10000 * 6 + [last_run_time] % 100 * 10 ) / 216e4 ) , [last_run_outcome] , [last_run_duration] FROM msdb.dbo.SysJobSteps WHERE job_id = @jobId
Il risultato ottenuto è esattamente lo stesso della query appena precedente, però il suo codice è decisamente più leggibile, no?
name enabled Next_Run -------------------------------------- ------- ----------------------- Caricamento Clienti e Fornitori 1 2013-05-03 10:44:00.000 (1 row(s) affected) step_id step_name last_run last_run_outcome last_run_duration ----------- ------------------------------------------ ----------------------- ---------------- ----------------- 1 Caricamento Fornitori 2013-05-03 10:44:00.000 1 7 2 Caricamento Clienti 2013-05-03 10:44:07.000 1 45 3 Esportazione Clienti in file CSV 2013-05-06 10:44:52.000 1 10 (3 row(s) affected)