Discussion :

[janlouk]

Bonjour à tous,

Nos backup se font via Redgate, pour la compression, sur SQL 2005.

J'ai activé IFI sur l'instance il y a presque 1 an. Avant hier, j'ai crée une DB qui est en SIMPLE (100 GB le .mdf et 1 GB le .ldf).

Elle n'est pas utilisée, elle est donc vide. Hier je remarque qu'elle ne fait plus que 4 mb le .mdf et 32 mb le .ldf. Je me suis dit, je suis fou, et je la repasse à 100 GB et rebelotte ce matin.

La DB model fait 4 Mb .mdf et 6 Mb .ldf

Malheureusement je n'ai pas une vue sur Redgate car nous ne gérons pas les backups (merci l'externalisation des services...). S'il y avait eu un shrink sur les db de l'instance, je devrais le remarquer que sur toutes mes DB qu'il y a 0% d'espace libre mais ce n'est pas le cas.

Ou alors, ces espaces sont remplis de 0 avant que j'ai activé l'IFI et pas celui de ma DB de 100 GB qui est vide car là j'avais activé l'IFI entre temps.

Je me fais bien comprendre ? Je ne sais pas du tout par où chercher. Et j'aimerais éviter d'ouvrir un ticket, qu'il aille en Pologne, qu'on me le remballe car on ne comprend pas ma question, que je réponde et hop on est parti pour 3 semaines avec un ticket clôturé sans aucune solution :-)

Merci pour vos idées.

Jean-Luc

[elsuket]

Salut Jean-Luc,

Si on est certains que Le logiciel de RedGate n'exécute pas de SHRINK, est-ce que la base n'aurait pas l'option AUTO_SHRINK à ON ?

Code :

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

1
2
3
4
SELECT	name
	, is_auto_shrink_on
FROM	sys.databases
WHERE	name = 'maDB'

Sinon il te faudra prendre une trace côté serveur pour voir ce qui provoque ce rétrécissement.

@++

[mikedavem]

Hello Jean-Luc,

Tu peux essayer de te servir de la trace par défaut SQL Server pour voir si quelqu'un exécute un SHRINK à la main ou si cela vient d'une routine auto comme l'explique Elsuket

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
 
declare @path nvarchar(4000);
 
select @path = [path] 
from sys.traces where id = 1
 
SELECT 
    TextData,
    HostName,
    ApplicationName,
	DatabaseName,
    LoginName, 
    StartTime,
	EventClass  
FROM fn_trace_gettable(@path, DEFAULT) 
WHERE (TextData LIKE '%SHRINKFILE%' -- manual shrink
OR EventClass IN (94, 95)) -- auto shrink log / data
	AND EventClass <> 175

PS : Une autre solution plus sauvage ... ah oui vous voulez jouer au shrink sauvage .. pas de souci je crée une table et je la remplis de 100GB de données pour bien réserver mon espace ... ok je sors


++

[janlouk]

Ok merci. Non "malheureusement" elle ne l'est pas en auto_shrink.

Bon, je créerai une trace et on verra. J'ai pas le choix...

Merci pour vos réponses.

[mikedavem]

N'oublie pas la trace par défaut qui est capable de te remonter ce genre d'information sans que tu aies à créer une trace additionnelle.

Bon courage

++

[janlouk]

N'oublie pas la trace par défaut qui est capable de te remonter ce genre d'information sans que tu aies à créer une trace additionnelle.

Bon courage

++

Pas sur de comprendre. La trace par defaut, tu veux dire, je crée une trace et je choisi juste comme filtre l'ID de ma DB et c'est tout. Je laisse les options par defauts ?

[mikedavem]

En fait depuis 2005, tu as déjà une trace par défaut qui tourne sur tous les serveurs SQL permettant d'auditer un certain nombre d'événements sans avoir à créer ses propres traces. (BOL). Je l'utilise beaucoup dans mes audits pour savoir rapidement si des auto shrink, auto grow ont été déclenché sur les bases de données par exemple.

Du coup dans ton cas je pense que tu peux déjà commencer par utiliser la trace par défaut avec le script que je t'ai donné pour savoir rapidement si quelqu'un a joué avec des shrinks manuels.
La trace par défaut possède l'id = 1 d'où le bout de code que je t'ai donné plus haut

Code :

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

1
2
3
4
5
6
declare @path nvarchar(4000);
 
