Discussion :

[tck-lt]

Bonjour,

après plusieurs années passées sur un poste de DBA Oracle (+exploitation+Linux) où je me suis "auto-formé" pour l'administration des BDD (pas de tuning ou autres éléments plus complexes pour ne pas être en porte-à-faux par rapport aux éditeurs), me voilà sur un poste plus orienté MS avec en particulier la mise en place de SharePoint 2013 hébergé sous SQL Server 2012. Je découvre donc pas mal de choses et je tente de ré-utiliser des scripts utilisés par mon prédécesseur sur SQL Server 2005.

Mon problème concerne le shrink de mes fichiers de log. Je lance depuis un script PowerShell le plan de maintenance créé sous Studio. Ce plan de maintenance fait une sauvegarde full de mes BDD + sauvegarde des journaux de transaction (=logfiles). Un 2nd script est ensuite lancé pour faire un shrink de ces logfiles et là le bât blesse, je rencontre l'erreur suivante :

Le contexte de la base de données a changé*; il est maintenant 'WSS_Content_SI'.
Impossible de compacter le fichier journal 2 (WSS_Content_SI_log), car le fichier journal logique situé à la fin du fichier est utilisé.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
24 2 7328 130 7328 128

(1 lignes affectées)
Exécution de DBCC terminée. Si DBCC vous a adressé des messages d'erreur, contactez l'administrateur système.

J'utilise le sql suivant :

USE [WSS_Content_SI]
GO
DBCC SHRINKFILE('WSS_Content_SI_log', 0, TRUNCATEONLY)
GO

appelé par la commande :

sqlcmd -S 127.0.0.1\SHAREPOINT -i "C:\exploite\scripts\Shrink_Log_Bases_Sharepoint\SHRINK_SQL_FILES\WSS_Content_SI_log.sql" -o "C:\exploite\logs\Shrink_Log_Bases_Sharepoint\WSS_Content_SI_log.log"

Quand je passe via Studio pour le faire (clic-droit -> Tâches -> Réduire -> Fichiers puis sélection des journaux de transaction et action "Libérer l'espace inutilisé"), je ne rencontre aucun souci. Avez-vous une explication à me fournir svp ?

2nde question : Est-ce-que ma manière de faire est cohérente svp ?

Merci.

NB : j'irai en formation SQL Server 2012 dans l'année

[Boubou2020]

Bonjour

pourquoi vous faite un shrink log ?

Que te donne ces deux requêtes

Code :

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

DBCC sqlperf(logspace)

Code :

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

1
2
3
4
 
SELECT name, recovery_model_desc
   FROM sys.databases
GO

[tck-lt]

Nous faisons des shrinks car certaines de ces bases (en particulier les bases WSS_) finissent par être énormes et générées un max de logs. Elles sont utilisées par SharePoint (GED), il y a donc un nombre de requêtes très important sur ces bases. On vient d'installer SharePoint 2013 et nous allons commencer la migration depuis le 2007. Sur 2007, ces logs nous ont régulièrement posé problème.

Database Name Log Size (MB) Log Space Used (%) Status
-------------------------------------------------------------------------------------------------------------------------------- ------------- ------------------ -----------
master 1,742188 38,00449 0
tempdb 6,117188 37,67561 0
model 0,9921875 54,52756 0
msdb 19,61719 12,87585 0
SP_Config 424,7422 4,119687 0
SP_AdminContent 19,92969 19,04155 0
SP_Content_Ruche2_1 8,304688 21,59572 0
SP_Content_MySites_1 11,17969 18,18221 0
SP_StateService 1,007813 44,0407 0
SP_Metadata 1,304688 35,40419 0
SP_UserProfile 38,99219 12,65528 0
SP_UserProfile_Sync 57,24219 7,48857 0
SP_UserProfile_Social 1,007813 50,04845 0
SP_UsageAndHealth 11,17969 18,56219 0
SP_Search 101,5547 7,150069 0
SP_Search_CrawlStore 57,24219 7,719735 0
SP_Search_AnalyticsReportingStore 1,007813 42,63566 0
SP_Search_LinksStore 101,5547 5,316274 0
SP_Content_Metiers2_1 5,117188 26,93702 0
SP_Content_Projets2_1 6,242188 22,63767 0
SP_Content_GedApplis2_1 6,242188 23,62328 0
WSS_Content_SITEST 79,99219 33,88881 0
WSS_Content_SI1 513,9922 99,40189 0
WSS_Content_SI 57,24219 33,97281 0
WSS_Content_DSI 4,179688 30,1285 0

