Documentation Source Text

Check-in [be40d822e3]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improvements to journal_size_limit documentation.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: be40d822e3680faaafc126dccb94fea3559c3bf9
User & Date: drh 2011-11-30 18:46:56.044
Context
2011-12-01
02:30
Changes to make capi3ref processing more robust. (check-in: ababf67f17 user: drh tags: trunk)
2011-11-30
18:46
Improvements to journal_size_limit documentation. (check-in: be40d822e3 user: drh tags: trunk)
2011-11-25
17:51
Update the changes log for sqlite3_stmt_busy(). (check-in: 8092164ae8 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/compile.in.
85
86
87
88
89
90
91
92
93


94
95

96
97
98
99
100
101
102
  enforcement of foreign key constraints on and off and run-time using
  the [foreign_keys pragma].  Enforcement of foreign key constraints
  is normally off by default, but if this compile-time parameter is
  set to 1, enforcement of foreign key constraints will be on by default.
}

COMPILE_OPTION {SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT=<i>&lt;bytes&gt;</i>} {
  This option sets the size limit on rollback journal files in
  [journal_mode pragma | persistent journal mode].  When this 


  compile-time option is omitted there is no upper bound on the
  size of the rollback journal file.  The journal file size limit

  can be changed at run-time using the [journal_size_limit pragma].
}

COMPILE_OPTION {SQLITE_DEFAULT_LOCKING_MODE=<i>&lt;1 or 0&gt;</i>} {
  If set to 1, then the default [locking_mode] is set to EXCLUSIVE.
  If omitted or set to 0 then the default [locking_mode] is NORMAL.
}







|
|
>
>

|
>







85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
  enforcement of foreign key constraints on and off and run-time using
  the [foreign_keys pragma].  Enforcement of foreign key constraints
  is normally off by default, but if this compile-time parameter is
  set to 1, enforcement of foreign key constraints will be on by default.
}

COMPILE_OPTION {SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT=<i>&lt;bytes&gt;</i>} {
  This option sets the size limit on [rollback journal] files in
  [journal_mode pragma | persistent journal mode] and
  [locking_mode | exclusiving locking mode] and on the size of the
  write-ahead log file in [WAL mode]. When this 
  compile-time option is omitted there is no upper bound on the
  size of the rollback journals or write-ahead logs.  
  The journal file size limit
  can be changed at run-time using the [journal_size_limit pragma].
}

COMPILE_OPTION {SQLITE_DEFAULT_LOCKING_MODE=<i>&lt;1 or 0&gt;</i>} {
  If set to 1, then the default [locking_mode] is set to EXCLUSIVE.
  If omitted or set to 0 then the default [locking_mode] is NORMAL.
}
Changes to pages/pragma.in.
434
435
436
437
438
439
440
441

442
443
444
445
446
447
448

    <p>^(The PERSIST journaling mode prevents the rollback journal from
    being deleted at the end of each transaction.  Instead, the header
    of the journal is overwritten with zeros.)^  This will prevent other
    database connections from rolling the journal back.  The PERSIST
    journaling mode is useful as an optimization on platforms where
    deleting or truncating a file is much more expensive than overwriting
    the first block of a file with zeros.</p>


    <p>^The MEMORY journaling mode stores the rollback journal in 
    volatile RAM.  ^This saves disk I/O but at the expense of database
    safety and integrity.  ^If the application using SQLite crashes in
    the middle of a transaction when the MEMORY journaling mode is set,
    then the database file will very likely go corrupt.</p>








|
>







434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449

    <p>^(The PERSIST journaling mode prevents the rollback journal from
    being deleted at the end of each transaction.  Instead, the header
    of the journal is overwritten with zeros.)^  This will prevent other
    database connections from rolling the journal back.  The PERSIST
    journaling mode is useful as an optimization on platforms where
    deleting or truncating a file is much more expensive than overwriting
    the first block of a file with zeros.  See also:
    [PRAGMA journal_size_limit] and [SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT].</p>

    <p>^The MEMORY journaling mode stores the rollback journal in 
    volatile RAM.  ^This saves disk I/O but at the expense of database
    safety and integrity.  ^If the application using SQLite crashes in
    the middle of a transaction when the MEMORY journaling mode is set,
    then the database file will very likely go corrupt.</p>

471
472
473
474
475
476
477
478

479
480
481
482


