SQL Server Interview Questions:

1.
2.
3.
4.
5.
6.
7.
8.

How to identify what hot fixes and service packs are installed.
How do identify and fix page corruption
What are trace flags, how many of them is in sql server
Emergency mode and suspect mode, how do you identify and fix it.
SQL Server is consuming 90 percent of memory how do you troubleshoot it.
Tempdb is full, what are steps you to take to prevent it
Tempdb is full, how do you freespace without shrinking it
what is missing indexes and unusable index, how to identify them and
troubleshoot it
9. Difference between index online and reorganize index
[Link] affinity and I/O affinity
[Link] is heart beat and keep alive and is alive
[Link] is the boot page in sql server
[Link] architecture
[Link] is active - active and active - passive in cluster
[Link] and filegroup backups
[Link] Server Architecture
[Link] Cleanup or Ghost Rows
[Link] are missing indexes and unused indexes
[Link] is TUF file and what happens if it gets lost
[Link] for failover in logshipping
[Link] u use same backup and restore path after failovering in logshipping
[Link] log backup and point in time recovery
[Link] for point in recovery if u database is crashed on wednesday afternoon
how u perform it.
[Link] to take log backup if the log is corrupted for a database or taking a log
backup for a crashed database. (Tail log backup and continue after error)
[Link] do you verify whether Backup succeed or not. U need to use RESTORE
VERIFY ..
[Link] do you move tempdb files to another location.
[Link] between Simple and Full recovery model
28.I have 32 bit system, and have 32 GB RAM, how much memory does it take,
what options do we required to be enabled to have max memory
[Link] is the use of min memory setting in sql server
[Link] between cluster index and non clustered index
[Link] is update statistics, reoraganize index and rebuild index, and what is
view to view it.
[Link] to patch sql server cluster instance
[Link] is Service packs and Hot fix.
[Link] do you get orphan users in sql server and what is the command to find
it.
[Link] is the way to execute T SQL query across multiple instances at same
time -- registered servers.
[Link] can locate sql server error log if the instance has crashed.
In SQL Server Configuration Manager, expand Services, and then click SQL
Server

In the details pane, right-click the name of the instance you want to start
automatically, and then click Properties
Click the Advanced tab, and Verify Startup Parameters.
-e The fully qualified path for the error log file
Other parameters
-d The fully qualified path master database file
-l The fully qualified path master database log file
Now, assuming that Enterprise Manager is not working. Heres how you still determine the
SQL Error Log location.
Using SQL Server Registry Keys
Click Start, click Run, type Regedit, and then click OK.
Locate the following registry subkey:

For - Default Instance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Para
meters

For - Named Instance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft SQL
Server\<Instance_Name>\MSSQLServer\Parameters

Go Try This !

[Link]
[Link]
[Link] can verify whether the backup is consistent or not. -- restore verify
[Link] you need to have differential backup when you have transaction log
backup is running for every 5 mins.
[Link] is filegroup backup and copy only backup.
[Link] is scheduled job and scheduled task.
[Link] do you monitor sql server performance
[Link] out the backup types in sql server
[Link] is dbcc and list out few of them
[Link] is logshipping and what are steps for configuring logshipping
[Link] is service account in sql server
[Link] out the steps involved in configuring sql server failover cluster.
[Link] many IPs do you use in configuring clustering and what are they what
are its use

[Link] of jobs created in sql server logshipping, and on which instance they
are created.
[Link] out the steps for configuring log shipping
[Link] between log shipping and database mirrroing
[Link] is push subscription and pull subscription.
[Link] many mirror database we can have in log shipping and how many
secondary databases do we have in secondary database.
[Link] can u change the port number of sql server.
[Link] can we establish connection to oracle from sql server without using
linked server.
[Link] between database recovery models
[Link] are the typical situations have faced in your environment explain few of
them.
[Link] steps do you follow to recover a database when the user database
crashes.
[Link] are reasons when you are not able to login/connect to sql server
instance and your are able to do remote desktop for that server/instance.
[Link] i have to provide if user want to access a particular database.
[Link] the situations that u have faced in client environment
[Link] you perform anything on mirror database.
[Link] many named instance can you have on a Server.
63.I have a scenario that database has crashed how do you recover it, explain
me the steps.
[Link] for moving system databases.
[Link] are the standards do you follow while installing sql server.
66.I have the one instance, I was able to login through RDP but not able to login
through SSMS what would be the possible reasons.
[Link] between inplace upgrade and side by side upgrade.
[Link] many types of upgrade modes do you have and what are they.
[Link] do you see in configuration manager.
[Link] between differential backup and transactional backup
[Link] we upgrade SQL Server 2005 to SQL Server 2012 directly.
[Link] we restore SQL Server 2005 backup onto SQL Server 2012 instance.
[Link] do you troubleshoot if tempdb is full.
[Link] is default port for sql server and how do you change the default port
number.
[Link]
[Link]
[Link]
[Link] is collation and what is its use.
[Link] is affinity I/O and affinity mask -- in doubt but question exists.
[Link] is dbcc checkdb and what it shows up.
CHECKDB found 0 allocation errors and 0 consistency errors in database
[Link] is database mirroring and operating modes.
[Link] is endpoint in database mirroring.
An endpoint is a SQL Server object that enables SQL Server to communicate
over the network.

[Link] counters in sql server