(25*ligne(s) affectée(s))

Exécution de DBCC terminée. Si DBCC vous a adressé des messages d'erreur, contactez l'administrateur système.

name recovery_model_desc
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------
master SIMPLE
tempdb SIMPLE
model FULL
msdb SIMPLE
SP_Config SIMPLE
SP_AdminContent SIMPLE
SP_Content_Ruche2_1 SIMPLE
SP_Content_MySites_1 SIMPLE
SP_StateService SIMPLE
SP_Metadata SIMPLE
SP_UserProfile SIMPLE
SP_UserProfile_Sync SIMPLE
SP_UserProfile_Social SIMPLE
SP_UsageAndHealth SIMPLE
SP_Search SIMPLE
SP_Search_CrawlStore SIMPLE
SP_Search_AnalyticsReportingStore SIMPLE
SP_Search_LinksStore SIMPLE
SP_Content_Metiers2_1 FULL
SP_Content_Projets2_1 FULL
SP_Content_GedApplis2_1 FULL
WSS_Content_SITEST FULL
WSS_Content_SI1 FULL
WSS_Content_SI FULL
WSS_Content_DSI FULL

J'ai lu des choses sur le shrink de logs et le mode de récupération de la BDD. Je n'ai pas compris quel est le lien entre les 2. Merci de m'éclairer svp.

[Boubou2020]

Utilisez la commande

Code :

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

DBCC OPENTRAN

pour voir quelle transaction est en cours.

[tck-lt]

Bonjour,

cela a fonctionné pour une partie des bases, les autres ont l'erreur indiquée.

Je viens de refaire le test sur une base voici le script SQL et le résultat associé :

Script :
USE [WSS_Content_SI]
GO
DBCC OPENTRAN
GO
DBCC SHRINKFILE('WSS_Content_SI_log', 0, TRUNCATEONLY)
GO


Résultat :
Le contexte de la base de données a changé*; il est maintenant 'WSS_Content_SI'.
Aucune transaction ouverte active.
Exécution de DBCC terminée. Si DBCC vous a adressé des messages d'erreur, contactez l'administrateur système.
Impossible de compacter le fichier journal 2 (WSS_Content_SI_log), car le fichier journal logique situé à la fin du fichier est utilisé.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
24 2 7328 130 7328 128

(1 lignes affectées)
Exécution de DBCC terminée. Si DBCC vous a adressé des messages d'erreur, contactez l'administrateur système.

[Boubou2020]

votre base était mis en réplication

Que Te donne cette requête ?

Code :

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

select name, database_id,recovery_model_desc,log_reuse_wait_desc from sys.databases

il se peut que votre journal n’a pas été tronqué car des enregistrements dans le journal sont en attente de réplication

[tck-lt]

Voici le résultat de la requête :

name database_id recovery_model_desc log_reuse_wait_desc
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------------------------ ------------------------------------------------------------
master 1 SIMPLE NOTHING
tempdb 2 SIMPLE CHECKPOINT
model 3 FULL LOG_BACKUP
msdb 4 SIMPLE NOTHING
SP_Config 5 SIMPLE NOTHING
SP_AdminContent 6 SIMPLE CHECKPOINT
SP_Content_Ruche2_1 7 SIMPLE NOTHING
SP_Content_MySites_1 8 SIMPLE NOTHING
SP_StateService 9 SIMPLE NOTHING
SP_Metadata 10 SIMPLE NOTHING
SP_UserProfile 11 SIMPLE NOTHING
SP_UserProfile_Sync 12 SIMPLE NOTHING
SP_UserProfile_Social 13 SIMPLE NOTHING
SP_UsageAndHealth 14 SIMPLE CHECKPOINT
SP_Search 15 SIMPLE NOTHING
SP_Search_CrawlStore 16 SIMPLE CHECKPOINT
SP_Search_AnalyticsReportingStore 17 SIMPLE NOTHING
SP_Search_LinksStore 18 SIMPLE CHECKPOINT
SP_Content_Metiers2_1 19 FULL LOG_BACKUP
SP_Content_Projets2_1 20 FULL LOG_BACKUP
SP_Content_GedApplis2_1 21 FULL LOG_BACKUP
WSS_Content_SITEST 22 FULL LOG_BACKUP
WSS_Content_SI1 23 FULL LOG_BACKUP
WSS_Content_SI 24 FULL LOG_BACKUP
WSS_Content_DSI 25 FULL LOG_BACKUP

