Important questions :- SQL Server Services

SQL Server Services

1) List out various services which are important for SQL Server and its components functionality?

·         SQL Server Service

·         SQL Server Agent service

·         SQL Server Analysis Service

·         SQL Server Browser service

·         SQL Server Integration Service

·         SQL Server Reporting Service

·         SQL Server VSS Writer service

·         Distributed Transaction Coordinator

·         SQL Server Active Directory Helper

·         SQL Full-Text Filter Daemon Launcher

·         SQL Server Distributed Replay Client service

·         SQL Server Distributed Replay Client service

2) What is SQL Server service and its importance?

SQL Server service is core of SQL Server instance. It runs the Database Engine and executes the client requests related to data processing.  If this service is not running, no users can connect to the any of the database, hence users will not be able to fetch, insert, update or delete the data.

3) What is SQL Server Agent service and its importance?

SQL Server Agent is the primary scheduling engine in SQL Server. This is used to execute scheduled administrative tasks like SSIS Packages, T-SQL Scripts, Batch Files and Subscriptions etc. which are referred to as Jobs. It uses msdb database to store the configuration, processing, and metadata information. Apart from SQL Server Agent related information, msdb database also stores similar information related to Backup, Restore, Log Shipping, SSIS Packages etc.

4) What is SQL Server Analysis service and its importance?

Microsoft SQL Server Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting you design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services lets you design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standard data mining algorithms.

5) What is SQL Server Integration service and its importance?

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.

6) What is SQL Server Browser?

This service acts as a listener for the incoming requests for Microsoft SQL Server resources. It provides information about the list of installed SQL Server instances on the computer to the client computers/applications. It helps in browsing the list of servers, locating and connecting to the correct server.

This listener service responds to client requests with the names of the installed instances, and the ports or named pipes used by the instance.

7) What is SQL Server Reporting Services?

This service is primarily used by SQL Server Reporting Services (SSRS) for browsing and viewing the reports on Reports Server, through Report Server or Report Manager interface. It is used to manage the shared data sources, reports, shared data sets, report parts, folder, etc. hosted on the Report Server. Reporting services are managed using the Reporting Services Configuration Manager.

8) What is SQL Server VSS Writer?

The SQL Writer Service provides added functionality for backup and restore of SQL Server through the Volume Shadow Copy Service framework. When running, Database Engine locks and has exclusive access to the data files. When the SQL Writer Service is not running, backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup.

Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running. It must be running when the Volume Shadow Copy Service (VSS) application requests a backup or restore. To configure the service, use the Microsoft Windows Services applet. The SQL Writer Service installs on all operating systems.

9) Which types of backups are supported by SQL Write Service?

SQL Writer supports:           

·         Full database backup and restore including full-text catalogs

·         Differential backup and restore

·         Restore with move

·         Copy-only backup

·         Auto-recovery of database snapshot

10) Which types of backups are not supported by SQL Write Service?

SQL Writer does not support:

·         Log backups

·         File and filegroup backup

·         Page restore

11) What is Full-Text Search service?

This service is used by the full-text search feature of SQL Server. It helps in starting the filter daemon host process, which manages the full-text indexing, querying, search filtering and word processing as part of the full-text search feature.

12) What is SQL Server Active Directory Helper?

This service enables the integration with the Active Directory. Irrespective of number of instances of SQL Servers installed on a computer, there is always only one instance of SQL Server Active Directory Helper service. This service is automatically started by SQL Server when required and is stopped once the operation is completed. This service is required whenever an SQL Server object needs to be created in the Active Directory to register an instance of SQL Server.

13) What is Distributed Transaction Coordinator?

This service coordinates distributed transactions between two or more database servers. Client applications use this service to work with data from multiple sources in one transaction. There is always only one instance of MSDTC service running on a computer irrespective of how many SQL server instances are installed. This service should be running on each of the servers which handle distributed transactions. This service is not a part of SQL Server installation. This service is installed with Windows OS installation.