select @path = [path] 
from sys.traces where id = 1
 
....

++

[janlouk]

C'est super ta query, je ne connaissais pas. Merci.

Malheureusement le résultat est vide. Par contre, je remarque effectivement qu'il y a des shrink des ldf et mdf sur d'autres instances en NonProd. Je vais analyser ça.

On a redémarré cette instances hier , j'ignore si ça vide le contenu de la trace par defaut ?

Il garde l'historique sur combien de jour ?

J'ai agrandi la DB maintenant, je vais créer une trace manuellement et voir si je trouve quelques choses.

[Bernardos]

non le fait de réinitialiser ton instance ne réinitialise pas ta trace par défaut.
Par contre va voir dans ton répertoire d'installation de ton instance (genre c:\programme\Microsoft sqlerver\MSSQL9.xxx.toninstance\MSSQL\Log) cherche les fichiers .trc et regarde le plus vieux datent de quand?
tu n'as pas de plan de maintenances "fantomes" qui font des shrink ou un quelconque job sql "fantome" qui ferait ca? ca m'arrive de voir ça chez des clients.
Cordialement,
Loïc

[janlouk]

Bonjour Loïc,

J'ai fait une recherche sur tous les fichiers .trc et le plus vieux fichier de mon instance date d'hier à 21h07. Je ne trouve aucun plan de maintenance ou de job malheureusement.

Quand j'exécute la query de David, j'ai aucun résultat car il va chercher le dernier fichier .trc. Il font tous 20.480 kb et il en garde que 5 apparemment et en crée un nouveau dès qu'il a atteint 20.480 kb. Par contre, quand j'exécute la query retravaillé de mon collègue, sur base de celle de David, j'ai effectivement un shrink qui est fait. On a trouvé pourquoi, il y a une différence entre les 2, ça se situe au niveau du path. Sa query va chercher dans le fichier log.trc, pourtant quand on va sur le serveur, on ne le voit nulle part (oui on fait apparaitre les fichiers cachés et systèmes aussi )

Puis-je lui dire de garder ces fichiers plus longtemps et qu'il soit plus gros ?

Voici la query :

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
declare @path nvarchar(4000);
SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +
                      '\log.trc'
FROM   sys.traces
WHERE  id = 1
 
SELECT substring(databasename,1,25) AS DatabaseName,
       substring(e.name,1,40)   AS eventname, TextData, EventClass,
       substring(cat.name,1,20) AS [CategoryName],
       starttime,
       e.category_id,
       substring(loginname,1,25) as loginname,
       substring(hostname,1,25) as hostname,
       servername,
       applicationname
FROM   ::fn_trace_gettable(@path, 0)
       INNER JOIN sys.trace_events e
         ON eventclass = trace_event_id
       INNER JOIN sys.trace_categories AS cat
         ON e.category_id = cat.category_id
WHERE  
(
(e.name like '%Shrink%' or TextData LIKE '%SHRINK%')
/*OR
(e.name like ( '%Grow%' ) or TextData LIKE '%Grow%')*/
)
AND EventClass <> 175 --AND DatabaseName = '*****'
ORDER  BY starttime DESC

EDIT : Le temps que j'écrive ce message, il m'a effacé celui d'hier et un nouveau a été créé.

[Bernardos]

Salut,
Le fait que ton plus vieux fichier .trc date d'hier et maintenant d'aujourd'hui Montre que tu as plus 100Mo de traces par jour.
J'ai déjà vu ca chez un de mes clients y avait des chiées de object:deleted et object:created
du coup, si tu regardes l'activité de ton disque avec le moniteur de ressources, tu constateras que t'as forcément de l'activité en permanence sur ce fichier.
et au final l'information est (dans mon cas) inexploitable.
Que contient tes fichiers de traces?


Pour info,ici tu trouveras pas mal de requêtes te permetant d'exploiter le contenu de la trace par défaut.


Et au final, t'as identifié qui fait ce shrink ou pas? t'as toujours le résultat de la requête qui t'avais retourné une ligne?
Cordialement,

Loïc

[elsuket]

Bonjour,

