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

Dans l’article pr\u00e9c\u00e9dent, nous avons vu quels sont les principaux modes de r\u00e9cup\u00e9ration SIMPLE et FULL, et leur effet respectif sur le journal des transactions.<\/p>\n

Ici nous allons aborder le troisi\u00e8me mode BULK-LOGGED, ses avantages, inconv\u00e9nients et contextes d’utilisation. Quelques rappels du post pr\u00e9c\u00e9dent:<\/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 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

Quelles sont ces op\u00e9rations de modification de masse:<\/p>\n

    \n
  1. SELECT INTO<\/li>\n
  2. BCP IN \/ BULK INSERT<\/li>\n
  3. ALTER INDEX … REBUILD<\/li>\n
  4. Manipulation de champs de type TEXT:<\/li>\n<\/ol>\n

    On les appelle parfois par abus de langage op\u00e9rations non logg\u00e9es<\/em>, le terme plus juste est minimallement logg\u00e9es<\/em>.<\/p>\n

    Lorsqu\u2019une de ces op\u00e9rations minimalement logg\u00e9e <\/em>est ex\u00e9cut\u00e9e, seules les allocations de pages sont journalis\u00e9es, mais elles seules ne suffisent pas \u00e0 restaurer l\u2019int\u00e9gralit\u00e9 de mon reindex ou de mon select into en cas de crash. C\u2019est pourquoi pour permettre de restaurer ces donn\u00e9es modifi\u00e9es, la sauvegarde embarque avec elle les pages de donn\u00e9es allou\u00e9es lors de l\u2019op\u00e9ration.<\/p>\n

    Pour le v\u00e9rifier, on se propose de comparer sous les deux modes de r\u00e9cup\u00e9ration FULL et BULK_LOGGED, deux op\u00e9rations minimalement logg\u00e9es :<\/p>\n

      \n
    1. Un SELECT INTO d’une table de 2,6 millions de lignes dans une table d\u2019archive.<\/li>\n
    2. Le rebuild d\u2019un index clusteris\u00e9 sur une table de 2,6 millions de lignes.<\/li>\n<\/ol>\n

      Dans chaque cas, nous allons mesurer plusieurs choses :<\/p>\n

        \n
      1. Le temps d\u2019ex\u00e9cution de l\u2019op\u00e9ration.<\/li>\n
      2. L\u2019impact sur le journal de transactions (en Mb).<\/li>\n
      3. L\u2019impact sur la taille de ma sauvegarde de transaction (en Mb).<\/li>\n<\/ol>\n

        1) <\/span><\/span>En mode de r\u00e9cup\u00e9ration FULL :<\/span><\/strong><\/p>\n

        Pour pouvoir ex\u00e9cuter la sauvegarde du journal \u00e0 l\u2019issue de chaque op\u00e9ration, il faut commencer par ex\u00e9cuter une sauvegarde compl\u00e8te (voir article pr\u00e9c\u00e9dent<\/a>) :<\/p>\n

        ALTER DATABASE CAPDATA SET RECOVERY FULL\r\n\r\nBACKUP DATABASE CAPDATA TO DISK='C:\\YUKON\\BACKUP\\CAPDATA.bak'\r\nWITH init, stats<\/span><\/pre>\n
        Ensuite on va v\u00e9rifier que l\u2019on part sur un journal vide :<\/p>\n
        dbcc sqlperf(logspace)<\/span>\r\n\r\nDatabase Name        Log Size (MB) Log Space Used (%) Status\r\n----------------------------------------------------------------------------------------------\r\nCAPDATA            49,99219      2,951633           0<\/span><\/pre>\n
        \n
        \n
        Le journal fait 50Mb, il est vide (2%). On peut donc lancer le SELECT INTO :<\/p>\n
        set statistics time on\r\nselect * into PIECEAPPARTEMENT2 from PIECEAPPARTEMENT\r\n<\/span><\/pre>\n
         SQL Server parse and compile time: <\/span><\/span><\/pre>\n
         <\/span>CPU time = 0 ms, elapsed time = 48 ms.<\/span><\/span><\/pre>\n
          <\/span>SQL Server Execution Times:<\/span><\/span><\/pre>\n
         <\/span>CPU time = 7641 ms, <\/span>elapsed time = 57673<\/strong> ms.<\/span><\/span><\/pre>\n
         (2644317<\/strong> row(s) affected)<\/span><\/span><\/pre>\n
        Quelle est la taille du journal ?<\/p>\n
        dbcc<\/span> sqlperf(logspace)<\/span><\/span><\/pre>\n
        \r\nDatabase Name        Log Size (MB) Log Space Used (%) Status\r\n----------------------------------------------------------------------------------------------\r\n<\/span><\/span><\/pre>\n
        CAPDATA <\/span>            97,55469       <\/span>93,43568 <\/span>           <\/span>0\r\n<\/span><\/span><\/pre>\n
        \n
        Il est pass\u00e9 de 50 \u00e0 97 Mb, et il est plein. Sauvegardons la transaction pour le vider :<\/p>\n
        BACKUP<\/span> LOG CAPDATA TO<\/span> DISK='C:\\YUKON\\BACKUP\\CAPDATA.trn'<\/span> with<\/span> init, stats<\/span><\/span><\/pre>\n
        \r\n(\u2026)<\/span><\/span><\/pre>\n
        BACKUP LOG successfully processed 11592 pages in 25.525 seconds (3.720 MB\/sec).<\/span><\/span><\/pre>\n
        11592 * 8192 ~= 93 Mb<\/strong>, soit la taille de ma transaction dans mon journal. Normal. Une fois la sauvegarde effectu\u00e9e, on repart sur un journal vide. On peut donc lancer imm\u00e9diatement le REBUILD de l\u2019index sur PIECEAPPARTEMENT :<\/p>\n
        ALTER<\/span> INDEX<\/span> PK_PIECEAPPARTEMENT ON<\/span> PIECEAPPARTEMENT REBUILD WITH<\/span> (SORT_IN_TEMPDB = ON<\/span>)<\/span><\/span><\/pre>\n
        \r\n SQL Server Execution Times:<\/span><\/span><\/pre>\n
         <\/span>CPU time = 11828 ms, <\/span>elapsed time = 66788<\/strong> ms.<\/span><\/span><\/pre>\n
        \n
        \n
        A nouveau le journal est plein et si je le sauvegarde encore, la taille du fichier sera \u00e9gale \u00e0 la taille de ma transaction.<\/p>\n
        dbcc<\/span> sqlperf(logspace)<\/span><\/span><\/pre>\n
        \r\n Database Name <\/span>     Log Size (MB)     Log Space Used (%)    Status<\/span><\/span><\/pre>\n
        ----------------------------------------------------------------------------------------------<\/span><\/span><\/pre>\n
        CAPDATA <\/span>            97,55469          <\/span>92,42763               <\/span>0<\/span><\/span><\/pre>\n
         <\/span><\/p>\n
        \n
        \n
        2) <\/span><\/span>En mode de r\u00e9cup\u00e9ration BULK_LOGGED :<\/span><\/strong><\/p>\n
        M\u00eame sc\u00e9nario mais cette fois la base est pass\u00e9e en mode de r\u00e9cup\u00e9ration BULK_LOGGED, et mon journal est vide. Pour partir sur la m\u00eame enveloppe initiale, et ne pas fausser les r\u00e9sultats pour le SELECT INTO, je vais r\u00e9duire la taille de mon journal \u00e0 sa taille de d\u00e9part (50Mb) :<\/p>\n
        dbcc<\/span> shrinkfile('CAPDATA_log01'<\/span>,50)<\/span><\/span><\/pre>\n
        \r\nDbId <\/span>  FileId   <\/span>    CurrentSize MinimumSize UsedPages <\/span>   EstimatedPages<\/span><\/span><\/pre>\n
        ------ ----------- ----------- ----------- ----------- --------------<\/span><\/span><\/pre>\n
        6 <\/span>     2 <\/span>       6400 <\/span>          6400 <\/span> <\/span>      6400 <\/span>         6400<\/span><\/span><\/pre>\n
        \n
        \n
        Et je lance le SELECT INTO :<\/p>\n
        \r\nSQL Server Execution Times:<\/span><\/span><\/pre>\n
         <\/span>CPU time = 8235 ms, <\/span>elapsed time = 24353<\/strong> ms.<\/span><\/span><\/pre>\n
         (2644317<\/strong> row(s) affected)<\/span><\/span><\/pre>\n
        \n
        D\u00e9j\u00e0, il a mis deux fois moins de temps. Voyons voir la situation du journal :<\/p>\n
        dbcc<\/span> sqlperf(logspace)<\/span><\/span><\/pre>\n
        \r\n Database Name <\/span>Log Size (MB) Log Space Used (%) Status<\/span><\/span><\/pre>\n
        ----------------------------------------------------------------------------------------------<\/span><\/span><\/pre>\n
        CAPDATA <\/span>      49,99219      <\/span>3,073722             <\/span>0<\/span><\/span><\/pre>\n
        \n
        Quasiment rien n\u2019a \u00e9t\u00e9 utilis\u00e9 !! Non seulement l\u2019enveloppe n\u2019a pas boug\u00e9 mais en plus mon journal est toujours quasiment vide. J\u2019en profite pour regarder ce qui se trouve dans ces 3% de <\/span>journal utilis\u00e9:<\/p>\n
        \n
        select<\/span> Operation, COUNT(*) nr_records, SUM([Log Record Length]) totalsize<\/span><\/pre>\n
        from<\/span> ::<\/span>fn_dblog(null<\/span>,null<\/span>) <\/span><\/pre>\n
        group<\/span> by<\/span> Operation order<\/span> by<\/span> COUNT(*) desc<\/span><\/span><\/pre>\n
         <\/span><\/pre>\n
        Operation                       <\/span>nr_records <\/span> totalsize<\/span><\/pre>\n
        ------------------------------- ----------- -----------<\/span><\/pre>\n
        LOP_SET_FREE_SPACE<\/span>             <\/span><\/span> <\/span>11111 <\/span>      577772<\/span><\/pre>\n
        LOP_SET_BITS                    <\/span>5650 <\/span>       328272<\/span><\/pre>\n
        LOP_MODIFY_ROW                  <\/span>1570 <\/span>       138852<\/span><\/pre>\n
        LOP_COUNT_DELTA                 <\/span>33 <\/span>         6864<\/span><\/pre>\n
        LOP_BUF_WRITE                   <\/span>31 <\/span>         59512<\/span><\/pre>\n
        LOP_INSERT_ROWS                 <\/span>27 <\/span>         4068<\/span><\/pre>\n
        LOP_ROOT_CHANGE                 <\/span>5 <\/span>          480<\/span><\/pre>\n
        LOP_BEGIN_XACT                  <\/span>4 <\/span>          432<\/span><\/pre>\n
        LOP_HOBT_DELTA                  <\/span>4 <\/span>          256<\/span><\/pre>\n
        LOP_COMMIT_XACT                 <\/span>4 <\/span>          208<\/span><\/pre>\n
        LOP_LOCK_XACT                   <\/span>3 <\/span>          120<\/span><\/pre>\n
        LOP_HOBT_DDL                    <\/span>2 <\/span>          72<\/span><\/pre>\n
        LOP_PREP_XACT <\/span>                  <\/span>1 <\/span>          68<\/span><\/pre>\n
        LOP_END_CKPT                    <\/span>1 <\/span>          136<\/span><\/pre>\n
        LOP_FORMAT_PAGE                 <\/span>1 <\/span>          84<\/span><\/pre>\n
        LOP_BEGIN_CKPT                  <\/span>1 <\/span>          96<\/span><\/pre>\n
        LOP_CREATE_ALLOCCHAIN           <\/span>1 <\/span>          40<\/span><\/pre>\n
         <\/span><\/p>\n
        \n
        \n
        L\u2019op\u00e9rateur logique LOP_SET_FREE_SPACE est typiquement un op\u00e9rateur d\u2019allocation, qui met \u00e0 jour la page PFS du fichier de donn\u00e9e et indique quels sont les pages qui ne sont plus libres pour de futures allocations car elles ont \u00e9t\u00e9 utilis\u00e9es par le SELECT INTO. Si je lance la sauvegarde de mon journal :<\/p>\n
        \r\nProcessed 149 pages for database 'CAPDATA', file 'CAPDATA_log01' on file 1.<\/span><\/pre>\n
        BACKUP LOG successfully processed 11277 pages in 28.242 seconds (3.270 MB\/sec).<\/span><\/pre>\n
        \n
        \n
        La sauvegarde fait 90Mb alors qu\u2019il n\u2019y a que 1,5Mb de transactions dans mon journal, parce qu\u2019elle embarque les pages de donn\u00e9es allou\u00e9es par le SELECT INTO.<\/p>\n
        Ex\u00e9cutons maintenant le rebuild de l\u2019index :<\/p>\n
        \r\nALTER<\/span> INDEX<\/span> PK_PIECEAPPARTEMENT ON<\/span> PIECEAPPARTEMENT REBUILD WITH<\/span> (SORT_IN_TEMPDB = ON<\/span>)<\/span><\/pre>\n
        \r\n SQL Server Execution Times:<\/span><\/pre>\n
         <\/span>CPU time = 11594 ms, <\/span>elapsed time = 48860 ms<\/strong>.<\/span><\/pre>\n
         <\/span><\/p>\n
        \n
        \n
        48 secondes au lieu de 66, et dans le journal:<\/p>\n
        \r\ndbcc<\/span> sqlperf(logspace)<\/span><\/pre>\n
        \r\n Database Name   <\/span>Log Size (MB)     Log Space Used (%) Status<\/span><\/pre>\n
        ----------------------------------------------------------------------------------------------<\/span><\/pre>\n
        CAPDATA <\/span>        49,99219           <\/span>5,8515<\/strong> <\/span>            0<\/span><\/pre>\n
         <\/span><\/p>\n
        \n
        Et la sauvegarde de journal fait 91 Mb, loin des 5% utilis\u00e9s. L\u00e0 encore, les pages de donn\u00e9es cr\u00e9\u00e9es lors du rebuild sont embarqu\u00e9es dans la sauvegarde.<\/p>\n
        Conclusion<\/strong>: en mode BULK_LOGGED, pour pouvoir restaurer une op\u00e9ration minimalement logg\u00e9e, le BACKUP LOG est oblig\u00e9 de prendre avec lui les pages de donn\u00e9es allou\u00e9es dans l’op\u00e9ration. Ici, on ne voit pas de diff\u00e9rence de taille des sauvegardes de journal entre les modes FULL et BULK_LOGGED, mais si en plus des op\u00e9rations minimalement logg\u00e9es, j’ai des ordres de modifications standards type INSERT\/DELETE,UPDATE, la taille de ma sauvegarde va cro\u00eetre (et le temps de sauvegarde avec) en BULK_LOGGED et d\u00e9passer la taille d’une sauvegarde en mode FULL. Donc attention \u00e0 l’espace disponible  sur les disques de sauvegardes le week end pendant la reorg !<\/p>\n
        David B.<\/p>\n