Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    10

    Unanswered: maximum datafile/log file size

    Is there any limit to the maximum size of a datafile or transaction log you can have with SQL Server 2000 on Windows 2000. Also is there a maximum size that should be adhered to for performance and admin reasons ?.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What number do you have in mind? The limitation is implemented through a Growth Limit setting if AutoGrowth is enabled. If Growth Limit is not set then it will be limited by the amount of free space on the disk.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Books Online {Maximum Capacity Specifications}

    Database size 1,048,516 TB ***
    Databases per instance of SQL Server32,767
    Filegroups per database 256
    Files per database 32,767
    File size (data) 32 TB
    File size (log) 32 TB

    footnote ***
    The data portion of a database cannot exceed 2 GB in size when using the SQL Server 2000 Desktop Engine (MSDE 2000) or the Microsoft Data Engine (MSDE) 1.0. The total size of the database, including log files, can exceed 2 GB provided the sum of the sizes of the data files remains 2 GB or lower.
    in addition, Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. The sum of the number of all these objects in a database cannot exceed 2,147,483,647.

  4. #4
    Join Date
    May 2004
    Posts
    10
    The database I have in mind is going to grow to be order of Terabytes in size, my question about size limits has been answered but can someone put this into the context of performance and admin implications.

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    there is no one fix for this issue.
    querying large sets of data has many obstacles to overcome.
    index creation:
    clustered, non-clustered, composite, indexed views, computed columns
    query creation:
    Probably the most misunderstood part of the dba's job.
    • what are your indexes?
    • what are yhour search arguments?
    • are you computing columns in the queries?
    • join strategies
    • and too many others


    keep your result sets as small as you can and use effective search arguments.
    use stored procedures views create statistics on commonly searched columns that will not be indexed.

    Dont forget that transactions and queries are natural enemies and there is a great benefit to creating an OLAP solution for decision support services.

    Microsoft® SQL Server 2000™ Performance Tuning Technical Reference
    Microsoft SQL Server 2000 RDBMS Performance Tuning Guide for Data Warehousing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •