SQLServer 2008 - déclencher dump tran sur seuil

[jeeps64]

Bonjour

Je suis entrain d'étudier une solution pour déclencher automatiquement sur seuil d'alerte un dump tran.
Le système mis en place en général s’appuie sur le système d'alert (1 alert par base de données / Compteur ‘Percent Log Used’) et un job / base lançant le dump tran.
Or quand on a des centaines de bases sur un même serveur ça fait des centaines de jobs et d'alertes.

Est-ce que quelqu'un a une autre solution ?
La solution du job cyclique étant déjà en place chez nous mais considéré comme une solution secondaire

Merci de votre aide
Jeeps64

[SQLpro]

Avant tout une sauvegarde n'est pas un dump.... Lisez ceci http://blog.developpez.com/sqlpro/p7...-et-log-ne-so/
Je pense que vous avez deux problèmes :

• un trop grand nombre de base sur un même serveur
• un mauvais paramétrage du mode de journalisation

1) il n'est pas conseillé d'avoir des centaines de bases de données sur un même serveur... Quel est le but de ces centaines de base ???
2) si vous ne gérez pas correctement la sauvegarde du journal de transaction autant passer au mode de journalisation SIMPLE. Vous n'aurez plus de sauvegardes ni d'alertes à créer !

A +

[dbaffaleuf]

C'est l'analogie avec Sybase (dump database / dump tran).

Il faudrait n connexions or 1 job ouvre 1 connexion. Tu peux le faire avec un package SSIS et N 'Execute SQL Tasks' en entrée, en boucle tant qu'il reste des bases à traiter, et programmer dans un job. Il ne faut pas en, lancer trop en même temps. Idéalement, un backup crée deux threads donc si tu as 16 CPU, disons que tu peux lancer 8 backups en //.

Sinon il faut faire un petit programme qui fasse la même chose avec des threads C# par exemple.

HTH A+

[kagemaru]

hello

allez, mes 2 euros : un script powershell qui lance les dump tran (pardon les backup log) en parallèle ...

Quel est le but de ces centaines de base ???

oui c'est vrai ça, pourquoi autant de bases ? Peux-tu donner un peu de contexte ?

merci

[SQLpro]

Envoyé par dbaffaleuf

...un backup crée deux threads donc si tu as 16 CPU, disons que tu peux lancer 8 backups en //.

Et donc plus aucun CPU de libre pour le service des données !!!!!!

A +

[dbaffaleuf]

Envoyé par SQLpro

Et donc plus aucun CPU de libre pour le service des données !!!!!!

A +

A chaque fin de backup log correspond la fin d'un batch donc yield du worker, d'autres demandes peuvent être traitées par SQLOS sans problème. Le but c'est quand même d'utiliser la CPU au maximum, ce n'est pas comme s'il s'agissait de deux process distincts. Disons que 8 est une valeur maximale au delà de laquelle il y aura des changements de contexte.

[mikedavem]

Si toutes les CPU sont occupés par les threads associées aux backups les données pourront être traités de toute manière. Lorsqu'un traitement devra être traité par un processeur il y aura probablement suspension d'une tâche associé aux backups pendant un laps de temps. On verra par ce fait le compteur de type d'attente SOS_SCHEDULER_YIELD augmenté.

A voir maintenant si les tâches de backups sont longues ou pas ...

++

[dbaffaleuf]

Envoyé par mikedavem

A voir maintenant si les tâches de backups sont longues ou pas ...++

Petit test:

SPID 53:

Code :

backup DATABASE ...

SPIDs 58 et 60:

Code :

1
2
3
4
5
while(1=1)
BEGIN
     SELECT ...
     WAITFOR DELAY '00:00:01'
END

SPID 52:

Code :

1
2
3
4
5
6
7
8
9
while (1=1)
begin
	INSERT INTO #trackworker53
	SELECT W.worker_address, W.is_preemptive, W.context_switch_count, W.state, W.quantum_used, W.last_wait_type
	FROM sys.dm_os_workers W
	INNER JOIN sys.dm_os_tasks TA ON TA.task_address = W.task_address
	INNER JOIN sys.dm_exec_requests R ON R.session_id = TA.session_id
	WHERE R.session_id = 53