Quand j'exécute la query de David, j'ai aucun résultat car il va chercher le dernier fichier .trc. Il font tous 20.480 kb et il en garde que 5 apparemment et en crée un nouveau dès qu'il a atteint 20.480 kb. Par contre, quand j'exécute la query retravaillé de mon collègue, sur base de celle de David, j'ai effectivement un shrink qui est fait. On a trouvé pourquoi, il y a une différence entre les 2, ça se situe au niveau du path. Sa query va chercher dans le fichier log.trc, pourtant quand on va sur le serveur, on ne le voit nulle part (oui on fait apparaitre les fichiers cachés et systèmes aussi )

Effectivement le moteur ne conserve que 5 fichiers de trace, qui sont tous numérotés (log_XXX.trc). Un fichier de trace système est crée à chaque démarrage d'instance.
Sur la taille en revanche, je ne sais pas quelle est la limite, mais le fait que tu observes 20Mo sur chacun des fichiers est révélateur.

La requête de David lit le fichier de trace courant; la requête que tu donnes lit tous les fichiers; c'est pour cela que tu as trouvé des SHRINK avec cette dernière.

On peut écrire directement le FROM comme suit :

Code :

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

1
2
3
4
5
6
7
8
9
FROM		sys.fn_trace_gettable
		(
			(
				SELECT	LEFT(path, LEN(path) - CHARINDEX('\', REVERSE(path))) + '\Log.trc'
				FROM	sys.traces -- read all five TR files
				WHERE   is_default = 1
			)
			, 6
		) AS FTG

Puis-je lui dire de garder ces fichiers plus longtemps et qu'il soit plus gros ?

Je crois que malheureusement, ce n'est pas paramétrable. Ce que l'on peut faire par contre, c'est lire le fichier et centraliser son contenu dans une base de données, de façon à avoir une baseline.

J'ai déjà vu ca chez un de mes clients y avait des chiées de object:deleted et object:created
du coup, si tu regardes l'activité de ton disque avec le moniteur de ressources, tu constateras que t'as forcément de l'activité en permanence sur ce fichier.

Ces deux types d'événement sont généralement liés à la base de données TempDB, et révèle l'utilisation de tables temporaires, de variables de type TABLE, mais aussi le tri de reconstruction d'index ou du recalcul des statistiques : il suffit de regarder la colonne ObjectName de cette trace pour s'en convaincre

@++

[janlouk]

Que contient tes fichiers de traces?

Malheureusement pas de shrink dans ce cas-ci car ce fichier est renouvellé trop de fois alors que le shrink a été fait hier à 23h30. Par contre, je ne parviens toujours pas à comprendre pourquoi j'ai un résultat via la query que j'ai posté.

Pour info,ici tu trouveras pas mal de requêtes te permetant d'exploiter le contenu de la trace par défaut.

MERCI

Et au final, t'as identifié qui fait ce shrink ou pas? t'as toujours le résultat de la requête qui t'avais retourné une ligne?

Non pas encore sûr à 100 % mais de toute façon, la règle pour le moment c'est un shrink tous les soirs et rien n'y changera malgré que c'est la pire chose qu'on puisse faire sur une DB. Mais avec ce que vous m'avez tous donné, j'ai une idée d'où ça vient...

[Bernardos]

Malheureusement pas de shrink dans ce cas-ci car ce fichier est renouvellé trop de fois alors que le shrink a été fait hier à 23h30.

ma question est plutot dans le sens d'essayer de détecter pourquoi tu as autant de trace. c'est potentiellement un problème. genre un utilisateur qui se connecte 1000(chiffre au hasard) fois par seconde avec échec de connexion.
donc que vois tu comme évenment répétitif dans tes traces par défaut?

Pour choper ton shrink tu peux utiliser le profiler avec l'évenement security audit - audit DBCC event

Par contre, je ne parviens toujours pas à comprendre pourquoi j'ai un résultat via la query que j'ai posté.

ah ca, moi pas comprendre non plus. là si tu lances ta requete maintenant tu as un résultat? si oui c'est que t'as forcément un fichier qui contient encore l'information
Cordialement,
Loïc

[janlouk]

C'est beaucoup de object:created, object:deleted.

C'est un serveur BI, c'est surement pour cela. C'est beaucoup entre 23h00 et 7h00 du matin. Sinon après on dirait que c'est calme.

Mais merci à tous les 3, je sais maintenant où chercher pour les prochaines fois.

Mais pour maintenant, j'arrette de chercher, je sais que ça vient d'un script/job d'un serveur où je n'ai pas accès. Et je ne peux pas empêcher celà et que ça ne changera pas. Sauf quand on aura tout migrer en 2012 mais d'ici là...