Hire Freelancers and Find Freelancers

https://www.elance.com/

https://www.odesk.com/

Microsoft SQL DBA Interview Questions with Infosys

It was Terrific and felt Happy after facing that...

1. Brief me about your self?

2. Ways to rename Database with TSQL Code?

sp_renamedb [ @dbname = ] 'old_name' , [ @newname = ] 'new_name'

USE master;
GO
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Make sure that no one is using the database, and then set the database to single-user mode.
3. Expand Databases, right-click the database to rename, and then click Rename.
4. Enter the new database name, and then click OK.

backup the Master DB after you rename any database.

3. When Log file is corrupted, how do you get the Database online ( you do not have any backups)?

Ans: SP_RESETSTATUS 'DBNAME'

If you have backup
restore recent backup with replace
Not getting much into theoretical part, you can read more about features in details from BOL or Search it on the web, let’s start with the issue and resolution.
Issue:

SQL Server shutdown unexpectedly (not clean shutdown) or Server Crashed or disk Crashed. On recovering the system or on starting the SQL Server services Database state changes to suspect. On checking the error you found

Message1:|
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_LogFile.LDF’. Diagnose and correct the operating system error, and retry the operation.

Message2:|
File activation failure. The physical file name “D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_LogFile.LDF” may be incorrect.

Message3:|
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Messag4:|
Backup detected log corruption in database system. Context is FirstSector. LogFile: 2 “D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_LogFile.LDF “’ VLF SeqNo: x2572 VLFBase: x4a60000 LogBlockOffset: x4ae3200 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x2043 LogBlock.StartLsn.Blk: x200000 Size: x0 PrevSize: x0

 

Message5:|
Msg 945, Level 14, State 2, Line 1

Database ‘Database Name’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Resolution

 

In case u lost the Log file (.Ldf)(Disk corruption, file deleted or SQL Server not able access the log file due to latency or disk issue) then the transaction which are not committed or not written to the data pages (hardened) will be lost (Might be possibility that u may see some data consistency issue).

Check the status of the Database by querying the system tables or views

select name,state,state_desc from sys.databases  where  name=’XYZ’

Name	State	State_desc
Database	3	RECOVERY_PENDING

 The only possible way to bring the database online is rebuilding the log file.
Set the database in Emergency Mode
ALTER DATABASE XYZ SET EMERGENCY
select name,state,state_desc from sys.databases  where  name=’XYZ’

Name	State	State_desc
Database	5	EMERGENCY

 Set the database in single user mode with rollback immediate mode
ALTER DATABASE XYZ SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Rebuild the log file
DBCC CHECKDB(‘XYZ’,REPAIR_ALLOW_DATA_LOSS)
Result MessageFile activation failure. The physical file name “D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\XYZ_log.LDF” may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Warning: The log for database ‘XYZ’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
 Check the database status
select name,state,state_desc from sys.databases  where  name=’XYZ’

Name	State	State_desc
Database	0	Online

The Database is still in single user mode, set it to multi user mode
ALTER DATABASE XYZ SET MULTI_USER

4. When Log file is full and SQL Server got Hung. we tried all the ways below but could not perform such as  Log Backup, Should not restart the server, Running Check Points, Adding Log file, Log File Shrinking. How do you free the Log Space and get the Server back to normal?


Backing up the log.
Freeing disk space so that the log can automatically grow.
Moving the log file to a disk drive with sufficient space.
Increasing the size of a log file.
Adding a log file on a different disk.
Completing or killing a long-running transaction.

Note: Factors, such as a long-running transaction, that keep virtual log files active for an extended period can restrict log shrinkage or even prevent the log from shrinking at all. For information about factors that can delay log truncation, see The Transaction Log (SQL Server).

Shrinking a log file removes one or more virtual log files that do not hold any part of the logical log (that is, inactive virtual log files). When a transaction log file is shrunk, enough inactive virtual log files are removed from the end of the log file to reduce the log to approximately the target size.


5. What type of Problems you faced so far in your company lately and how do you fix them?