end

Séquencement des waits pendant le backup full

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
...
...BACKUPBUFFER
...SOS_SCHEDULER_YIELD
...ASYNC_IO_COMPLETION
...BACKUPBUFFER
...SOS_SCHEDULER_YIELD
...ASYNC_IO_COMPLETION
...BACKUPBUFFER
...SOS_SCHEDULER_YIELD
...ASYNC_IO_COMPLETION
...BACKUPBUFFER
...SOS_SCHEDULER_YIELD
...ASYNC_IO_COMPLETION
...BACKUPBUFFER
...SOS_SCHEDULER_YIELD
...ASYNC_IO_COMPLETION
...BACKUPBUFFER
...SOS_SCHEDULER_YIELD
...ASYNC_IO_COMPLETION
...

Le worker qui traite le backup rend la main lorsqu'il est en attente d'une IO asynchrone (écriture ou lecture). Il décompte son temps sur SOS_WORKER_YIELD, signale le worker suivant (SPID 53, 58, ou 60), se place en IO List et décompte du temps sur ASYNC_IO_COMPLETION en attendant d'être signalé à son tour.

Donc pas de problème de monopolisation des ressources par un backup.

[mikedavem]

Je me demande si c'est aussi simple que cela. Si je reprends ton exemple avec une session qui lance un backup et une session qui lance dans une boucle infinie une requête simple. Je précise que je n'ai alloué qu'une seule CPU pour les tests.

Chez moi :

Le SPID 51 correspond à la requête :

Code :

BACKUP DATABASE ...

Le SPID 56 correspond à la requête :

Code :

1
2
3
4
WHILE 1 = 1
BEGIN
 SELECT @@servicename
END

Le SPID 54 correspond à la requête :

Code :

1
2
3
4
5
6
7
8
9
10
while (1=1)
begin
	INSERT INTO #trackworker53
	SELECT R.session_id, S.scheduler_id, W.worker_address, W.is_preemptive, W.context_switch_count, W.state, W.quantum_used, W.last_wait_type
	FROM sys.dm_os_workers W
	INNER JOIN sys.dm_os_tasks TA ON TA.task_address = W.task_address
	INNER JOIN sys.dm_exec_requests R ON R.session_id = TA.session_id
	INNER JOIN sys.dm_os_schedulers S ON S.scheduler_address = W.scheduler_address
	WHERE R.session_id IN (51, 54, 56)
end

J'ai rajouté dans ta requête le no de session ainsi que le scheduler associé. Pour moi le type d'attente SOS_SCHEDULER_YIELD correspond toujours à la session 54 donc celle qui lance la requête qui récupère les résultats. Pour moi ceci est tout à fait normal puisque pour exécuter la requête correspondante à la session 54, le worker associé au backup doit être volontairement suspendu (d'où le SOS_SCHEDULER_YIELD). En fait ici on ne pourra voir que cet état selon moi ...

Il y a en même temps un 2ème worker dans l'état running pour la session 56 mais bon nous sommes en mode préemptif pour ce dernier ...

Qu'en penses tu ?

++

[dbaffaleuf]

Envoyé par mikedavem

Pour moi le type d'attente SOS_SCHEDULER_YIELD correspond toujours à la session 54

Tu es sûr de ne voir aucun SOS_SCHEDULER_YIELD pour ton SPID 51 (le backup) ?

Après la technique de sampling est discutable, en faisant des inserts / select on peut rater des waits pour les autres sessions. Essayes de tracer simplement les waits d'un backup sur un disque local, sans regarder les autres sessions, tu dois voir des yields. A chaque fois qu'une écriture est exécutée, le worker exécute sa routine de yield et passe en IO-list, c'est le comportement normal.

[jeeps64]

Bonjour

Désolé de ne pas avoir donné de nouvelles avant

Nous sommes entrain d'étudier un système avec l'alerting SQLServer sur le remplissage des log déclenchant un job qui lancera un dump tran (oups, un backup log)

NB : quand je parlai des centaine de bases, j'exagérai un peu en fait. Mais sur des applis telle que Sharepoint, il est possible d'avoir plusieurs dizaine de bases sur un même serveur.

Merci de vos différents retours sur ce sujet

Jeeps64