Qu'est-ce-que la réplication au sens SQL Server stp (tu peux me tutoyer) ?

[Boubou2020]

Faite un bakup log de votre base WSS_Content_SI

Code :

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

BACKUP LOG WSS_Content_SI TO DISK='C:\fileName.TRN'

puis faite un shrinkfile

[SQLpro]

D'abord la commande SHRINK est une commande à n'utiliser qu'en cas extrême.
En aucun cas il ne faut systématiser son usage, surtout dans un plan de maintenance.
Si vous aviez suivi un cours de DBA, c'est l'un des points les plus important.
Il faut faire en sorte de provisionner correctement vos fichiers pour l'exploitation de votre base et ne jamais avoir recours (ou très rarement) à une augmentation de volume des fichiers, ni être dans l'obligation de réduire la taille des fichiers.

Pour cela il est impératif de savoir comment générer les redo log avec SQL Server :

1) en mode de récupération FULL ou BULK LOGGED, pour générer les redo log, il faut impérativement effectuer des sauvegardes du journal de transaction

REDO LOG (oracle) = SAUVEGARDE TRANSACTIONNELLE (SQL Server)

Pour savoir votre mode de journalisation :

SELECT name, recovery_model_desc
FROM sys.databases

2) en mode SIMPLE, impossible de générer les redo log ! En fait, le journal de transaction est auto purgé...

Donc, aucune crainte à avoir.

A quelle fréquence faire des sauvegardes du JT ?

Cela dépend de l'activité transactionnelle. Vous pouvez le faire toutes les heures voir toutes les minutes. Faites le aux heures de production.

Que fait la sauvegarde du journal de transaction ?

• Elle copie toutes les transactions du journal qui ont eu lieu depuis la dernière sauvegarde qu'elle qu'en soit sa nature.
• Elle purge le journal de transaction, ce qui permet de contenir le fichier dans une dimension raisonnable

Quelles sont les bonnes pratiques ?

Dimensionner correctement tous les fichiers de la base pour la durée de production attachée au serveur (par exemple 3 à 5 ans)
Pour les données, c'est assez facile à estimer à l'aide d'une règle de trois
Pour le journal de transactions prenez 20 à 30 % du volume des données (20% si grosse base, 30 % si petite)

Mettez vos bases de production "métier" en mode de journalisation FULL et faite des sauvegardes régulières de vos JT

Mettez vos bases de DataWarehousing en mode de journalisation SIMPLE et ne faites rien.

Pourquoi ne jamais faire de SHRINK et dimensionner large ?

Cela s'apelle du capacity planning et fait partie du métier de DBA.
Les opérations de croissances et décroissance des fichiers sont des opérations plus que très pénalisantes. Elle peuvent conduire à des dysfonctionnement de SQL Server comme en témoigne cet internaute qui lui aussi n'a rien compris au film :
http://www.developpez.net/forums/d15...o-grow-failed/

Pour info, voici ce que je dis dans un de mes papiers :
"
Pire ! C’est possible…
Obnubilé par la taille des fichiers d’une base de données, certains développeurs débutant et certains pseudo DBA (ceux qui n’ont généralement jamais suivi une formation de DBA – administrer des bases de données, c’est un vrai métier…) veulent à tout prix, réduire la taille des fichiers… Ils mettent en œuvre pour cela un plan de maintenance avec réduction de la taille des fichiers (SHKRINFILE, SHRINKDATABASE, AUTO_SHRINK…) ce qui fait que non seulement ils perdent du temps à faire croitre les fichiers, mais aussi à les faire décroitre et recroitre encore… Bref, un vrai accordéon (ces pseudo DBA devrait se faire musiciens, ils ont plus à y gagner visiblement…).
Posons une analogie. Imaginons que vous construisez un parking. Il est plein, mais il y a encore de la surface non structurée. Une voiture arrive. Vous vous mettez à lisser le béton, peindre le sol et délimiter la place. La voiture repart, vous prenez votre marteau piqueur afin de détruire votre travail… Au moins avec une telle stratégie, c’est le secteur du bâtiment qui va y gagner !!!
"