14) How to check how many SQL Server instances are installed on a Window Server?

There are multiple ways through which we can check the No. of SQL Server instances which are running on a server like:

·         Check the SQL services for different Instances

·         SQL Server Configuration Manager Start- all programs – Microsoft SQL Server 2008 R2 -> configuration tools –> Microsoft SQL Server configuration Manager

·         List out SQL Services from Net Start command

·         Using Powershell commands

·         Readging the Registry Keys, Regedit-> HKEY_LOCAL_MACHINE–>SOFTWARE ->Microsoft ->Microsoft SQL Server –> InstalledInstance

15) What are the different ways to start and Stop SQL Server services?

There are different ways through which we can start or stop SQL Server services.

·         Go to Services –> Look for SQL server service related to  the Instance

Named Instance: SQL Server(Instance Name)

Default Instance: SQL Server(MSSQLServer)

·         Right Click on the SQL Server instance in management studio and click on restart

·         Go to SQL Server Configuration Mananger (SQLServermanager10.msc) and right click on the services and click restart.

·         Net stop command

·         use “Net START” command to list all the running services

·         Use “Net STOP MSSQL$Instancename”  to stop the SQL Service for a particular instance

16) List out the Service Display name, Service Name and Executable for SQL Serve related services?

Common Name	Service Display Name	Service Name	ExecutableName
Distributed Transaction Coordinator (DTC)	Distributed Transaction Coordinator	MSDTC	msdtc.exe
SQL Server	SQL Server (MSSQLSERVER)	MSSQLSERVER	sqlservr.exe
	SQL Server (NamedInstance)	MSSQL$NamedInstance
SQL Server Active Directory Helper	SQL Active Directory Helper Service	MSSQLServerADHelper100	SQLADHLP.EXE
SQL Server Agent	SQL Server Agent (MSSQLSERVER)	SQLSERVERAGENT	SQLAGENT.EXE
	SQL Server Agent (NamedInstance)	SQLAgent$NamedInstance
SQL Server Analysis Services	SQL Server Analysis Services (MSSQLSERVER)	MSSQLServerOLAPService	msmdsrv.exe
	SQL Server Analysis Services (NamedInstance)	MSOLAP$NamedInstance
SQL Server Browser	SQL Server Browser	SQLBrowser	sqlbrowser.exe
SQL Server Full Text Search	SQL Full-text Filter Daemon Launcher (MSSQLSERVER)	MSSQLFDLauncher	fdlauncher.exe
	SQL Full-text Filter Daemon Launcher (NamedInstance)	MSSQLFDLauncher$NamedInstance
SQL Server Integration Services	SQL Server Integration Services 10.0	MsDtsServer100	MsDtsSrvr.exe
SQL Server Reporting Services	SQL Server Reporting Services (MSSQLSERVER)	ReportServer	ReportingServicesService.exe
	SQL Server Reporting Services (NamedInstance)	ReportServer$NamedInstance
SQL Server VSS Writer	SQL Server VSS Writer	SQLWriter

17) What are the default parameters of SQL Server service start up process and from where these parameters can be changed?

master database data and log file and error log files are the default parameters which are passed to SQL Server service.

·         -dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf;

·         -eC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG;

·         -lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

We can add trace flags and other parameters to the startup process from SQL Server Configuration manager.

18) How to start the SQL Server with minimal configuration?

If there are any configuration problems that prevent the server from starting, you can start an instance of Microsoft SQL Server by using the minimal configuration startup option. This is the startup option -f.  Starting an instance of SQL Server with minimal configuration automatically puts the server in single-user mode.

19) How to start SQL Server with single user model?

Under certain circumstances, we may have to start an instance of SQL Server in single-user mode by using the startup option -m. For example, you may want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.

20) What are trace flags and how can we apply trace flags on a SQL Server instance?

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behaviour.

Ex: Trace flag 3205 is set when an instance of SQL Server starts, hardware compression for tape drivers is disabled. Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems.

