Discussion :

[LudovicB]

Bonjour,

Je fait de l'administration SQL depuis quelques temps.
Et depuis tout ce temps, malgré les formations et les recherches, je ne suis pas satisfait de mes sauvegardes !
J'ai des petites bases. Entre 10 Mo et 5 Go.
J'ai des serveurs en SQL 2008
Certaines sont en mode de récupération simple : pas de problème : Je fais un backup database with init tous les soirs. Et je sais que je ne peux revenir qu'à la date de la sauvegarde. Et je sais que le fichier log est purger à l'occasion de la sauvegarde.
La plupart des bases sont en mode de récupération Complet : là est mon soucis.
Je fais une sauvegarde complète à 22:00 Les données d'abord avec backup database with init. Puis les log dans le même device avec backup log.
Ensuite toute les heures je fais une sauvegarde des log toujours dans le même device avec backp log.
Et à 21:30 chaque jour, je copie mon fichier device sur le réseau pour une sauvegarde "bande".
J'avais l'impression d'être propre avec cela.
Puis en regardant la taille de mes sauvegardes, je me dis que je dois pouvoir améliorer. En effet le premier backup log effectué juste après la sauvegarde data me parait très gros. Pourtant je croyais que le backup log purgeait le fichier des transactions ...
Bref je suis perdu. Il me manque quelquechose.
Est-il conseillé de valider toutes les transaction avec de faire la sauvegarde complète des data ? Comment ?
Pourquoi la première sauvegarde des log est-elle grosse ? En posant la question, je me demande si je n'ai pas trouvé la réponse ... Je fais en début de job de sauvegarde, un check (EXECUTE sp_MSForEachTable
@command1 ='DBCC CheckDB With NO_INFOMSGS') puis un rebuild index (voir script plus loin).
Comment m'organiser pour minimiser l'espace disque occupée par les sauvegardes, tout en étant heureux de pouvoir restaurer à toute heure ?

Merci de votre aide.

Ludovic.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 
 
SET @fillfactor = 90 
 
DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases   
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')   
ORDER BY 1  
 
OPEN DatabaseCursor  
 
FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  
 
   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName   
                    FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''   
 
   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor   
 
   FETCH NEXT FROM TableCursor INTO @Table   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   
 
       -- SQL 2000 command  
       --DBCC DBREINDEX(@Table,' ',@fillfactor)   
 
       -- SQL 2005 command  
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'  
       EXEC (@cmd)  
 
       FETCH NEXT FROM TableCursor INTO @Table   
   END   
 
   CLOSE TableCursor   
   DEALLOCATE TableCursor  
 
   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor

[mikedavem]

Bonjour,

Les opérations de reconstruction sont loggées dans le JT. Ceci explique qu'avant la sauvegarde votre ou vos journaux de transactions peuvent être volumineux. Pour limiter la journalisation dû aux opérations de maintenance sur vos index vous pourriez être plus précis dans votre script en ne reconstruisant que les index réellement fragmentés (fragmentation > 30%). Le reste peut être réorganisé (fragmentation > 10%). Il doit y avoir un exemple de script applicable sur le blog de SQLPro qui prend également en compte le nombre de pages dans un index il me semble. Je vous laisse voir

Pourtant je croyais que le backup log purgeait le fichier des transactions

Oui c'est exact, cela ne veut pas dire qu'il réduit la taille de votre journal. Une purge reste une opération logique alors qu'une réduction de fichier est une opération physique et s'effectue par une commande DBCC SHRINKDATABASE / SHRINKFILE

++

[SQLpro]

...
Certaines sont en mode de récupération simple : pas de problème : Je fais un backup database with init tous les soirs. Et je sais que je ne peux revenir qu'à la date de la sauvegarde. Et je sais que le fichier log est purger à l'occasion de la sauvegarde.

Non pas du tout. Dans le mode simple le journal est recyclé en permanence environ toutes les minutes. La sauvegarde n'a pas d'incidence là dessus.

La plupart des bases sont en mode de récupération Complet : là est mon soucis.
Je fais une sauvegarde complète à 22:00 Les données d'abord avec backup database with init. Puis les log dans le même device avec backup log.
Ensuite toute les heures je fais une sauvegarde des log toujours dans le même device avec backp log.
Et à 21:30 chaque jour, je copie mon fichier device sur le réseau pour une sauvegarde "bande".
J'avais l'impression d'être propre avec cela.

C'est le cas.

Puis en regardant la taille de mes sauvegardes, je me dis que je dois pouvoir améliorer. En effet le premier backup log effectué juste après la sauvegarde data me parait très gros. Pourtant je croyais que le backup log purgeait le fichier des transactions ...

BACKUP LOG fait une sauvegarde par copie des portions concerné du journal depuis le dernière sauvegarde précédente quel qu'en soit sa nature (FULL, DIFFERENTAIL ou LOG). Le fichier ainsi généré reflète le contenu des transactions.

