Incapable de rapetisser le log file

**Baquardie** · 04/05/2010, 16h53

Bonjour !

Nous avons un serveur sql 2008 installé en cluster mode.

Deux des bases de données contiennent uniquement des données répliquées provenant d'un système AS/400. La réplication s'effectue avec le logiciel DataMirror d'IBM.

Ce serveur n'est pas encore en production alors outre la réplication je n'ai pas d'usagé qui lisent les données.

Mon problème est du côté de la plus petite base de donnée. Je ne suis pas capable de rapetisser le log file. Il fait présentement 30 gig alors que la base de données prend 7 gig.

Le recovery mode est à FULL.

Dans le Maintenance Plans, nous faisons un backup de la base de donnée une fois par semaine et un backup du log file une fois par heure...

Sinon, j'essaie dans mon query analyser de faire des backup du log et tout de suite après je tente de faire un shrink :

Code :

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

DBCC shrinkfile (ronald, truncateonly)

Mais ça ne fonctionne toujours pas, le log reste immense.

Des idées?

Merci !

**zinzineti** · 04/05/2010, 17h19

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
--1.Visualiser la taille du fichier log (Log Size) et espace utilisé (Log Spaced %) de la base 
USE yourdatabase
GO
DBCC SQLPERF (LOGSPACE)
 
--2. Faire un BACKUP LOG (backup du journal de transaction)
 
BACKUP LOG yourdatabase to disk ='\BACKUP\yourdatabase.trn'
 
--3. Obtenir le nom logique du fichier du fichier LOG 
USE yourdatabase
GO
exec sp_helpfile 
 
--4. Diminuer la taille du fichier LOG (yourdatabase_log) 
de 250 Mo par exemple
 
DBCC SHRINKFILE(yourbase_log,250)
 
--5. Visualiser à nouveau l'espace libre dans le fichier LOG
USE yourdatabase
GO
DBCC SQLPERF (LOGSPACE)

A+
Etienne ZINZINDOHOUE

**zinzineti** · 04/05/2010, 17h30

Envoyé par zinzineti

--1.Visualiser la taille du fichier log (Log Size) et espace utilisé (Log Spaced %) de la base
USE yourdatabase
GO
DBCC SQLPERF (LOGSPACE)

Permet d'avoir une idée de l'espace utilisée dans le fichier LOG
et donc de savoir de combien peut-on reduire le fichier de LOG !
Donc il y a un petit calcul à faire avant de faire le DBCC SHRINKFILE

Exemple : Si on a 30 % d'espace utilisé pour fichier de 1000 Mo
alors il y a donc (1000 x 30/100) = 300 Mo d'espace utilisé
donc 700 Mo de libre

Dans ce cas on peut alors faire

Code :

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

1
2
 
DBCC SHRINKFILE(yourbase_log, 300)

yourbase_log = le nom logique du fichier de LOG de la base.
C'est le résultat de la commande :

Code :

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

1
2
3
USE yourdatabase
GO
exec sp_helpfile

A+
Etienne ZINZINDOHOUE

**mikedavem** · 04/05/2010, 17h44

Avant de réduire le journal des transactions, posez vous la question de savoir pourquoi celui-ci fait cette taille car l'effet acordéon des opérations d'agrandissement et de réduction ne feront que fragmenter votre disque et altérer considérablement vos performances.

Ceci étant dit l'opération DBCC shrinkfile (ronald, truncateonly) ne doit être utilisé qu'en cas de dernier recours car celui-ci rompt la chaîne séquentielle des LSN du journal des transactions... ce qui est dommage pour vos sauvegardes ...

Regardez le taux de remplissage de ce journal avec les commandes fournies par zinzineti.

Regardez aussi du côté de la vue système sys.databases pour voir si une transaction ne serait pas à l'origine du problème :

Code :

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

1
2
3
4
5
6
 
SELECT 
	[name] AS database_name,
	log_reuse_wait_desc
FROM sys.databases
WHERE database_name = '<maBase>';

++

**SQLpro** · 04/05/2010, 18h02

Plus rapidement voyez s'il n'y a pas une transaction ouverte depuis des lustres en faisant :

Code :

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

1
2
3
4
5
USE mabase;
GO
 
DBCC OPENTRAN (0) WITH TABLERESULTS;
GO

Il est probable qu'une transaction ouverte depuis plusieurs jours n'ait jamais été finalisée.

A +

**Baquardie** · 04/05/2010, 18h16

Merci pour vos réponse

Je viens de travailler là dessus... j'ai suivi les étapes de zinzineti. Le log était rempli à 98%.

J'ai donc fait un back up du log

Code :

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

1
2
backup log ronald to disk = 'E:\SQL\Log\ronald\ronald_backup_log_2010_05_04_manuel3.BAK'
	with noinit

et j'ai reçu ce message d'erreur...

The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.

J'ai googlé sp_repldone et je suis tombée sur cette page :

http://msdn.microsoft.com/fr-fr/library/ms173775.aspx

Et j'ai utilisé la commande suivante :

Code :

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

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

Et miracle ! Le Log Space Used est tombé en bas de 1% !

Cependant le fichier prenait encore quasiment 30 gig...

Alors j'ai refait un backup du log, puis un shrink et enfin... le fichier a rapetissé vivement ! Il est maintenant de 5 gig !

Par contre, lorsque je fais

Code :

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

DBCC SQLPERF (LOGSPACE)

Le Log Space Used est retourné à 98%....

Et j'ai beau fait un back up du log et un shrink, le fichier reste toujours à 5 gig et le Log Space Used dans les environ de 90%...

Est-ce que c'est correct ?

**zinzineti** · 05/05/2010, 00h00

C'est bien normal.
On est passé de 30 gig à 5 gig.
et

Code :

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

DBCC SQLPERF (LOGSPACE)

renvoie Log Space Used = 90%
celà veut dire que 90 % des 5gig[/B] sont utilisés.
Un taux d'occupation de 90 % du fichier du journal de transaction
c'est déjà pas mal.
A+
Etienne ZINZINDOHOUE

**kagemaru** · 05/05/2010, 11h01

Les questions que je me pose sont :

Est-ce que le Change Data Capture est activé sur la base ?
Est-ce qu'il y a (ou plutôt y avait) un flux de réplication actif sur la base ?

Ces 2 fonctionnalités conservent les transactions dans le journal tant qu'elles n'ont pas été consommées (par le log reader ou l'agent CDC)... quoi que tu fasses.

Il faut donc regarder côté SQL Agent s'il n'y a pas un job de CDC ou de réplication actif (bien qu'après le sp_repldone je ne pense pas qu'il reste grand chose).

**dbaffaleuf** · 06/05/2010, 18h03

Envoyé par mikedavem

Ceci étant dit l'opération DBCC shrinkfile (ronald, truncateonly) ne doit être utilisé qu'en cas de dernier recours car celui-ci rompt la chaîne séquentielle des LSN du journal des transactions... ce qui est dommage pour vos sauvegardes ...

Ceci dit je ne suis pas sûr qu'un TRUNCATEONLY puisse faire grand chose au journal dans une base en mode FULL ou BULK_LOGGED. Il y aura toujours un checkpoint ou un autre opérateur qui va traîner en fin de journal et qui bloquera la libération d'espace. (cf fn_dblog(null,null)).

David B.