21) How the trace flags are enabled?

Trace flags are enabled at different levels.

·         Global

·         Session

Instance level

We can use the -T option in the startup configuration for the SQL Server Service to enable trace at instance level.

Session Level

We can use the DBCC TRACEON and DBCC TRACEOFF commands to enable it on a session level.

22) How do I know what Trace Flags are turned on at the moment?

We can use the DBCC TRACESTATUS command

23) Name some of the Important Trace flags and their functionality?

Trace Flag: 1204

This trace flag lets SQL Server to log detailed deadlock information to SQL Server Error Log in text format. In SQL Server 2008, this trace flag is only available at the Global Level (i.e. applies to the SQL Server instance). In my experience, it is worth turning this trace flag on, only for debugging purposes.

Trace Flag: 1222

Similar to trace flag 1204, this trace flag lets SQL Server to log detailed deadlock information to SQL Server Error Log in XML format.

Trace Flag: 3226

In an environment where database backup is frequently performed, it is a good idea to turn trace flag 3226 on, to suppress an entry to the SQL Server Error Log for each backup.  This is beneficial as the bigger the log, the harder it is to find other messages.  However, you will need to ensure that none of your scripts or systems rely on the backup entry detail on the SQL Server Error Log.

24) What are the mandatory databases to bring SQL Services up?

master, model, resource db, tempdb location.

26) What is Protocol is used by SQL Server Browser service?

SQL Server Browser service uses UDP protocol.

27) Which Port no. is used by SQL Server Browser service ?  1434

28) What will happen if SQL Server Browser service is stopped?

If the SQL Server Browser service is not running, the following connections do not work:

·         If we have just one instance installed on machine and it is running on default port 1433, then status of SQL Server Browser service does not make any difference in our connection parameters.

·         If there are more than one instances running on the same machine, in that case either we have to start SQL Server Browser service or provide the port number along with IP (or server name) and instance name, to access any other instance than default.

·         If SQL Server Browser service is stopped and IP along with port number is not provided then connection will be refused.

·         If SQL Server instance is configured using dynamic ports then browser service is required to connect to correct port number.

·         Also our named instances will not be published in the list of SQL Server instances on the network (which could be a good thing)

29) What is the high Level SQL Server start up process?

·         The service is authenticated by verifying the credentials provided in the logon account and the service is started.

·         PID is allocated at windows level

·         Authentication mode details are verified i.e either MIXED or WINDOWS

·         Information of the startup parameters is captured i.e mdf location of master database, SQL Server error log location and ldf file location

·         Some memory and CPU settings done at windows level, this is an informational message only

·         Starts the master database

·         model is the next database to start

·         set the port related information

·         Tempdb is recreated each time when we restart sql server

·         Start msdb and other user databases based on dbid

30) What is Distributed Replay?

Distributed Replay is a new functionality of Microsoft SQL Server 2012. It helps you assess the impact of future upgrades (SQL Server, hardware, OS) or SQL Server tunings by replaying a trace captured from a productive SQL Server environment to a new SQL Server test environment.

Important Questions:- Data File and Transaction Log File Architecture

Data File and Transaction Log File Architecture

1) Explain the SQL Server Database Data File structure?

SQL server divides the data file into 8 KB pages and page is the smallest unit of any IO operation. SQL server refer the first page in the data file as page number 0.In all the data file first 9  pages (till page number 8) are in same order as shown below. In the primary data file, the 10th page (Page number 9) will be the boot page which stores the metadata about the database.

2) What is a Data Page?

Data rows with all data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data.

3) What is an Index Page?

Contains information related to Indexes. This holds index records in the upper levels of a clustered index and all levels of non-clustered indexes.

4) What is a Page Header?

Page number 0- the file header (page type 15). It holds the file header information. There will be only one header page for each data file and that reside in the 0th position. The header page store the file level information like file size, minimum size, max size and file growth.

5) What is PFS?