[Link] taken when SQL Server not connecting remotely?
Enable remote connections on the instance of SQL Server that you want to
connect to from a remote computer.
Turn on the SQL Server Browser service.
Configure the firewall to allow network traffic that is related to SQL Server
and to the SQL Server Browser service
What is difference between DELETE and TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with
a WHERE clause. Truncate will actually remove all the rows from a table and there will be no
data in the table after we run the truncate command.
1. TRUNCATE:
1. TRUNCATE is faster and uses fewer system and transaction log resources than
DELETE.
2. TRUNCATE removes the data by deallocating the data pages used to store the tables
data, and only the page deallocations are recorded in the transaction log.
3. TRUNCATE removes all rows from a table, but the table structure, its columns,
constraints, indexes and so on, remains. The counter used by an identity for new rows is
reset to the seed for the column.
4. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY
constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
5. TRUNCATE cannot be rolled back.
6. TRUNCATE is DDL Command.
7. TRUNCATE Resets identity of the table
2. DELETE:
1. DELETE removes rows one at a time and records an entry in the transaction log for each
deleted row.
2. If you want to retain the identity counter, use DELETE instead. If you want to remove
table definition and its data, use the DROP TABLE statement.
3. DELETE Can be used with or without a WHERE clause
4. DELETE Activates Triggers.
5. DELETE can be rolled back.
6. DELETE is DML Command.

7. DELETE does not reset identity of the table.

Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if
the current session is not closed. If TRUNCATE is written in Query Editor surrounded by
TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled
back.
What is the difference between a Local and a Global temporary table?

82. A local temporary table exists only for the duration of a connection or, if defined inside a
compound statement, for the duration of the compound statement.
83. A global temporary table remains in the database permanently, but the rows exist only
within a given connection. When connection is closed, the data in the global temporary table
disappears. However, the table definition remains with the database for access when
database is opened next time.

[Link] do observer pending failover and what are the reasons for it.
[Link] database is corrupted what steps do you follow to resolve it.
[Link] is database snapshot and how it differs from the user database and do
they have same size as of user database when snapshot is created before
and after.
[Link] differential backup backed up the transaction log records like full
database. Yes differential backup will have transaction log records for
recovery purpose.
[Link] is uncommitted isolation level.
89.I have a situation I need to grant the view definition and data reader privs for
5 users on 3 tables what steps do u take care.
[Link] have scheduled maintenance activity, where we need to failover the first
node to second node, but for some reason it is not allowing to failover. What
are the possible reasons and where do you check for the logs.
91.I have 3 node cluster and 4 instances are on it how do you look at it what are
opinions of placing it.
We can have two instances each on two nodes, which can be thought as
active - active.
the third node will act as passive node.
[Link] have two drives, in one you have data file and other you have log files,
and we have a situation in where transaction log filled up the space on the
drive and users are complaining that it is affecting business, so what steps do
u take to resolve the issue.
[Link]
also check mssqltips site.

93. Is Database Mirroring Causing The Transaction Log To Fill Up

Every insert, update, and delete operation that occurs on the principal database is sent to the mirror
database through active transaction log records. The mirror server applies these log records in
sequence as quickly as possible. There are two operating modes that dictate how SQL Server
manages the way the principal and mirrored databases handle the transactions.
94. The first operating mode is high-performance (asynchronous) mode, where there is just a principal
server and a mirror server. In this mode, performance is priority at the risk of a potential loss of data
and high availability. Whats important to note here is that in this mode once the principal server
sends the log for a transaction to the mirror, it does not wait for a confirmation from the mirror
acknowledging the transacation. In this case, the principal will just keep sending log data to the mirror
regardless of the work load.
95. The second operating mode is High Safety failover (synchronous) mode. In this mode, all committed
transactions are guaranteed to be written to disk on the mirror server. The principal database will send
the transactions over to the mirror and wait to commit those transactions until it gets a verification from
the mirror server. If for some reason, the link between the two databases is affected, the transaction
log will continue to grow on the principal database until it receives acknowledgement from the mirrored
database. As a result the transaction log will grow and not re-use space.

96. What is the endpoint in Mirroring?

Endpoint: An endpoint is a SQL Server object that enables SQL Server to
communicate over the network. It encapsulates a transport protocol and a port
number.
97. Is High Safety mirroring is recommended on WAN network? If not then why?

We make no recommendations about whether a wide-area network (WAN) is reliable

enough for database mirroring in high-safety mode. If you decide to use high-safety
mode over a WAN, be cautious about how you add a witness to the session, because
unwanted automatic failovers can occur. For more information,
see Recommendations for Deploying Database Mirroring, later in this topic.

[Link] you take transaction log backup in simple recovery model and what is
the reason.
In simple recovery model u cannot take the transaction log backup as the log
is truncated when it checkpoint occurs all committed transactions are written
to the disk.
[Link]
html
In full recovery model,all the DML transactions are logged and unless you
have a periodic transactional backups,the committed transactions are still
there in the log [Link] makes it to grow until you perform a log [Link]
backup takes all the committed transactions into [Link] you have all the

transactions in the log you can recover the database upto the point in case of
disaster.
In simple recovery model,only the uncommitted transactions will be there in
the [Link] committed transactions are removed upon a checkpoint issued by
sql [Link] log file never grows abnormally and you cannot perfom a log
[Link] in time recovery is not possible in this [Link] you have a full
backup scheduled ,we can recover only upto that point.
General Notes:
Client connections to the principal server do not use the database
mirroring endpoint.