À lire :
http://blog.developpez.com/sqlpro/p5..._fichiers_et_t

Papier officiels de MS sur le sujet :
https://support.microsoft.com/en-us/kb/315512
Une explication détaillée :
https://www.simple-talk.com/sql/data...owth-settings/

D'autres considérations dans notre livre, recommandée pour apprendre le métier de DBA et éviter de telles conneries...!


A +

[tck-lt]

Bonjour,

comme je l'indique, j'irai en formation SQL Server. Je suis d'accord avec vous, DBA est un métier à part.

Si j'ai bien compris la 1ère partie du message, il ne me sert à rien de faire des sauvegardes des journaux de transaction si la BDD n'est pas en mode FULL (SIMPLE = NOARCHIVELOG d'Oracle).

Pour la 2nde partie, nous avons bien provisionné l'espace disque pour la partie "DATA", nous ne ferons donc jamais de SHRINK sur les fichiers de données. Pour les LOGS, c'est le prestataire qui nous le conseille. Je ne vois pas en quoi cela peut poser problème. Sous Oracle par exemple, nous vidons les redo (qui sont de taille fixe, je vous l'accorde) dans des fichiers que nous sauvegardons/purgeons avec RMAN par la suite. Si je fais un BACKUP de mes LOGS, je peux donc enchainer avec un SHRINKFILE de ceux-ci, non ?

Que fait la sauvegarde du journal de transaction ?

•Elle copie toutes les transactions du journal qui ont eu lieu depuis la dernière sauvegarde qu'elle qu'en soit sa nature.
•Elle purge le journal de transaction, ce qui permet de contenir le fichier dans une dimension raisonnable

C'est la commande BACKUP LOG à elle seule qui fait ces 2 actions ou c'est BACKUP LOG + DBCC SHRINKFILE svp ?

[Oishiiii]

La commande BACKUP LOG va marquer certaines parties du fichier comme ré-utilisables pour écrire les prochains log records. La taille du fichier ne va pas diminuer.

Tout a été dit par SQLpro, Shrinker les journaux de transaction n'est pas une bonne pratique.
Dimensionnez vos fichiers pour qu'ils contiennent l'activité de votre base entre deux BACKUP LOG et ne shrinkez pas.
Comme sur Oracle.

[tck-lt]

Je commence à comprendre (je comprends vite mais faut m'expliquer longtemps )

Le BACKUP LOG va libérer de l'espace logique sur mon fichier de log mais ne va pas libérer de l'espace au sens physique (ce que fait le shrink). Vu que je fais un BACKUP LOG quotidien, il faut que je calcule la taille physique de mon fichier de log pour chaque BDD, ce qui re-représente environ 20% de la taille future max de ma BDD. Le jour où cette taille est atteinte sur mon fichier de log, devrais-je faire un shrink ou une extension de mon fichier svp ? Dans quel cas doit-on utiliser le shrink de fichiers de log du coup, jamais ?

[Oishiiii]

Ce qui compte ce n'est pas la taille des DATA, c'est l'activité, notamment transactionnelle sur la BDD.
Si l'activité de votre base augmente, plutôt qu'augmenter la taille du fichier, vous pouvez également augmenter la fréquence des BACKUP LOG (toutes les 2 heures, toutes les heures, toutes les 5 minutes, etc..).
Si vous faites un seul BACKUP LOG par jour, et juste après le FULL, peut-être que vous pourriez passer en mode SIMPLE. A voir en fonction de votre RPO.

Dans quel cas doit-on utiliser le shrink de fichiers de log du coup, jamais ?

Dans des cas exceptionnels, pas de manière automatisée en tout cas.
Quand une grosse transaction reste ouverte et fait anormalement grossir le fichier de log par exemple.

[tck-lt]

Merci pour ces infos. En fait, il va y avoir un gros paquet de transactions lors de l'import (migration SharePoint 2007 -> 2013), je peux donc faire un shrink unique après la migration et ensuite me contenter des backups quotidiens.

Merci pour vos conseils.