{"id":5,"date":"2008-06-13T14:16:18","date_gmt":"2008-06-13T13:16:18","guid":{"rendered":"http:\/\/192.168.1.220:8080\/blogs\/sqlserver\/?p=4"},"modified":"2023-04-20T07:03:10","modified_gmt":"2023-04-20T06:03:10","slug":"modes-de-recuperation-et-journal-de-transactions-episode-1","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/modes-de-recuperation-et-journal-de-transactions-episode-1\/","title":{"rendered":"Modes de r\u00e9cup\u00e9ration et journal de transactions, \u00e9pisode 1"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Une petite s\u00e9rie de posts sur les diff\u00e9rents modes de r\u00e9cup\u00e9ration des bases sous SQL Server 2005\/2008, et leur impact sur le journal de transactions.<\/p>\n

Dans ce premier \u00e9pisode:<\/strong><\/p>\n

    \n
  1. Qu’implique le passage de SIMPLE \u00e0 COMPLET.<\/li>\n
  2. Que devient mon journal en mode COMPLET si je ne le sauvegarde pas r\u00e9guli\u00e8rement.<\/li>\n<\/ol>\n

    Le mode de r\u00e9cup\u00e9ration (recovery model<\/em> en anglais) est une propri\u00e9t\u00e9 de base de donn\u00e9es qui va pr\u00e9ciser pour celle-ci deux choses essentielles:<\/p>\n

      \n
    1. Le recyclage de l’espace consomm\u00e9 par les transactions valid\u00e9es dans son journal de transactions.<\/li>\n
    2. La possibilit\u00e9 ou non de pouvoir sauvegarder ces transactions valid\u00e9es.<\/li>\n<\/ol>\n

      Il existe trois modes de r\u00e9cup\u00e9ration: FULL<\/strong>, SIMPLE <\/strong>et BULK-LOGGED<\/strong>.<\/p>\n

      FULL: <\/strong>c’est le mode par d\u00e9faut. Dans ce mode, toutes les transactions sont journalis\u00e9es, m\u00eame les op\u00e9rations de modifications de masse (CREATE INDEX, BULK INSERT ou bcp.exe, SELECT INTO, et les op\u00e9rations sur les champs TEXT). D\u00e8s l’instant o\u00f9 il y a eu au moins une sauvegarde compl\u00e8te sur la base, les transactions valid\u00e9es restent dans le journal. La seule fa\u00e7on de recycler l’espace occup\u00e9 par ces transactions est de lancer r\u00e9guli\u00e8rement une sauvegarde du journal. Les transactions valid\u00e9es sont sauvegard\u00e9es dans un fichier et sont alors supprim\u00e9es du journal * .<\/p>\n

      SIMPLE: <\/strong>dans ce mode, le recyclage des transactions valid\u00e9es est la responsabilit\u00e9 du checkpoint. En plus de flusher sur disque les pages de donn\u00e9es modifi\u00e9es dans le cache de donn\u00e9es, le checkpoint supprime les transactions valid\u00e9es du journal sans les sauvegarder. Cons\u00e9quence de cel\u00e0, je n’ai pas la possibilit\u00e9 de sauvegarder mes transactions en mode SIMPLE. Je ne pourrai ex\u00e9cuter que des sauvegardes compl\u00e8tes ou diff\u00e9rentielles (ou de fichier en lecture seule).<\/p>\n

      BULK-LOGGED: <\/strong>c’est un mode \u00e0 combiner avec le mode FULL. Dans ce mode, les op\u00e9rations de modification de masse (cit\u00e9es plus haut) ne sont pas compl\u00e8tement journalis\u00e9es, et l’impact de telles op\u00e9rations sur le journal est limit\u00e9. Le fait de changer de FULL \u00e0 BULK-LOGGED ne modifie en rien ma fa\u00e7on de sauvegarder mes bases. En mode BULK-LOGGED, mes sauvegardes transactionnelles horaires continuent de s’ex\u00e9cuter sans probl\u00e8me. Tr\u00e8s utile lorsqu’on lance notre r\u00e9organisation des indexes hebdomadaire car on gagne un temps fou.<\/p>\n\n\n\n
      *: <\/strong>il existe au moins deux exceptions \u00e0 cette r\u00e8gle: si la base est principale dans une session de miroir synchrone et que le site miroir est injoignable, o\u00f9 si la base est publi\u00e9e dans le cadre d’une r\u00e9plication transactionnelle et que la distribution ou l’agent de lecture du journal sont injoignables \/ inactifs. Dans ces deux cas, les transactions en attente (non envoy\u00e9es au miroir, non \u00e9crites dans la base distribution) restent dans le journal et ne sont pas vid\u00e9es par les sauvegardes de transactions.<\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

      Note: <\/strong>Attention \u00e0 ne pas confondre mode de r\u00e9cup\u00e9ration FULL et sauvegarde FULL.<\/p>\n

      Pour retrouver le mode de r\u00e9cup\u00e9ration d’une base:<\/strong><\/p>\n

      SELECT<\/span> DATABASEPROPERTYEX('maBase'<\/span>,'RECOVERY'<\/span>) as<\/span> RECOVERYMODE<\/span><\/pre>\n
      Pour changer le mode d’une base:<\/strong><\/p>\n
      \n
      ALTER<\/span> DATABASE<\/span> maBase SET<\/span> RECOVERY [ FULL | SIMPLE | BULK_LOGGED ]<\/span><\/pre>\n
      \n
      1) Premi\u00e8re remarque: il faudra obligatoirement (re)lancer une sauvegarde compl\u00e8te avant de sauvegarder le journal:<\/strong>
      \n– Quand je cr\u00e9\u00e9 ma base.
      \n– Quand je passe de SIMPLE \u00e0 FULL.<\/p>\n
      La sauvegarde de transaction a besoin d’un num\u00e9ro de transaction (LSN pour Log Sequence Number) de r\u00e9f\u00e9rence pour savoir \u00e0 partir de quelle transaction commencer sa sauvegarde. Lorsqu’il n’y a pas eu d’autre sauvegarde de transactions avant elle, comme c’est le cas quand je passe de SIMPLE \u00e0 FULL ou quand je viens de cr\u00e9er ma base, elle se base sur le LSN de fin de la derni\u00e8re sauvegarde compl\u00e8te dans le m\u00eame mode. Le fonctionnement pr\u00e9cis de la sauvegarde \u00e0 chaud fera l’objet d’un autre article prochainement.<\/p>\n
      Pour le mettre en \u00e9vidence, cr\u00e9ons-nous une petite base CRASHTEST:
      \n
      \n<\/span><\/p>\n
      CREATE<\/span> DATABASE<\/span> CRASHTEST<\/span><\/pre>\n
      on<\/span> PRIMARY<\/span><\/span><\/pre>\n
      (NAME='CRASHTEST_data'<\/span>,<\/span><\/pre>\n
      FILENAME='F:\\KATMAI\\MSSQL10.KATMAI\\MSSQL\\DATA\\CRASHTEST_data.mdf'<\/span>,<\/span><\/pre>\n
      SIZE=100MB, MAXSIZE=500MB)<\/span><\/pre>\n
      LOG ON<\/span><\/span><\/pre>\n
      (NAME='CRASHTEST_log'<\/span>,<\/span><\/pre>\n
      FILENAME='F:\\KATMAI\\MSSQL10.KATMAI\\MSSQL\\DATA\\CRASHTEST_log.ldf'<\/span>,<\/span><\/pre>\n
      SIZE=50MB,\r\nMAXSIZE=500MB)<\/span><\/pre>\n
      V\u00e9rifions son mode de r\u00e9cup\u00e9ration:<\/p>\n
      SELECT<\/span> DATABASEPROPERTYEX('CRASHTEST'<\/span>,'RECOVERY'<\/span>) as<\/span> RECOVERYMODE<\/span><\/pre>\n
      RECOVERYMODE\r\n---------------------------------------------------------\r\nFULL<\/pre>\n
      Cr\u00e9ons une petite transaction et essayons de la sauvegarder:<\/p>\n
      use<\/span> CRASHTEST<\/span><\/pre>\n
      create<\/span> table<\/span> TABLE1 (A numeric identity<\/span>, B varchar(5000))<\/span><\/pre>\n
      insert<\/span> into<\/span> TABLE1 values<\/span> (replicate('b'<\/span>,5000))<\/span><\/pre>\n
      backup<\/span> log CRASHTEST to<\/span> disk='C:\\CRASHTEST.log1.trn'<\/span> with<\/span> <\/span>init, stats<\/span><\/pre>\n
      Msg 4214, Level 16, State 1, Line 1<\/em><\/span><\/pre>\n
      \r\nBACKUP LOG cannot be performed because there is no current database backup.<\/em><\/span><\/pre>\n
       <\/em><\/span>
      \nJ’ai donc effectivement besoin d’avoir une sauvegarde compl\u00e8te pour initialiser ma cha\u00eene:<\/p>\n
      backup<\/span> database<\/span> CRASHTEST to<\/span> disk='C:\\CRASHTEST.1.bak'<\/span> with<\/span> init, stats<\/span><\/pre>\n
      Processed 152 pages for database 'CRASHTEST', file 'CRASHTEST_data' on file 1.\r\n100 percent processed.\r\nProcessed 1 pages for database 'CRASHTEST', file 'CRASHTEST_log' on file 1.\r\nBACKUP DATABASE successfully processed 153 pages in 0.266 seconds (4.711 MB\/sec).\r\n<\/em><\/span><\/pre>\n
      OK, je retente de sauvegarder mon log:<\/p>\n
      backup<\/span> log CRASHTEST to<\/span> disk='C:\\CRASHTEST.log1.trn'<\/span> with<\/span> <\/span>init, stats<\/span><\/pre>\n
      100 percent processed.<\/em><\/span><\/pre>\n
      \r\nProcessed 6 pages for database 'CRASHTEST', file 'CRASHTEST_log' on file 1.<\/em><\/span><\/pre>\n
      \r\nBACKUP LOG successfully processed 6 pages in 0.161 seconds (0.283 MB\/sec).<\/em><\/span><\/pre>\n
       <\/em><\/span>
      \nM\u00eame probl\u00e8me lorsque je passe de SIMPLE \u00e0 FULL:<\/p>\n
      ALTER<\/span> DATABASE<\/span> CRASHTEST set<\/span> RECOVERY SIMPLE<\/span><\/pre>\n
      ALTER<\/span> DATABASE<\/span> CRASHTEST set<\/span> RECOVERY FULL<\/span><\/span><\/pre>\n
      backup<\/span> log CRASHTEST to<\/span> disk='C:\\CRASHTEST.log2.trn'<\/span> with<\/span> init, stats<\/span><\/pre>\n
      Msg 4214, Level 16, State 1, Line 1\r\nBACKUP LOG cannot be performed because there is no current database backup.<\/em><\/span><\/pre>\n
      2) SAUVEGARDEZ VOS TRANSACTIONS !<\/strong><\/p>\n
      C’est un probl\u00e8me que l’on retrouve chez beaucoup de clients: grands comptes, PME… Il y a toujours chez eux au moins une base de donn\u00e9es en mode de r\u00e9cup\u00e9ration FULL dont le journal n’a JAMAIS \u00e9t\u00e9 sauvegard\u00e9. Ce n’est pas \u00e9tonnant en un sens puisque FULL est le mode par d\u00e9faut pour toutes les bases cr\u00e9\u00e9es par l’utilisateur. Le r\u00e9sultat est un fichier LDF qui fait 80 Gb parce qu’il contient une ann\u00e9e ou deux de transactions valid\u00e9es, sauvegard\u00e9es dans des backup FULL depuis belle lurette, mais on le r\u00e9p\u00e8te, en mode FULL, il n’y a qu’une sauvegarde de journal qui permet de recycler l’espace occup\u00e9 par des transactions valid\u00e9es.<\/p>\n
      Reprenons notre base CRASHTEST, et cr\u00e9ons un peu de volum\u00e9trie…<\/p>\n
      USE<\/span> CRASHTEST<\/span><\/pre>\n
      declare<\/span> @cpt int<\/span><\/pre>\n
      set<\/span> @cpt=1<\/span><\/pre>\n
      while<\/span>(@cpt<=50000)<\/span><\/pre>\n
      begin<\/span><\/pre>\n
      insert<\/span> into<\/span> TABLE1 values<\/span> (replicate('b'<\/span>,5000))<\/span><\/pre>\n
      set<\/span> <\/span>@cpt=@cpt+1<\/span><\/pre>\n
      end<\/span><\/pre>\n
      go<\/span><\/pre>\n
      …et v\u00e9rifions quelle est la situation du journal \u00e0 l’issue:<\/p>\n
      DBCC<\/span> SQLPERF(LOGSPACE)<\/span><\/pre>\n
      Database Name       Log Size (Mb)    Log Space Used (%)      Status\r\n------------------  ---------------  ----------------------  --------\r\n(...)               (...)            (...)                   (...)\r\nCRASHTEST           253,3047         97,07731                0<\/pre>\n
      Il a plus que quadrupl\u00e9 de volume et il est plein.<\/p>\n
      Pour r\u00e9cup\u00e9rer l’espace sur le disque, il faudra faire un DBCC SHRINKFILE en mode TRUNCATEONLY.
      \nSi je lance le DBCC SHRINKFILE tout de suite:<\/p>\n

      \n<\/span><\/p>\n
      DBCC<\/span> SHRINKFILE(\u2019CRASHTEST_log\u2019,'TRUNCATEONLY\u2019)<\/span><\/span><\/pre>\n
      Dbid        Fileid        CurrentSize        MinimumSize    UsedPages         EstimatedPages\r\n---------   ----------- ----------------  ---------------- ---------------   ------------------\r\n8           2            31680            6400             31680             6400<\/pre>\n
      31680*8192Kb = 247,5 Mb, autant dire que presque rien n’a \u00e9t\u00e9 fait. Cel\u00e0 vient du fait que le TRUNCATEONLY tronque la taille du fichier jusqu’au dernier extent allou\u00e9.
      \nComme mon journal est plein, le dernier extent allou\u00e9 est pr\u00e9cis\u00e9ment \u00e0 la fin du fichier.<\/p>\n
      Donc il faut commencer par vider ce journal: je sauvegarde ma transaction :<\/p>\n
      backup<\/span> log CRASHTEST to<\/span> disk='C:\\CRASHTEST.log3.trn'<\/span> with<\/span> init, stats, compression<\/span><\/pre>\n
      go<\/span><\/pre>\n
      \r\n100 percent processed.<\/span><\/pre>\n
      \r\nProcessed 31461 pages for database 'CRASHTEST', file 'CRASHTEST_log' on file 3.<\/span><\/pre>\n
      \r\nBACKUP LOG successfully processed 31461 pages in 16.391 seconds (15.723 MB\/sec).<\/span><\/pre>\n
       <\/span>
      \nDans mon journal…<\/p>\n
      DBCC<\/span> SQLPERF(LOGSPACE)<\/span><\/pre>\n
      Database Name        Log Size (Mb)    Log Space Used (%)      Status\r\n------------------   ---------------  ----------------------  --------\r\n(...)                (...)            (...)                   (...)\r\nCRASHTEST            247,4922         4,201521                 0<\/pre>\n
      Je retrouve mon enveloppe \u00e0 247,5Mb, et cette fois elle est bien vide. Je vais pouvoir ramener mon log \u00e0 sa taille d’origine:<\/p>\n
      DBCC<\/span> SHRINKFILE(\u2019CRASHTEST_log\u2019,'TRUNCATEONLY\u2019)<\/span><\/span><\/pre>\n

      \n<\/span><\/p>\n
      Dbid        Fileid        CurrentSize        MinimumSize    UsedPages         EstimatedPages\r\n---------   ----------- ----------------  ---------------- ---------------   ------------------\r\n8           2            6400               6400            6400              6400<\/pre>\n
      Attention, si je choisis de rester en mode FULL, je devrai programmer des sauvegardes de transactions (\u00e0 travers le SQL Agent par exemple).<\/p>\n
      Dans le cas o\u00f9 ce journal fait plusieurs dizaines de Gb, je choisis de le vider en passant la base en mode SIMPLE:<\/p>\n
      ALTER<\/span> DATABASE<\/span> CRASHTEST SET<\/span> RECOVERY SIMPLE<\/span><\/pre>\n
      Un checkpoint initi\u00e9 d\u00e8s la fin de l’ALTER se chargera de vider le contenu du journal, et il ne restera plus qu’\u00e0 faire le SHRINKFILE.<\/p>\n
      A suivre l’effet du BULK-LOGGED sur la taille du journal…<\/p>\n
      [ David B. ]<\/p>\n