SQL Server: Database Basics November 19th, 2012

Vinod Kumar

Database learning can never stop. Based on the previous posts on the basics I saw few comments asking to cover some of the basics in a one liner mode. In this blog post, we will talk about the database fundamentals of system DB’s, what are the DB files and what are the filegroups. I get an opportunity to talk to a lot of developers who come from the compete platform and are often asking these fundamental and basic questions. I am sure this blog post will help them understand these fundamentals.

System Databases

  1. Master: composed of system tables that keep track of server installation as a whole and all other databases that are eventually created. Master DB has system catalogs that keep info about disk space, file allocations and usage, configuration settings, endpoints, logins, etc.
  2. Model: template database. Gets cloned when a new database is created. Any changes that one would like be applied by default to a new database should be made here
  3. Tempdb: re-created every time SQL Server instance is restarted. Holds intermediate results created internally by SQL Server during query processing and sorting, maintaining row versions, etc. Recreated from the model database. Sizing and configuration of tempdb is critical for SQL Server performance.
  4. Resource [hidden database]: stores executable system objects such as stored system procedures and functions. Allows for very fast and safe upgrades.
  5. MSDB: used by the SQL Server Agent service and other companion services. Used for backups, replication tasks, Service Broker, supports jobs, alerts, log shipping, policies, database mail and recovery of damaged pages.

Database Files

  1. Primary data files: every database must have at least one primary data file that keeps track of all the rest of the files in the database. Has the extension .mdf.
  2. Secondary data files: a database may have zero or more secondary data files. Has the extension .ndf.
  3. Log files: every database has at least one log file that contains information necessary to recover all transactions in a database. Has the extension .ldf.

Creating a Database

  1. New user database files must be at least 3 MB or larger including the transaction log
  2. The default size of the data file is the size of the primary data file of the model database (2 MB) and the default size of the log file is 0.5 MB
  3. If LOG ON is not specified but data files are specified during a create database, the size of the log file is 25% of the sum of the sizes of all the data files.

Expanding or Shrinking a Database

  1. Automatic File Expansion:
  1. The file property FILEGROWTH determines how automatic expansion happens
  2. File property MAXSIZE sets the upper limit on the size
  • Manual File Expansion: use the ALTER DATABASE command with the MODIFY FILE option to change the SIZE property to increase the database file size
  • Fast File Initialization: adds space to the data file without filling the newly added space with zeros. New disk content is overwritten as new data is written to the files. Security is managed through Windows security setting SE_MANAGE_VOLUME_NAME
  • Automatic Shrinkage:
    1. Same as doing DBCC SHRINKDATABASE (dbname, 25). Leave 25 % free space in the database after the shrink
    2. Thread performs autoshrink as often as 30 minutes, very resource intensive
  • Manual Shrinkage: use DBCC SHRINKDATABASE if you want to shrink.
  • I highly recommend not to shrink the database.
  • Filegroups

    1. Can group data files for a database into filegroups for allocation and administration purposes.
    2. Improves performance by controlling the placement of data and indexes into specific filegroups on specific drives or volumes.
    3. Filegroup containing the primary data file is called the primary filegroup, there is only one primary filegroup.
    4. Default filegroup: there is at least one filegroup with the property of DEFAULT, can be changed by DBA.
    5. Use cases when -not- to use filegroups:
    1. DBA might decide to spread out the I/O for a database: easiest way is to create a database file on a RAID device.
    2. DBA might want multiple files, perhaps to create a database that uses more space than is available on a single drive: can be accomplished by doing CREATE DATABASE with a list of files on separate drives
  • Use cases when you want to use filegroups:
    1. DBA might want to have different tables assigned to different drives or to use the table and index partitioning feature in SQL Server.
  • Benefits:
    1. Allows backup of parts of the database.
    2. Table is created on a single filegroup, allows for backup of critical tables by backing up selected filegroups.
    3. Same for restoration. Database can be online as soon as primary filegroup is restored, but only objects on the restored filegroups will be available.

    Given the nature of content, also look at the other topics we have discussed before.

    Concurrency Basics

    Locking Basics

    Transaction Log Basics

    Thanks for reading this far. Feel free to pass your comments too.

    Tags: , , , , , , , ,

    This entry was posted on Monday, November 19th, 2012 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

    15 Responses to “SQL Server: Database Basics”

    1. Law K R H says:

      Appreciate share very good knowledge’s.

    2. selvakumar says:

      hi vinod sir,

      one small doubt in system database. why we need MODEL database ?

      • Vinod Kumar says:

        As the name suggests, MODEL database is used like a template database whenever a CREATE DATABASE command is fired. A copy of this MODEL database is used for all database creation. Hence the MODEL database is required.

        A lot of people would want some standard schema objects available whenever a database is created, this can be achieved by creating them inside the MODEL database and subsequent databases created will automatically inherit these objects into their newly created database.

        Hope this makes it clear.

    3. Rajesh Kasturi says:

      Nice article and helpful for who wants to start learning sql server and most of the developers did not understand the File Group concepts and importance of having FG’s while designing the DB at the beginning and face performance issues when the application goes live.

      Implementing File Group concepts while designing database and table partitioning and is essential step to improve the database performance.

      Nice article Vinod.

    4. saniya says:

      How to create primary data , secondary data and log files in a database?

    5. Devi Prasad says:

      Awesome one! Refreshed basics..

      Would be highly appreciable, if you can point ResourceDB [hidden database] in details here.. Just few lines on comment window.


      • Vinod Kumar says:

        Sure, the fine prints can be updated later. Just for the records.

        ResourceDB: SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

    6. sybanva says:

      Ok Thnx Vinod, I will again check and get back to you in case of doubt.

    7. sybanva says:


      Thanks for confirmation.

      In SQL server, there is Sys schema in every database, and there is one view sys.databases in every database. I am not able to understand why its in each database as it doesn’t contain any particular database information like sys.tables.

      It must be only in master as its related to sysdatabases system table.

      Looking for your kind reply.

      • Vinod Kumar says:

        Read the article again, the sys schema is stored in the Resource Database which is hidden. It is by design. The scope of these system views are local to the context of the DB. These are not created in every database as you mentioned.

        • Abhay Bajpai says:

          Resource database is system and hidden database.
          Its read only and contain system objects.
          It contain metadata information.
          Its complement of master database.
          As it has sys schema and sys chema exist all over databases for every individual instance.

    8. sybanva says:

      As per my understanding the primary, secondary files and log files can have any extension( without extension is also fine).

      .mdf , ndf and .ldf is only for readability(convention) and it is not mandatory for a Database in Any SQL server instance.

      Please let me know if I am wrong as I am very new to SQL server. Thanks,

      • Vinod Kumar says:

        You are correct. From an notation perspective I would recommend this. As you say, the extensions don’t matter :) … When in doubt, just test.

        CREATE DATABASE [test]
        ON PRIMARY
        ( NAME = N’test’, FILENAME = N’C:\temp\test.mdf1′ , SIZE = 4096KB , FILEGROWTH = 1024KB )
        LOG ON
        ( NAME = N’test_log’, FILENAME = N’C:\temp\test_log.ldf2′ , SIZE = 1024KB , FILEGROWTH = 10%)

        This shows that the restriction is not an requirement for name/extension. Thanks again for the note.

    Leave a Reply