SQL DBA Interview Questions and Answers-Database Architecture and Database Properties
This article comprises of SQL Server Questions and answers series on Database Architecture and various Database Properties. Intentionally I skipped Database Recovery Models, Backups, Restore, Data File and Transaction Log Architecture Questions from this series because I will cover those topics in a complete separate series on Database Backup, restores and File Architectures.
1) How many types of files are there in a SQL Server database?
SQL Server databases have three types of files:
- Primary data files
- Secondary data files
- Transaction Log files
2) Explain each type of database 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.
Secondary data files
Secondary data files make up all the data files, other than the primary data file. Some databases may not have any secondary data files, while others have several secondary data files. The recommended file name extension for secondary data files is .ndf.
Transaction Log file
This file holds all the log information that is 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.
3) What is the major difference between Primary data files and secondary data files?
Primary data file contains system objects where as secondary data files contains all user defined Database objects if these are not part of the Primary File group.
One of the important difference between Primary and Secondary data files is BOOT PAGE. Page Number 9 is the boot page Page type 13). Boot page is available as 9th page only in the primary data file.
4) How many maximum files can be added to a database?
32,767
5) What are file groups and Type of File Groups?
Database File groups: Database objects and files can be grouped together in file groups for allocation and administration purposes. There are two types of file groups:
Primary: The primary file group contains the primary data file and any other files not specifically assigned to another file group. All pages for the system tables are allocated in the primary file group.
User-defined: User-defined file groups are any file groups that are specified by using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.
6) What is the default File Group in a database?
Primary File group
7) Is it possible to change the Default file group from Primary to some other user defined file group? What is the benefit of this?
Yes it is possible to change the Default File group to user defined File group. All the newly created objects will be created in User defined File group be default.
8) How many Maximum File groups can be added in a database?
32,767
9) Is it possible to add Transaction Log file in a file group?
Log files are never part of a filegroup. Log space is managed separately from data space.
10) What is the use of having multiple File Groups?
Below are the major benefits which can be achieved using multiple data files and placing these files in separate file groups on separate disk drives.
- Disk I\O Performance
- Easy Management and Archiving of the data
- Benefit of doing File Group level Backups and restores
- Usage of File Groups in Portioning of the tables
11) Is there any benefit to add multiple log files? Yes/No, Why?
No, there is no benefit of adding multiple log files in a database as the write operations in a Transaction log files are always serial.
12) Will the below script work if yes, how?
Create database Test;
Yes, this script will work because rest of the parameters will be taken from model database and Files will be located to the respective folders which are set at the SQL Server instance level.
13) What is Database Growth and what settings are available in a database?
Each database file that is associated with your database has an auto-growth setting. There are three different settings you can use to identify how your database files will grow. They can grow by a specific size, a percentage of the current size, or not grow at all. Additionally you can set your files to unrestricted growth, which means they will keep growing as they need more space or you run out of disk space. Or you can restrict the growth of a database file to grow no larger than a specified size. Each one of these different auto-grow setting have defaults, or you can set them for each database file.
14) What are the recommended settings for transaction Log File for file growth?
If you are required to set the setting for Auto growth of Transaction log file, it should always be in a specific size instead of percentage.
15) What is a compatibility level of a database?
Compatibility level sets certain database behaviors to be compatible with the specified version of SQL Server. The default compatibility level is 110. Databases created in SQL Server 2012 are set to this level unless the model database has a lower compatibility level.
16) How to change the Compatibility level of a database?
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 90 | 100 | 110 }
‘90 = SQL Server 2005
100 = SQL Server 2008 and SQL Server 2008 R2
110 = SQL Server 2012
17) What’s the difference between database version and database compatibility level?
Database version
The database version is a number stamped in the boot page of a database that indicates the SQL Server version of the most recent SQL Server instance the database was attached to.
USE master;
GO
SELECT DatabaseProperty ('dbccpagetest', 'version');
GO
Database compatibility level
The database compatibility level determines how certain database behaviors work. For instance, in 90 compatibility, you need to use the OUTER JOIN syntax to do an outer join, whereas in earlier compatibility levels, you can use ‘*=’ and ‘=*’
SELECT name AS 'DB Name', compatibility_level AS 'Compatibility Level' FROM master.sys.databases; GO
18) What is a Page Verify option in a database?
When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity.
19) What are the different Database states in SQL server instance?
| State | Definition |
| ONLINE | Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed. |
| OFFLINE | Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed. |
| RESTORING | One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable. |
| RECOVERING | Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable. |
| RECOVERY PENDING | SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed. |
| SUSPECT | At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem. |
| EMERGENCY | User has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state. |
20) How many databases can be created in SQL server instance?
32,767
21) What is auto close option?
AUTO CLOSE option:
When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.
When set to OFF, the database remains open after the last user exits.
22) What is auto shrink option?
AUTO SHRINK option:
When set to ON, the database files are candidates for periodic shrinking. Both data file and log files can be shrunk automatically by SQL Server. AUTO_SHRINK reduces the size of the transaction log only if the database is set to SIMPLE recovery model or if the log is backed up.
When set to OFF, database files are not automatically shrunk during periodic checks for unused space.
23) What is page?
Page is the smallest unit of storage in SQL Server database, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page
24) What is an extent?
An extent is a collection of eight physically contiguous pages.
25) Types of extent?
Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.
26) What is the difference between single user and restricted user and restricted user option?
This option controls who and how many users can connect to a database.
When SINGLE_USER is specified, one user at a time is allowed to connect to the database. All other user connections are broken.
When RESTRICTED_USER is specified, only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles are allowed to connect to the database, but it does not limit their number.
When MULTI_USER is specified, all users that have the appropriate permissions to connect to the database are allowed.
27) What is a logical File and physical File name?
Each data and transaction log file in a SQL Server database has two names:
logical_file_name
The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements. The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.
os_file_name
The os_file_name is the name of the physical file including the directory path. It must follow the rules for the operating system file names.
28) What is ROW_OVERFLOW_DATA? How does it work?
ROW_OVERFLOW_DATA : Assume that a table is created with record size 12000 bytes having 4 varchar data types of size 4000 bytes. Whenever user inserts a record with size greater than 8000 (page size is 8K), then the excess data is moved to ROW_OVERFLOW_DATA pages. In simple terms, ROW_OVERFLOW_DATA pages will come in to picture only when the row size exceed page maximum limit.
29) How can we check the allocation unit of objects?
DMV sys.system_internals_allocation_units
30) What is trustworthy property of a database?
Trustworthy property :When ON, database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database.
When OFF is specified, in an impersonation context cannot access resources outside the database.
TRUSTWORTHY is set to OFF whenever the database is attached.
31) What is Instant file initialization?
Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros when you perform one of the following operations:
- Create a database.
- Add files, log or data, to an existing database.
- Increase the size of an existing file (including autogrow operations).
- Restore a database or file group.
- File initialization causes these operations to take longer. However, when data is written to the files for the first time, the operating system does not have to fill the files with zeros.
Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.
References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.
http://www.sqlskills.com/blogs/glenn/setting-your-page-verify-database-option-to-checksum/
https://technet.microsoft.com/en-us/library/ms190969(v=sql.105).asp
