Archive for the ‘SQL’ Category

SQL File and FileGroups :
SQL Server databases have three types of files:
• Primary data files
The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf (Microsoft Data Format File).
• Secondary data files
Secondary data files comprise all of the data files other than the primary data file. Some databases may not have any secondary data files, while others have multiple secondary data files. The recommended file name extension for secondary data files is .ndf(NimbleGen Design File).
• Log files
Log files hold all of the log information used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf(Layered Data Format File). Log files are never a part of a filegroup. Log space is managed separately from data space.

SQL Server data and log files can be placed on either FAT or NTFS file systems, but cannot be placed on compressed file systems.
FileGroups are logical containers used to store database files.
There are two types of filegroups:
• Primary
The primary filegroup contains the primary data file and any other files not specifically assigned to another filegroup. All pages for the system tables are allocated in the primary filegroup.
• User-defined or Secondary
User-defined filegroups are any filegroups specified using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

SQL System Databases

Posted: June 11, 2008 in SQL

Following are the SQL Server System Databases :
1. master Database – Contains information about the server overall and all databases created. The information in this database includes: file allocations, disk space usage, system-wide settings, user accounts, and the existence of other databases and other SQL Servers.
2. model Database – This database provides a template for all newly created databases. If you ever need to add a table or stored procedures to all the new databases you create, simply modify the model database.
3. msdb Database – Used by the SQL Server Agent service. Stores information related to SQL Jobs and Alerts.
4. tempdb Database – Is a workspace used for holding temporary information.