[SQL Agent] Requête effectuée tous les jours à minuit

Version imprimable

Bonjour, j'aimerais savoir ce qui ne va pas dans mon code :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
BEGIN TRY 
    BEGIN TRAN 
    DECLARE @jobId BINARY(16) 
    --Add job 
    EXEC msdb.dbo.sp_add_job @job_name=N'PurgeTS7Ans', @job_id = @jobId OUTPUT 
    --Add step to job 
    EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Requête à effectuer',  
    @step_id=1,  
    @subsystem=N'TSQL',  
    @command=N'DELETE FROM Action_OPE WHERE     (DATEDIFF(dd, TimeStmp, GETDATE()) > 2558);',   
    @database_name=N'Bat_B1',  
    @flags=0 
    --Add schedule to job 
    EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'A_minuit',  
    @freq_type=1,  
    @freq_interval=4,  
    @freq_subday_type=1,    
    @freq_subday_interval=1,  
    @freq_relative_interval=0,  
    @freq_recurrence_factor=1,  
    @active_start_date=NULL,  
    @active_end_date=99991231,  
    @active_start_time=000000,  
    @active_end_time=235959 
    COMMIT TRAN 
END TRY 
BEGIN CATCH 
    SELECT ERROR_Message(), ERROR_Line(); 
    ROLLBACK TRAN 
END CATCH

Ce job doit me permettre d'effectuer une suppression des enregistrements vieux de plus de 7 ans dans ma table "Action_Ope" tous les jours à minuit mais j'ai vérifié et celui ci ne fonctionne pas. Pouvez vous m'aider à identifier la portion de l'algorithme fausse ?

Merci d'avance et bonne journée.

PS : voici l'aide sur le MSDN http://msdn.microsoft.com/en-us/library/ms187320.aspx

20/07/2011, 11h48
hmira

Ta requête, sur le plan "algorithmique" me parait correcte.

Il faut vérifier que service Agent SQL Server est bien démarré. C’est l'Agent SQL Server qui se charge du traitement des travaux planifiés.

Ci-dessous quelques autres pistes.

- Vérifies le contenu des Journaux d’erreurs de Agent SQL Server

- Vérifies également l’historique des travaux planifiés (sysjobhistory)

Code:

select * from msdb.dbo.sysjobhistory

A+
20/07/2011, 13h47
elsuket
Bonjour,

Effectivement regarder l'historique du job, qui vous indiquera quelle est l'erreur qui se produit.

Cela dit je ne vois pas pourquoi vous faites appel aux procédures de création des jobs et de leurs étapes, alors qu'il est plus simple pour cette tâche d'utiliser l'interface graphique.
Est-ce que vous déployez le job sur plusieurs instances de SQL Server ?

D'autre part votre requête contient un prédicat qui n'est pas SARGable :

Code:

DELETE FROM Action_OPE WHERE (DATEDIFF(dd, TimeStmp, GETDATE()) > 2558);

Parce que SQL Server maintient des statistiques sur la distribution des valeurs de TimeStmp, mais pas sur DATEDIFF(dd, TimeStmp, GETDATE()) > 2558).
Vous pouvez également utiliser des années dans DATEDIFF :

Code:

DATEDIFF(year, TimeStmp, GETDATE()) > 7).

Mais le mieux est d'écrire :
Code:

1 2 DELETE FROM dbo.Action_OPE WHERE TimeStmp < DATEADD(year, -7, GETDATE())
Si la colonne TimeStmp est indexée, cela ira très vite.
Avec votre requête, SQL Server est obligé de lire toute la table, ce qui peut prendre beaucoup de temps, surtout dans votre cas puisqu'il s'agit d'une table qui doit contenir de nombreuses lignes.

@++ ;)
20/07/2011, 14h05
pitipilot
Citation:
Ta requête, sur le plan "algorithmique" me parait correcte.

Il faut vérifier que service Agent SQL Server est bien démarré. C’est l'Agent SQL Server qui se charge du traitement des travaux planifiés.

Ci-dessous quelques autres pistes.

- Vérifies le contenu des Journaux d’erreurs de Agent SQL Server

- Vérifies également l’historique des travaux planifiés (sysjobhistory)
Code :
Sélectionner tout - Visualiser dans une fenêtre à part

Code:

1 2 SELECT * FROM msdb.dbo.sysjobhistory

A+
En fait j'utilise SQL server express 2005 et SQL Agent n'est pas accessible par l'arborescence de SQL server express Management.

J'ai effectué votre requête de lecture et il n'y a rien dans la table "sysjobhistory"

