SQL Server DBA Interview question and answers Part 1

1. A user had a hard-disk crash where .ldf files were located . But mdf and ndf files are accessible. How the user can over come this situation ?

CREATE DATABASE FOR ATTACH_REBUILD_LOG can help the user to create a database without log.

2. What LOCK does truncate table statement acquire ?

Page Lock & Table Lock .

3. You have a table with 4,294,296,295 rows . You issues an update that should effect all but 3 or 4 Rows . You want to determine how many rows were effected by the update . How can you do this .

We need to use SELECT ROWCOUNT_BIG () function to get the value .

Note : We cannot use @@ROWCOUNT to check about it hence @@ROWCOUNT is an integer and it can work only for 2^31 rows .

4. What is the default protocol when you install SQL Server ?

TCP/IP 

5. What are different protocols available in SQL Server ?

TCP/IP , Named Pipe and Shared Memory .

6. If you don't specify the size with cast and convert , what is default size of char data ?

30

7.  What is the maximum size of Index Key ?

900 bytes .

8. What is maximum number of columns allowed in an index key ?

16

9. Can you run a differential backup on a database with Simple Recovery Model ?

 

Yes . It has nothing to do with differential backup.

10. Does transnational replication replicate CLR , Stored procedure , Full-Tex and CLR data types .

Yes .

11. In service broker , if your message does not send and has an error , where do you find this error ? .

In sys.transaction_queue.transmission_status .

12. What is default interval of check point .

SQL Server decides itself .

13. What is the function of SQL Server Agent ?

SQL Server Agent is a windows service which handles the tasks scheduled within the SQL Server environment . 

These tasks also called as Jobs .

These jobs may run on a predefined schedule or on demand through a Trigger .

This service is useful in determining why a particular job did not run as intended .

14. What is Buffer Cache ?

Buffer Cache is a memory pool in which data pages are read .

Default Buffer Cache value is 95 %  , another 5 % is needed for physical disk access .

If the value falls below 90% then it is the indication of more physical memory required on the server.

15. What is Log Cache ?

Log Cache is a memory pool used to  read and write the log pages .

A set of cache pages are available in each log cache .

The synchronization is reduced between log and data buffers by managing log caches separately from the buffer cache .