SQL Server DBA Interview question and answers Part 3

1. What is transaction ? What is transaction (ACID ) properties ?

      Transaction is a set of logical unit of work and it contains one or more database operations. A valid transaction should be met ACID ( Atomocity , Consistency , Isolation , Durability ) properties .

a. Atomicity :

 A Transaction must be an atomic unit of work i.e either all data modification are performed or none of them is performed.

b. Consistency :

Consistency means before a transaction begins the database must be in a consistent state.

c. Isolation :

Isolation means that the effects of each transaction are the same as if the transaction was only one in the system i.e A transaction either sees a data in the state it was in before another concurrent transaction modified it or see the data after second transaction completed.

d. Durability :

Durability means that once a transaction is committed the effect of the transactions remain permanently in the database even in the event of system failures.

2. What is the purpose of LSN numbers ?

Every record in the transaction log is uniquely identified by a log sequence number . LSNs are used internally during restore sequence to track the point in time to which data has been restored.

3. Explain about RAID and RAID levels ?

RAID stands for redundant array of independent disks . RAID is a disk system that contains arrays to provide greater performance , reliability , storage capacity and lower cost . Typically 0 , 1 , 5 and 10 RAID levels are used in SQL Server .

To know more about RAID levels please refer the below link from my blog .

http://sqlandsai.blogspot.in/2016/02/raid-levels.html

4. How to set SQL Server in Single user mode and Minimal Mode ?

a. Single User Mode :

Only admin can access the server i.e user cannot access .

Start --> Run --> COmmand Prompt --> SQLSERVER.EXE -m

b. Minimal Mode :

All users can access the server .

Start --> Run --> Command Prompt --> SQLSERVER.EXE -f

5. What is meant by Orphan Users ? How to find Orphan Users and Resolve them ?

When a DB is move , attach & detach , restoring to different instance or  corresponding login is dropped then user of the database becomes Orphaned Users and they can't access the database because the database users are not mapped to SID (Security Identifiers ) in the new instance .

To detect orphaned users :

sp_change_users_login 'Report '

To resolve orphaned users :

sp_change_users_login 'update_one' , 'db_user','login_name'

6. What is Row size of a database ?

Row size of a database is 8060 bytes.

7. What is Heap table ?

A table without a clustered index is called as heap table .

8. How many ways you can move the data or databases between servers and databases ?

SSIS , DTS , Attach & Detach , Bulk copy program , Bulk Insert & Import and Export wizard .

9. How to find database ID ?

Select db_id('db_name')

10 . How to find out the database name using database id ?

select db_name(database id value)

11. How to find out Logins ? Current Users ?

To find out Logins :

Select * from sys.syslogins

To find out Current Users :

Select current_user

12. Is it possible to take backups and restores of system databases ?

Except Tempdb all the system databases can take backups .

13. What is service account ?

All SQL Services required a login account to operate . The login account can be

a. Local service account
b. Domain user account
c. Network service account
d. Local system account

Generally all companies are preferred "Domain user account " because it will require when the service account need to interact with other services on the network .

14. What do you mean by Collation ? what is default Collation ?

Collation is basically the sort order . Based on the collation SQL Server sorting and compare the data There are 3 types of sort orders i.e Dictionary case sensitive , Dictionary case insensitive and Binary .

15. How you will find the problems if Installation fails ?

We can analyze the errors by viewing the details available in 'Summary.txt' file located in setup bootstrap folder in 'C:\Program Files \ Microsoft SQL Server \ 90 \ Setup Bootstrap \ Log '