...
Est-il conseillé de valider toutes les transaction avec de faire la sauvegarde complète des data ? Comment ?

Vous ne pouvez pas "forcer" la validation des transactions pour les sessions ou les transactions en cours. Cela n'a d'ailleurs pas de sens !

Pourquoi la première sauvegarde des log est-elle grosse ? En posant la question, je me demande si je n'ai pas trouvé la réponse ... Je fais en début de job de sauvegarde, un check (EXECUTE sp_MSForEachTable
@command1 ='DBCC CheckDB With NO_INFOMSGS') puis un rebuild index (voir script plus loin).

DBCC CHECK... n'a pas d'incidence sur le journal. En revanche la reconstruction des index l'impacte fortement.
Dans ce cas il y a différentes solutions :
1 - ne pas tout ré indexer sauvagement. C'est stupide de faire des choses inutiles comme de ré indexer des index parfaitement propre (sans fragmentation).
Il ne faut ré indexer que les index fragmentés.
Lisez les articles que j'ai écrit à ce sujet, avec les procédures qui vont avec :
1) http://sqlpro.developpez.com/optimis...ntenanceIndex/
2) http://blog.developpez.com/sqlpro/p8...es-index-et-s/

2 - Passer temporairement en mode de journalisation BULK LOGGED. Ce mode propose justement de ne pas journaliser les opérations reproductibles, comme justement les CREATE INDEX....

Comment m'organiser pour minimiser l'espace disque occupée par les sauvegardes, tout en étant heureux de pouvoir restaurer à toute heure ?

A partir de 2088 Enterprise, vous pouvez aussi faire de la sauvegarde compressée !

A +

PS : DBA c'est un vrai métier, et cela demande une solide formation. Je vous invite à vous offrir un telle formation, comme celles que je donne à Orsys :
http://www.orsys.fr/formation-sql-se...nistration.asp
http://www.orsys.fr/formation-sql-se...on-avancee.asp

[LudovicB]

Merci pour vos réponses.
Mais je n'ai pas encore tout pigé et reviens vers vous avec d'autres questions.

Deux sujets : Le journal des transactions et les index

BACKUP LOG fait une sauvegarde par copie des portions concerné du journal depuis le dernière sauvegarde précédente quel qu'en soit sa nature (FULL, DIFFERENTAIL ou LOG). Le fichier ainsi généré reflète le contenu des transactions.

Dans mon cas je fais une recontruction des index, suivie d'un backup database, suivie d'un backup log.
La reconstruction des index génère des transaction (beaucoup : normal).
Mais, quand je fais mon backup database, les transactions ont été validées et la base sauvegardées a des index reconstruits.
Donc la sauvegarde des log dans la foulée ne devrait pas être importante en volume, et donc en valeur (pas de transactions dans l'intervalle).
Pourquoi donc ma première sauvegarde des log est-elle si volumineuse ?

Citation:
...
Est-il conseillé de valider toutes les transactions avec de faire la sauvegarde complète des data ? Comment ?

Vous ne pouvez pas "forcer" la validation des transactions pour les sessions ou les transactions en cours. Cela n'a d'ailleurs pas de sens !

Je ne voulais pas écrire "avec" mais "avant" !!!
Quelle action enlève les anciennes transactions du journal ?

1 - ne pas tout ré indexer sauvagement. C'est stupide de faire des choses inutiles comme de ré indexer des index parfaitement propre (sans fragmentation).
Il ne faut ré indexer que les index fragmentés.

Je suis tout à fait d'accord, et à tel point que vous n'imagineriez pas !
Mais vos deux liens ne me satisfont pas. Le premier est trop ardu et le deuxième trop intrusif. Je préfèrerais quelque chose de plus light.

A partir de l'idée de ne reconstruire que ce qui est défragmenté à plus de 30%, j'ai fait quelques tests. Et j'ai de nouveau plus de questions que de réponses !

J'ai regardé le taux de fragmentation des index d'une de mes bases :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT *
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
where database_id = 6
order by avg_fragmentation_in_percent desc

Le résultat n'est pas terrible !
J'ai ensuite tout reconstruit (je sais il ne faut pas, mais c'était pour voir)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
EXECUTE sp_MSForEachTable
   @Command1 = 'ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)'

Cela prend un peu de temps.
Puis relancé ma requête pour le taux de fragmentation.
Et là, horreur, c'est du pareil au même !
Qu'est-ce qui cloche ?

Merci de votre aide.

Ludovic.

[dbaffaleuf]

Si la valeur d'avg_fragmentation_in_percent ne change pas après un rebuild, ça peut être parce que les tables sont petites ("J'ai des petites bases. Entre 10 Mo et 5 Go.") et sont stockées sur moins de 8 pages cf http://blog.capdata.fr/index.php/fra...ckees-en-s-gam