elsuket -> Comment savoir si mon champ est indexé ?
J'utilise un job car cette solution m'a paru pratique cependant, si vous avez une autre solution je suis tout à fait preneur:D
20/07/2011, 15h30
hmira

Pour vérifier et le cas échéant démarrer l'Agent 'Agent SQL, il faut procéder comme suit :

Dans le menu Démarrer, pointez sur Tous les programmes, sur Microsoft SQL Server 2005, sur Outils de configuration, puis cliquez sur Gestionnaire de configuration SQL Server.

Dans le Gestionnaire de configuration SQL Server, développez Services SQL Server 2005, puis cliquez sur Agent SQL.

Dans le volet de résultats, cliquez avec le bouton droit sur l'instance considérée, puis cliquez sur Démarrer.

Une flèche verte sur l'icône à côté de l'Agent SQL Server, indique que l'Agent SQL Server est bien démarrée.

A+,
20/07/2011, 15h55
pitipilot

Citation:

Envoyé par hmira

Pour vérifier et le cas échéant démarrer l'Agent 'Agent SQL, il faut procéder comme suit :

Dans le menu Démarrer, pointez sur Tous les programmes, sur Microsoft SQL Server 2005, sur Outils de configuration, puis cliquez sur Gestionnaire de configuration SQL Server.

Dans le Gestionnaire de configuration SQL Server, développez Services SQL Server 2005, puis cliquez sur Agent SQL.

Dans le volet de résultats, cliquez avec le bouton droit sur l'instance considérée, puis cliquez sur Démarrer.

Une flèche verte sur l'icône à côté de l'Agent SQL Server, indique que l'Agent SQL Server est bien démarrée.

A+,

Merci beaucoup pour votre réponse détaillée. Cependant, SQL server Express ne permet pas d'accéder à SQL Agent de cette façon c'est pour ça que je pense que la raison pour laquelle mon job ne démarre pas est tout simplement due au fait que SQL sever Express ne gère pas du tout SQL agent même lorsqu'on fait appel à lui avec un algorithme comme le mien :cry:
20/07/2011, 17h22
elsuket

Effectivement SQL Server Express ne contient pas l'Agent SQL Server.
Ceci rappelle encore une fois l'utilité du respect de la charte de postage : si vous aviez préfixé votre sujet par [SQL 2005 Express] ou précisé cela dans votre premier post, personne n'aurait perdu de temps ;)

Mea culpa,
Comme, je utilise très peu (pour ne pas dire jamais) SQL Server Express Edition, j'avais complétement oublié le fait que l'Agent SQL Server n'existe pas sous SQL Server Express Edition. Merci elsuket pour le rappel ;)

Une autre solution très simple à mettre en œuvre, est d'utiliser le Planificateur de tâche de Windows combiné à l'utilitaire, en ligne de commande, de SQL Server : sqlcmd et créer une tâche planifiée Windows.

Pour cela, il suffit , dans la zone programme/script de la tâche planifiée Windows, de taper une commande, en une seule ligne, comme illustrée dans l'exemple ci-dessous :
Code:

1 2 3 4 5 sqlcmd -S nom_de_votre_instance -d nom_de_votre_base_de_donnees -U votre_login -P votre_mot_de_passe -Q "DELETE FROM dbo.Action_OPE WHERE TimeStmp < DATEADD(year, -7, GETDATE());"
puis planifier (périodicité, jour, heure, …), de façon classique, la tâche Windows, et le tour est joué !

PS : Il faut veiller à respecter comme indiqué, la casse des options (-S -d -U -P -Q) c'est important. Certaines options existent en minuscule et en majuscule, mais n'ont pas la même signification !

A+

22/07/2011, 11h22
pitipilot

hmira : Étant donné que je vais migrer ma base prochainement vers SQL Server 2005 (pas express =P) je pense garder la solution du job mais l'autre solution me sera surement fortement utile :D.

Par contre le paramétrage de mon scheduler est il bon ?

Merci beaucoup bonne journée
22/07/2011, 14h04
hmira

Citation:

Envoyé par pitipilot

..
Par contre le paramétrage de mon scheduler est il bon ?

A partir du moment où tu utiliseras, comme l'a déjà suggéré elsuket dans un message ci-dessus, l'interface graphique pour créer la tâche planifiée, tu n'auras point besoin de script (ou code) pour créer la partie planification. Au travers l'interface graphique, tu pourras ainsi aisément tout paramétrer (étape, planification, alerte, notifications, etc.) de la tâche planifiée.

A+