6. In Cluster Environment, after maintenance activity I rebooted server from services. All the Databases are up and Running fine except one. The one server is in ' IN RECOVERY' mode, what might be the reason?
How do you check why DB is in 'IN RECOVERY' mode and how to know how much time it takes to DB to come to ONLINE state?

7. Tail Log backup Vs Transaction Log backup?

8. Log File is Full, how do you resolve this issue?

9. DB size is 1.5 TB, I have two SANs, one for Dedicated Data and another for Log. DB is in Full Recovery M ` odel. All Applications are hanged and user can not access. I can't increase the Log SAN. We can not create log file in Data SAN. How do you resolve the issue? Log Backup Failed, Check Point Did not solve, Can not restart the production server. Log File Shrinking also No Luck.

10. When Log file is full, change the recovery model to Simple and go back to free the Log Space.
will it release space?

11. What happens to LSN when you change the Recovery Model from FULL to SIMPLE and Vice Versa? LSN will reset or it'll continue form last LSN. What happens to the Backup strategy chain?
Ex: If we did the above on Monday, and server crashed on Wednesday, how do you recover from that? Is it possible to get the data till Wednesday or we can get only till Monday?
Backup Strategy: Sunday Full, Every Day: Differential Every 1 Hour: Transactional backup

Ans: Resotre in order given below.
Full Backup> Todays Differential> Last Hour Transactional>Tail Log if available.


12. How do you check why DB is in 'IN RECOVERY' mode and how to know how much time it
takes to DB to come to ONLINE state?

13. I use SA User to connect to DB only using Mixed Authentication Mode. My Login got disabled/forgot the SA Password. How can you enable it?

14. LDF corrupted and MDF is working fine. I don't have any backup. how do you get DB Online?

SP_RESETSTATUS 'DBNAME'

When Log file is corrupted, you can not access Database Properties.

15. Management Studio (SSMS) was not installed in my Machine, how do you connect to your Database?

ANS: SQLCMD,

16. What kind of High Availabilities you are using currently? What kind of DR you are using?

17. Important Things you need to know in replication?

18. Type of Replication in SQL 2008R2?


19. What is Index and type of Indexes available?

20. Clustered Vs Non Clustered?

21. Rebuild and Re Org?

22. pre requisite to rebuild an index?

23. Online Vs Offline Rebuilding Indexes?

24. What is Fill Factor?

25. What type of Extents will be created, if a Non-Clustered index is created?

26. What is Heap?

27. How many clustered and Non-Clustered Indexes can be created in 2008R2?

28. Primary Vs unique Key?

29. Char Vs VarChar Vs nVarChar?

30. can we use Truncate key in Foreign Key Table and why?

31. What is the use of DBCC commands? what is DBCC?

32. Tell about DBCC Command?

33. Trace Flags to trace DeadLocks?

Ans: 1222, 1204

24. What are the trace logs to enable Single user mode?

25. What is View?

26 What is Stored Procedure?

27. Stored Procedure Vs Trigger?

28. Where will be usernames and password get saved?

SysLogins Table

29. Use of Hints and explain?

Hints are options or strategies specified for enforcement by the SQL Server query processor on

SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the

query optimizer might select for a query.

Because the SQL Server query optimizer typically selects the best execution plan for a

query, we recommend that <join_hint>, <query_hint>, and <table_hint> 
30. What are the Main services will be available once installed SQL Server?

SQL Active Directory Helper Service (Enables integration with Active Directories)
SQL Full-text Filter Daemon Launcher (Performs document filtering and word breaking for SQL Server full-text search)
SQL Server
SQL Server Agent
SSAS
SQL Server Browser
SSIS
SSRS
SQL Server VSS Writer (Provide the interface to backup/restore Microsoft SQL server through the windows VSS Infrastructure) Volume Shadow Copy Service 

31. Pre requisites for DB Main?

32. What are the problems you faced with installations and how do you resolve with Examples?

33. I was able to setup Log Shipping with two servers, but when I try with another secondary server to add, I was unable to do it. what might be the reason?

34. How to apply patch in all modes like Clustering Nodes, Mirror Servers, Log Shipping Servers, Replication Servers on Physical and Virtual Environments?

35. Can we truncate data in a table with Foreign Key reference?

Funds for your New Business Ideas which should impact the People (Social Enterprise)

http://www.sankalpforum.com/


You have an Idea, Get Investors and Guidance

http://www.mytrah.com/inspiringsolution

Microsoft SQL DBA Interview Questions with FIS

FIS Global, Bangalore

1. What is the purpose of Covering index? how it is different from other indexes?


2. Tell All Fixed Server Roles

Ans: bulkadmin, dbcreator, diskadmin, processadmin, public, securityadmin, serveradmin, setupadmin, sysadmin

(Instance>Security>Server Roles)

3. Tell all Fixed Database Roles

Ans: db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter, db_dbowner, db_securityadmin, public

(Instance>Databases>Any Database>Security>Roles>Database Roles>)

4. What permissions should you have to kill a process?
' Processadmin' Fixed Server role

5. What is meant by Orphaned users? Complete understanding about it? What link is getting break while moving databases from one physical server to another?

6. How to fix and resolve orphaned users? 

7. Why should we fix orphaned users?

8. What is QUORUM? where this concepts is useful? How is it useful in Mirroring and Clustering?

http://en.wikipedia.org/wiki/Quorum
http://technet.microsoft.com/en-us/library/ms189902.aspx
http://technet.microsoft.com/en-us/library/hh270280.aspx
http://technet.microsoft.com/en-us/library/hh270278.aspx

9. Did you work on Clustering?
10. What is QUORUM in Clustering and how it works?
11. Which Server and user role permission you should have to run a job?


12. What is the purpose of 'Public' role under both Server Level and User Level roles?

13. Tell me about 10 new features introduced in 2005, 2008, 2008 R2, 2012 and 2014?

14. Tell DBCC commands

Ans: http://technet.microsoft.com/en-us/library/ms188796.aspx

15. How do you check, if you have enough memory in your server? Which object you check in 'Performance Monitor'?

16. How do you trace Deadlocks?

17. What is the difference between 1222 and 1204 Trace Flags?

18. What are the isolation levels and explain about them?




ITC InfoTech Questions

1. What are the prerequisites for DB Mail?
2. What is the purpose of Service Broker?
3. What is Virtual Log file?
4. What is 3GB Switch (AWE) and it's purpose?
5. What is DB Mail and SQL Mail?
6. What do you in Query Execution Plan?
7. How do you restrict a particular user to execute only few stored procedures?

Virtual Memory?

Processes use Secondary memory as RAM when RAM is running low. This is alswo known as Paging file.

A paging file is an area on the harddisk that windows uses as if it were RAM.

Installation Problems?

3GB switch (AWE)?

To enable 32 Bit Servers to use more than 3GB RAM...

Query Execution plan purpose?

It shows the execution cost with details such as Index/Table Scans and Key lookups.

Prerequisites for using DB mail?

SQL Server Service Broker is enabled by default in msdb database but may be deactivated if you attached an msdb database. Enabling Service Broker in any database requires a database lock. If Service Broker was deactivated in msdb, to enable Database Mail, first stop SQL Server Agent so Service Broker can obtain the necessary lock.

Virtual Log Files

http://technet.microsoft.com/en-us/library/aa933049(v=sql.80).aspx

Each transaction log file is divided logically into smaller segments called virtual log files. Virtual log files are the unit of truncation for the transaction log. When a virtual log file no longer contains log records for active transactions, it can be truncated and the space becomes available to log new transactions.

The smallest size for a virtual log file is 256 kilobytes (KB). The minimum size for a transaction log is 512 KB, which provides two 256-KB virtual log files. The number and size of the virtual log files in a transaction log increase as the size of the log file increases. A small log file can have a small number of small virtual log files (for example, a 5-MB log file that comprises five 1-MB virtual log files). A large log file can have larger virtual log files (for example, a 500-MB log file that comprises ten 50-MB virtual log files).

Microsoft® SQL Server™ 2000 tries to avoid having many small virtual log files. The number of virtual log files grows much more slowly than the size. If a log file grows in small increments, it tends to have many small virtual log files. If the log file grows in larger increments, SQL Server creates a smaller number of larger virtual log files. For example, if the transaction log is growing by 1-MB increments, the virtual log files are smaller and more numerous compared to a transaction log growing at 50-MB increments. A large number of virtual log files can increase the time taken to perform database recovery.

As records are written to the log, the end of the log grows from one virtual log file to the next. If there is more than one physical log file for a database, the end of the log grows through each virtual log file in each physical file before circling back to the first virtual log file in the first physical file. Only when all log files are full will the log begin to grow automatically.

Service Broker

Introduction

With Service Broker, a feature in Microsoft SQL Server 2005, internal or external processes can send and receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Manipulation Language (DML). Messages can be sent to a queue in the same database as the sender, to another database in the same SQL Server instance, or to another SQL Server instance either on the same server or on a remote server.

To better understand Service Broker, familiarity with the key concepts of queues, dialogs, conversation groups, and activation is helpful. These are discussed briefly in this section.

Queues

Service Broker uses queues to provide loose coupling between the message sender and the message receiver. The sender can use the SEND command to put a message in a queue and then continue on with the application, relying on Service Broker to ensure that the message reaches its destination.

Queues permit a lot of scheduling flexibility. For example, the sender can send out multiple messages for multiple receivers to process in parallel. The receivers might not process the messages until long after they were sent, but because incoming messages are queued, the receivers can process them at their own rate and the sender doesn't have to wait for the receivers to finish before continuing.

Dialogs

Service Broker implements dialogs, which are bidirectional streams of messages between two endpoints. All messages in a dialog are ordered, and dialog messages are always delivered in the order they are sent. The order is maintained across transactions, across input threads, across output threads, and across crashes and restarts. Some message systems ensure message order for the messages sent or received in a single transaction but not across multiple transactions, making Service Broker dialogs unique in this regard.

Each message includes a conversation handle that uniquely identifies the dialog that is associated with it. For example, an order entry application might have dialogs open simultaneously with the shipping application, the inventory application, and the billing application. Because messages from each application have a unique conversation handle, it's easy to tell which application sent each message.

Conversation Groups

Service Broker provides a way of grouping all the dialogs that are used for a particular task. This method uses conversation groups. In our previous order entry example, all the dialogs associated with processing a particular order would be grouped into a single conversation group. The conversation group is implemented as a conversation group identifier, which is included with all messages in all dialogs contained in the conversation group. When a message is received from any of the dialogs in a conversation group, the conversation group is locked with a lock that is held by the receiving transaction. For the duration of the transaction, only the thread that holds the lock can receive messages from any of the dialogs in the conversation group. This makes our order entry application much easier to write because even though we use many threads for scalability, any particular order is only processed on one thread at a time. This means we don't have to make our application resilient to problems that are caused by the simultaneous processing of a single order on multiple threads.

One of the most common uses for the conversation group identifier is to label the state that is associated with a particular process. If a process involves many messages over time, it probably doesn't make sense to keep an instance of the application running through the whole process. For example, the order entry application will scale better if, between messages, any global state that is associated with processing an order is stored in the database and retrieved when the next message associated with that order is received. The conversation group identifier can be used as the primary key in the state tables to enable quick retrieval of the state associated with each message.

Activation

You use the activation feature of Service Broker to specify a stored procedure that will handle messages destined for a particular service. When messages arrive for a service, Service Broker checks whether there is a stored procedure running that can process the messages. If there isn't a running message-processing stored procedure, Service Broker starts one. The stored procedure then processes messages until the queue is empty, after which it terminates. Moreover, if Service Broker determines that messages are arriving faster than the stored procedure can process them, it starts additional instances of the stored procedure until enough are running to keep up with the incoming messages (or until the configured maximum number is reached). This ensures that the right number of resources for processing incoming messages are always available.

Why Use Asynchronous, Queued Messaging?

Queues enable the flexible scheduling of work, which can translate to big improvements in both performance and scalability. To see how, go back to the order entry example. Some parts of an order must be processed before the order can be considered complete, such as the order header, available to promise, and order lines. But other parts realistically don't have to be processed before the order is committed; for example, billing, shipping, inventory, and so on. If the "delayable" piece of the order can be processed in a guaranteed but asynchronous manner, the core part of the order can be processed faster.

Asynchronous messaging can also provide opportunities for increased parallelism. For example, if you need to check the customer's credit and check availability for ordered items, starting both processes simultaneously can improve overall response time.

Queuing can also enable systems to distribute processing more evenly, reducing the peak capacity required by a server. For example, a typical incoming order rate might look something like this:

ms345108.sql2k5_servbroker_fig1(en-US,SQL.90).gif
Figure 1
There is a peak at the beginning of the day and another one at the end. If each order is entered into the shipping system as it is created, the shipping system's load would look like this:
ms345108.sql2k5_servbroker_fig2(en-US,SQL.90).gif
Figure 2
The afternoon peak is bigger because that's when the shipping paperwork is done for outgoing shipments. If the shipping system is connected to the order entry system with a queue, the peaks can be leveled by shifting some of the work to the slack incoming order times:
ms345108.sql2k5_servbroker_fig3(en-US,SQL.90).gif
Figure 3

Why Use Transactional Messaging?

Service Broker supports transactional messaging, which means that messages are sent and received as transactions. If a transaction fails, the message sends and receives are rolled back, and don't take effect until the transaction has processed the messages successfully and committed.

Transactional messaging makes programming a messaging application much more straightforward because you can freely scatter sends and receives wherever they make sense and nothing happens until the transaction commits. If the transaction gets rolled back, none of the sends take place, and the messages that were received go back in the queue so they will be received and processed again.

How Service Broker Solves the Hard Problems in Messaging
Messaging applications have been around for a long time, and there are compelling reasons to build them. So why aren't there more of them? The answer is that messaging applications are hard to get right. SQL Server Service Broker, however, solves some of the most difficult messaging application issues: message ordering, coordination, multithreading, and receiver management.
Message Ordering
In traditional reliable messaging applications, it's easy to get messages delivered out of order. For example, application A sends messages 1, 2, and 3. Application B receives and acknowledges 1 and 3, but experiences an error with 2, so application A resends it. However, now 2 is received after 3. Traditionally, programmers dealt with this problem by writing the application so that order didn't matter, or by temporarily caching 3 until 2 arrived so the messages could be processed in order. In contrast, Service Broker handles this transparently, so all messages in a dialog are received in the order sent, with no gaps in the message sequence.
A related problem is duplicate delivery. In the previous example, if application B received message 2, but the acknowledgement message back to application A was lost, application A would resend 2 and application B would now receive 2 twice. Again, Service Broker ensures that messages are never delivered twice, even if the power is lost in the middle of a transaction.
Coordination
Messages are generally stand-alone entities, which can make it difficult to determine which conversation a message came from. For example, you may send thousands of messages to an inventory service requesting inventory updates. The inventory service may respond to some of these messages almost immediately and take a very long time to respond to others, making it difficult to decide which response message corresponds to which inventory request.

With Service Broker, by contrast, both the dialog handle and the conversation group identifier are included with every message, making it very easy to determine the order and request that each response goes with. (Some messaging systems have a correlation ID you can set to make this determination, but with dialogs, this is not necessary.)

Multithreading
One of the most difficult issues in a messaging application is making a multithreaded reader work correctly. When multiple threads are receiving from the same queue, it's always possible for messages to get processed out of order, even if they are received in order. For example, if a message containing an order header is received by thread A and a message containing an order line is later received by thread B, it's possible that the order line transaction will attempt to commit first and fail a referential constraint because the order doesn't exist yet. Although the order line message will roll back until the order header exists, it is still a waste of resources.
Service Broker solves multithreading issues by putting a lock on the conversation group when a message is read, so that no other thread can receive associated messages until the transaction commits. Service Broker makes multithreaded readers work simply and reliably.
Receiver Management
In many reliable messaging systems, the application that receives messages from a queue must be started before messages are received. In most cases, the user must decide how many application instances or threads should be running in each queue. If this is a fixed number and the message arrival rate varies, there are either too many or too few queue readers running most of the time.
Service Broker solves receiver management issues by activating queue readers as required when messages arrive. Moreover, if a reader crashes or the system is rebooted, readers are automatically started to read the messages in the queue. Service Broker does many of the same kinds of application management tasks that are typically handled by a middle-tier transaction-processing monitor.

Why Build Messaging into the Database?

Why is Service Broker part of the database engine? Wouldn't it work as well if it were an external application that used the database to store its messages? There are several reasons why the database is the right place for Service Broker to be.

Conversation Group Locking
Service Broker makes multiple queue readers possible by locking the conversation group; however, locking the conversation group with normal database commands is almost impossible to accomplish efficiently. Service Broker accordingly uses a new kind of database lock, and only Service Broker commands understand this lock type.
Remote Transactional Receive Handling
Some messaging systems restrict transactional messaging to receiving applications that are running on the same server as the queue. Service Broker, by contrast, supports remote transactional receives from any server that can connect to the database.
Common Administration
One of the issues with transactional messaging systems is that if the messages are stored in a different place than the data, it's possible for the message store and the database to get out of synch when one or the other is restored from backup. With a single database for both messages and application data in Service Broker, this is very hard to get wrong. When your data, messages, and application logic are all in the database, there is only one thing to back up, one place to set up security, and one thing to administer.
Direct Sends to Receive Queue
Because Service Broker is integrated into the database engine, a message that is addressed to another queue in any database in the same SQL Server instance can be put directly into the receive queue, bypassing the send queue and greatly improving performance.
Common Language Support

The messaging part of an application and the data part of an application use the same language and tools in a Service Broker application. This leverages the developer's familiarity with Microsoft ActiveX Data Objects (ADO) and other database programming techniques for message-based programming. With the CLR (common language runtime) stored procedures available in SQL Server 2005, stored procedures that implement a Service Broker service can be written in a variety of languages and still take advantage of the common administration benefits of Service Broker.

Single-Connection Execution Capability
Service Broker commands can be executed over the same database connection as the rest of the Transact-SQL used by the application. Using a single connection means that a messaging transaction doesn't need to be a distributed transaction, as it would have to be if the messaging system were external to the database.

Enhanced Security

Because Service Broker messages are handled internally by the database, the access permissions of the message sender can be easily checked against database objects. If the message system were an external process, a separate database connection would be required for each user who is sending messages. Having the same identity for the database and the messaging system makes security easier to set up correctly.

Transmission Queue Visibility Across Databases

Because Service Broker runs in the context of the database instance, it can maintain an aggregate view of all messages that are ready to be transmitted from all databases in the instance. This capability enables each database to maintain its own transmission queues for backup and administration while still maintaining fairness in resource usage across all the databases in the instance, something that would be very difficult, if not impossible, for an external messaging manager to do.

Conclusion

The unique features of Service Broker and its deep database integration make it an ideal platform for building a new class of loosely coupled services for database applications. Service Broker not only brings asynchronous, queued messaging to database applications but significantly expands the state of the art for reliable messaging.

Question from Other Friends

1. What is out of synch state for a Database (file)?

Ans:


2, What do you do if MSI file Error occurs while installing patch in 2008? What about 2005 behavior?

In 2005, ResourceDB can be replaced to revert the damage with Sercive Pack installation Failure

3. How do you trace Dead Locks and solutions to prevent it?
4. What is the port no for different instances in your Server (multiple instances are installed on one PC)?

 Go to ' SQL Server Configuration Manager'
For Default Instance TCP Porn# 1433

for other instances 'TCP Dynamic Ports' values is set to '0', i.e. it works on Dynamic Ports.

SQL Version Query:

Select @@version

Select serverproperty(‘productversion’)

Select serverproperty(‘edition’)

select SERVERPROPERTY('productlevel')