483
484
485




486

487
488

489
490
491
492
493
494


495
496
497
498
499

500
501
502
503
504
505
506
507
508
509
510
511
512
}

Pragma journal_size_limit {
    <p><b>
    PRAGMA journal_size_limit<br>
    PRAGMA journal_size_limit = </b><i>N</i> <b>;</b>

  <p>^If a database connection is operating in either "exclusive mode"

  (PRAGMA locking_mode=exclusive) or "persistent journal mode"
  (PRAGMA journal_mode=persist) then under certain circumstances
  after committing a transaction the journal file may remain in
  the file-system. This increases efficiency but also consumes


  space in the file-system. After a large transaction (e.g. a VACUUM),
  it may consume a very large amount of space.





  <p>^This pragma may be used to limit the size of journal files left

  in the file-system after transactions are committed on a per database
  basis.  ^Each time a transaction is committed, SQLite compares the

  size of the journal file left in the file-system to the size limit
  configured using this pragma and if the journal file is larger than the
  limit allows for, it is truncated to the limit.

  <p>^The second form of the pragma listed above is used to set a new limit
  in bytes for the specified database.  ^A negative number implies no limit.


  ^Both the first and second forms of the pragma listed above return a single
  result row containing a single integer column - the value of the journal
  size limit in bytes. ^The default limit value is -1 (no limit), which
  may be overridden by defining the preprocessor macro
  [SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT] at compile time.</p>


  <p>^This pragma only operates on the single database specified prior
  to the pragma name (or on the "main" database if no database is specified.)
  There is no way to operate on all attached databases using a single
  PRAGMA statement, nor is there a way to set the limit to use for databases
  that will be attached in the future.
}


Pragma legacy_file_format {
   <p>^(<b>PRAGMA legacy_file_format;
       <br>PRAGMA legacy_file_format = <i>boolean</i></b></p>
    <p>This pragma sets or queries the value of the legacy_file_format







|
>
|
|
|
|
>
>
|
|

>
>
>
>
|
>
|
|
>
|
|
|



>
>


|
<
|
>



|
|
|







472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509

510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
}

Pragma journal_size_limit {
    <p><b>
    PRAGMA journal_size_limit<br>
    PRAGMA journal_size_limit = </b><i>N</i> <b>;</b>

  <p>^If a database connection is operating in
  [locking_mode | exclusive locking mode] or in
  [journal_mode | persistent journal mode] 
  (PRAGMA journal_mode=persist) then
  after committing a transaction the [rollback journal] file may remain in
  the file-system. This increases performance for subsequent transactions
  since overwriting an existing file is faster than append to a file,
  but it also consumes
  file-system space. After a large transaction (e.g. a [VACUUM]),
  the rollback journal file may consume a very large amount of space.

  <p>Similarly, in [WAL mode], the write-ahead log file is not truncated
  following a [checkpoint].  Instead, SQLite reuses the existing file
  for subsequent WAL entries since overwriting is faster than appending.

  <p>^The journal_size_limit pragma may be used to limit the size of 
  rollback-journal and WAL files left
  in the file-system after transactions or checkpoints.
  ^Each time a transaction is committed or a WAL file resets, SQLite 
  compares the size of the rollback journal file or WAL file left in 
  the file-system to the size limit
  set by this pragma and if the journal or WAL file is larger 
  it is truncated to the limit.

  <p>^The second form of the pragma listed above is used to set a new limit
  in bytes for the specified database.  ^A negative number implies no limit.
  ^To always truncate rollback journals and WAL files to their minimum size, 
  set the journal_size_limit to zero.
  ^Both the first and second forms of the pragma listed above return a single
  result row containing a single integer column - the value of the journal
  size limit in bytes. ^The default journal size limit is -1 (no limit).  The

  [SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT] preprocessor macro can be used to change
  the default journal size limit at compile-time.</p>

  <p>^This pragma only operates on the single database specified prior
  to the pragma name (or on the "main" database if no database is specified.)
  There is no way to change the journal size limit on all attached databases
  using a single PRAGMA statement.  The size limit must be set separately for
  each attached database.
}


Pragma legacy_file_format {
   <p>^(<b>PRAGMA legacy_file_format;
       <br>PRAGMA legacy_file_format = <i>boolean</i></b></p>
    <p>This pragma sets or queries the value of the legacy_file_format