PFS page is the second page (Page Number 1) in the data file followed by file header (Page Number 0). PFS pages are used to track page level allocation. PFS page repeat after every 8088 pages

6) What is a GAM Page?

GAM pages records what extents have been allocated for any use. GAM has bit for every extent. If the bit is 1, the corresponding extent is free, if the bit is 0, the corresponding extent is in use as uniform or mixed extent.

7) How many GAM pages will be there in a 7GB data file?

A GAM page can hold information of around 64000 extents. That is, a GAM page can hold information of (64000X8X8)/1024 = 4000 MB approximately. In short, a data file of size 7 GB will have two GAM pages.

8) What is SGAM page?

SGAM (Shared Global Allocation Map) page (Page type 8). SGAM pages record what extents are currently being used as mixed extent and also have at least one unused page. SGAM has bit for every extent. If the bit is 1, the corresponding extent is used as a mixed extent and has at least one page free to allocate. If the bit is 0, the extent is either not used as a mixed extent or it is mixed extent and with all its pages being used.

9) How many SGAM pages will be there in a 7GB data file?

A SGAM page can hold information of 64000 extents. That is, a SGAM page can hold information of (64000X8X8)/1024 = 4000 MB. In short, a data file of size 7 GB will have two SGAM page.

10) What is a DCM and its significance?

SQL Server uses DCM pages to track extent modified after the last full backup operation. DCM pages track the extents modified after the full backup. DCM has a bit for every extent it tracks. If the bit is 1, the corresponding extent is modified after the last full backup, if the bit is 0, there is no change in the corresponding extent after the last full backup

11) How SQL Server engine will decide which extents need to be added into a differential backup?

A differential backup process scans through DCP page to identify the extents which are modified after the last full backup and add those extents in the Differential backup.

12) What is a BCM and its significance?

This tracks the extents that have been modified by bulk logged operations since the last BACKUP LOG statement. If the bit for an extent is 1, the extent has been modified by a bulk logged operation after the last BACKUP LOG statement. If the bit is 0, the extent has not been modified by bulk logged operations.

Although BCM pages appear in all databases, they are only relevant when the database is using the bulk-logged recovery model. In this recovery model, when a BACKUP LOG is performed, the backup process scans the BCMs for extents that have been modified. It then includes those extents in the log backup.

13) What is Boot Page?

There is one special data page that exists only once per database. It is the database boot page. The database boot page is always stored in page 9 of file 1, the first file in the primary file group.

The database boot page contains a broad variety of data about the database itself. Here you can find the internal database version, the compatibility level, the database name and the date of the last log backup.

14) How many types of Allocation units are there?

There are three types of allocation units.

IN_ROW_DATA (default): If a table is relatively simple in desin (meaning record size is less than 8000 and no LOB data types are present), all records are stored in a pages refered as IN_ROW_DATA pages.

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.

LOB_DATA: LOB data like text not stored along with data pages. LOB data is stored in pecial pages called LOB_DATA pages. 16 byte pointer in data page will be used to refer LOB_DATA page.

15) What is an IAM page?

IAM stands for Index Allocation Map: To catalog all the pages that belong to a single allocation unit, SQL Server utilizes a special type of pages, the Index Allocation Map or IAM pages. Each allocation unit has at least one IAM Page; depending on the size of the table there might be many. The page type of an IAM page is 10.

16) What is a ROW OFFSET and its importance?

Page header stored system information about the page. After the page header data row is stored serially. Row offset information is stored at the end of the page, after the data rows. Every data row has a row offset. Row offset stores information about how far the row is from the start of the page.

17) What are LOB pages?

SQL Server stores all data in 8192-byte sized blocks called pages. Several types of pages are in use within a typical database. One particularly interesting group is formed by the type-3 pages or Large Object Pages.

LOB_DATA Allocation Units

Most data types in SQL Server take up no more than 8000 bytes of storage. However, there are a few data types, which allow for larger pieces of information to be stored. Examples include the VARCHAR(MAX), VARBINARY(MAX) or XML data types.

