Pr.Pg Next Pg


  • SQL Server can support numerous distinct databases.

  • Each of these databases can store data that can be related to data from one or more databases.

  • In addition to the usual tables, columns and rows that a database consists of, a SQL Server Database also consists of at least one primary datafile, and a transaction log file.

  • It can also have secondary datafiles. These files are used only by that database.

  • In addition, a SQL Server database can also store procedures that use Transact-SQL code to perform operations with the data in the database. Furthermore, SQL Server tables do not only contain columns and rows that contain data, but also other objects, such as views, indexes, stored procedures and triggers, that are used to support activities performed with a relational database.

  • A SQL server database is made of at least 2 physical files. One Data file and another is Log file. The data file contains data and log file contains the transaction log.


Data files

  • SQL Server data files include both primary and secondary data files.

  • By default, the primary data file can generally be identified by the ” .MDF” filename extension and stores data in system table, user tables and indexes. It also contains the startup information for the database and the system tables that track objects in the database, including file location information about the database’s secondary data files and transaction log files.

  • The secondary data file can generally be identified by the “.NDF” filename extension and are used primarily when a database spans more than one disk drive. A database can have upto 32,766 .NDF files. Secondary data files are Optional.


Transaction Log Files

  • Each SQL Server database contains at least one transaction log file but can have multiple transaction log files spread across a number of hard disk drives that SQL Server treats as a continuous transaction log.

  • Each transaction log file is a separate operating system file and is used by the specific database.

  • By default, the transaction log file can generally be identified by the ".ldf" filename extension

  • Log files hold all the log information that is used to recover the database.


Data file Names

  • Every data file has a logical filename, which is used in Transact-SQL statements, and a physical filename which is used by the operating system.

  • The logical filename must however be unique to its database and must also conform to the SQL Server identifier rules.

The SQL Server identifier rules are as follows:

1)     The first character of the identifier must be an uppercase or lowercase letter; the underscore; the “at” sign (@); or a hash sign (#).

2)     Subsequent characters of the identifier can be upper or lowercase letters; decimal numbers; the “at” sign, the dollar sign ($), the hash sign, or the underscore.

3)     The identifier cannot be a Transact-SQL reserved word; and

4)     The identifier cannot contain embedded spaces or special characters.



When you install SQL Server 2012, it install following System Databases

1)     Master: used for System Configuration database

2)     Model: template for new user database.

3)     Msdb: is configuration database for the SQL Server Agent services and other system services.

4)     Tempdb: this database is used by the database engine to store temporary data such as work tables. This database is dropped and recreated every time SQL Server restart. Never store your user data in to this database.

5)     Resource (This is hidden system configuration database.)


tempdb Database

  • Because the tempdb database is much more heavily used than in previous versions, special care needs to be taken in how you design the storage underneath tempdb.

  • In addition to temporary objects, SQL Server uses tempdb for worktables used in grouping/sorting operations, worktables to support cursors, the version store supporting snapshot isolation level, and overflow for table variables.

  • You can also cause index build operations to use space in tempdb.

  • Due to the potential for heavy write activity, you should move tempdb to a set of disks separated from your databases and any backup files. To spread out the disk I/O, you might consider adding additional files to tempdb.


Data file Properties

  • Other properties attached to data files include the file ID, initial file size, file growth increment, and maximum file size.

  • Automatically grow file (auto-grow) :- As the database begins to hold more data. The file size must grow. If auto-grow is not enabled, the DBA will have to manually adjust the size. If auto-grow is enabled, SQL server will automatically adjust the size according to the following growth properties.

  • File Growth in Megabytes – this is good option for larger data file.

  • File Growth in Percentage – this is good option for smaller database.



Pr.Pg border                                              Next Pg