Normal data pages that belong to a table are grouped in IN_ROW_DATA allocation units. However, if a value that is larger than 8000 bytes needs to be stored, SQL Server does not attempt to store it in those data pages anymore. It does not even store those values in the same allocation unit. Instead, Large Object data or LOB data is stored in special LOB_DATA allocation units.

18) What is a PAGE SPLIT?

Page splits are performed when there is no space in a data page to accommodate more inserts or updates. In a page spilt, SQL server removes some data from the current data page and moves it to another data page. This is done in order to make space for the new rows.

19) How Page Splits impacts the database performance?

Too many page splits can decrease the performance of the SQL server because of the large number of I/O operations.

Following remedies can be taken to avoid too many page splits:-

·         Rebuild indexes frequently to empty the fill factor space for more data.

·         Increased the Fill factor value after observing the page split behavior

20) How can we monitor or detect Page Splits?

Number of page splits can be observed by using the Performance Monitor and watch the SQLServer:Access Methods:Page Splits/sec counter

21) What is a Fill Factor?

The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

22) What is a Logical Read?

Logical read indicates total number of data pages that are found in the data cache and read from Data cache without reading anything from Disk.

23) What is a Physical read?

Physical read indicates total number of data pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read. And usually it happens for first query request. And for subsequent same query request the number will be substantially decreased because the data pages have been in data cache.

24) How can we view the content of a page?

To see the row data stored in that page , we have to use the DBCC PAGE command. The syntax of DBCC PAGE

dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]);

Print opt:

·         0 – print just the page header

·         1 – page header plus per-row hex dumps and a dump of the page slot array

·         2 – page header plus whole page hex dump

·         3 – page header plus detailed per-row interpretation

DBCC TRACEON(3604)  GO

 DBCC page('test',1,114,3)

 25) What is a Transaction log file?

A transaction log is a physical file in which SQL server stores the details of all transactions and data modifications performed on the database.  In the event of disaster, that causes SQL server to shutdown unexpectedly (Power failure/hardware failure), the transaction log is used to bring the database in a consistent state while restarting the server. On restarting the server, database goes through the recovery process.

During this recovery process, the transaction log is used to make sure that all committed transactions are written to respective data file (rolled forward) and rollback the uncommitted transaction.

Logically transaction log is a set of log records. Each record is identified by a log sequence number (LSN). The new log records are always written at the logical end of log file with a LSN which is greater than the previous one

26) What are Virtual Log files? How these are created?

SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files cannot be configured or set by administrators.

27) How VLFs are created SQL Server in a Transaction log file?

SQL server decides the size and number of VLF in a log file based on the size of the log file growth as given below.

Growth upto 64 MB        = 4 VLF

From 64 MB to 1 GB       = 8 VLF

Larger than 1 GB             = 16 VLF

28) What is the way to check Virtual Log file details?   DBCC loginfo(‘mydb’)

29) What is Log Sequence No?

Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

30) What is a Log Truncation?

Log truncation is essential to keep the log from filling. Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the physical transaction log. If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files. However, before the log can be truncated, a checkpoint operation must occur. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk. When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. Thereafter, the inactive portion can be freed by log truncation.

31) How Does Log Truncation occurs?

Log truncation occurs automatically after the following events, except when delayed for some reason:

·         Under the simple recovery model, after a checkpoint.

·         Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.

Important Questions:- Database Architecture and Database Properties

4.Database Architecture and Database Properties

1) How many types of files are there in a SQL Server database? 3 Types

·         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 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 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.

1.       Disk I\O Performance

2.       Easy Management and Archiving of the data

3.       Benefit of doing File Group level Backups and restores

4.       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 behaviours 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 | 120|130 }

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.

SELECT DatabaseProperty ('dbccpagetest', 'version');

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;

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?

No	State	Definition
1	ONLINE	Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed.
2	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.
3	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.
4	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.
5	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.
6	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.
7	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?  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.