More DBA Interview Questions...

More DBA Interview Questions...

1. what are current service packs  of SQL server 2005 and 2008 and 2008r2 and 2012 ?

2. what are the system db's availble in sql server 2005? significance of each db ?

3. what is fill factor? what is the default value and what is the best value ?

4. how will troubleshoot log file growth issue which is causing disk space issue?

Ans: 
step1: verifying the open tranasaction. DBCC  opentran
step2. identfying the process using DBCC inputbuffer

if there is no open transaction , then i will take the log backup and shrink the log file.
and another option is truncating the log file and shrink the db and then take the full backup.

but truncating the log file is not possible in 2008.

5. what are the DBCC comonads you use regularly ?

6. wha are the dmv's you know  and what is the purpose?

7.how will troublsoot blocking?

8. what is the difference between locking and blocking and deadlock ?

9. what is the tail log backup? when it will be useful?

10. how sql server identifies the differential backup ?

ans: once the full backup was completed sql server will create a LSN, normally pages will  modify  user queries. while taking the differential backup sql server will capture the extents(8 pages) of modified pages from the marked LSN.


11. how will you verify the logins and users using queries in sql server?
ANS:
sys.syslogins : Lists all the login names in server
sys.sysusers : Lists all the users in database.

12. what are differences between mirroring ang logshipping?

13. can we configure mirroring in bulk log recovery model ?

ans: No. mirroring allows only FUll recovery model.

14. can we configure logshipping in bulk log recovery model ?

15. what is the quorm drive? what is the use of it?

ANS: quorm drive will be used to identify the cluster status, and it will decide the failover and failback process. in sql server 2008 r2 doesn't mandatory of quorm drive ?

16. how will you start the sql server in single user mode?

17. how will recover the secondary database in logshipping after the promary failure?

18. explain the modes in mirroring?

19. can we a tables in single filgroup? how ?
ans: we can create the tables in filegroup. for that first you need to create a new filegroup and keep it default. then new tables created in the db will move to that filegroup.

20. how to truncate the log file in sql server 2008? 
ans: it is not possible to truncate the log in 2008. but we can truncate in 2005.

21. how will you recover the suspect db?

ans: 
1. chnage the status of db
exec sp_resetstaus.
2. keep the db in emergency mode.
alter dataase dbname set emergency.
3. keep the db in singleusermode.
alter database dbname set single_user rollback immediate.
4. run checkdb.
5. repair if there are any corrupted pages.
6. keep the db in multiuser mode.

22. what is heap table?
ans: a table which doesn't have any index is called help table.

23. due to some memory issue, you are unable to open management studio. server is responding very slow. you want to troubleshoot , how will u troubleshoot?

ans: using DAC , we can connect the server. in the management studio in the server name tab, we need to provide servername,admin.

with this we can connect to sql servr and troubleshoot.

24. what are the new features availble in sql server 2008 ?

25. how will you identify when was the last time sql server was restarted?
ans: using error log.

26.how will check the processor count in sql server?

ans: sql server proeprties, or we can a query to verify the properties. xp_msver

27. what is the difference between active -active and active -passive cluster?

28. how the data will transfer from publisher to subscriber in replication ?

29.how will connect the sql server  in cluster?

ans: using network name we can connect the sql server using management studio.

30. what is the difference between dreindex and reorg?

31. what is fragmentation , how will you find in sql server 200 and 2005?

Ans: in sql server 2000 , we can use dbcc showcontig, 
in sql server 2005, we can use dbcc show contig or dmv's
dmv--> sys.dm.db_index_physical_stats.

32. How can you issue a full backup and not interrupt the LSN's?

ans: Issue a copy only backup.

33. what are statisstics? why we need to update inregular intervals?
ans: statistics will increase the selectivity of a index. if statistics are outof date sql server will choose wrong index and will cause the slow performance.

34. what is the difference between a Primary Key and a Unique Key ?

ans: primary key doesn't allow null values, unique key will allw one null value.

35.  what is orphan users? how will troubleshoot ?

36. what trace flag you enable to capture the deadlocks in the erorlog?
ans:  -T1204 & -T1222

37. Can we Perform Backup Restore operation on TEMPDB?

Ans. NO

38. what is the use of resource database ?
ans: it wlll contains system tables and procedures.

39. how will chnage the port no? does it require restar of sql server?

ans: in configuration manager --> sql server protocal--> tcp--> ip_all. and yes it require restart of SQL Server.

40. how will add a new table in publisher  , and will replicate without running the complete snapahot ?

DBA interview Questions...

Common DBA interview Questions..

===============================

1. Explain about your SQL Server DBA Experience.
2. What are the different SQL Server Versions you have worked on?
3. What are the different types of Indexes available in SQL Server?
4. What is the difference between Clustered and Non-Clustered Index?
5. What are the new features in SQL Server 2005 when compared to SQL Server 2000?
6. What are the High-Availability solutions in SQL Server and differentiate them briefly.
7. How do you troubleshoot errors in a SQL Server Agent Job?
8. What is the default Port No on which SQL Server listens?
9. How many files can a Database contain in SQL Server?How many types of data files exists in SQL Server? How many of those files can exist for a single database?
10. What is DCL?
11. What are the commands used in DCL?
12. What is Fill Factor?
13. What is the default fill factor value?
14. What is normalization? Explain different levels of normalization?
15. What is denormalization and when would you go for it?
16. How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
17. What's the difference between a primary key and a unique key?
18. What are user defined data types and when you should go for them?
19. What is bit data type and what's the information that can be stored inside a bit column?
20. What are defaults? Is there a column to which a default can't be bound?
21. What is a transaction and what are ACID properties?
22. What type of Index will get created after executing the above statement?
23. What's the maximum size of a row?
24. What are constraints? Explain different types of constraints.
25. What is RAID and what are different types of RAID configurations?
26. What are the steps you will take to improve performance of a poor performing query?
27. What are the steps you will take, if you are tasked with securing an SQL Server?
28. What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
29. What is blocking and how would you troubleshoot it?
30. How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
31. What are statistics, under what circumstances they go out of date, and how do you update them?
32. What are the different ways of moving data/databases between servers and databases in SQL Server?.
33. Explain different types of BACKUPs available in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?
34. How to determine the service pack currently installed on SQL Server?
35. What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
36. Write down the general syntax for a SELECT statements covering all the options.
37. What is a join and explain different types of joins.
38. What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?
39. What is the system function to get the current user's user id?
40. What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?
41. What do you think of this implementation? Can this be implemented better?
42. What is a self-join? Explain it with an example.
43. What are the steps to improve the performance of a query?
44. How would you use the SP_ functions to identify the blocking problems?
45. What are the different types of backups?
46. What are the different levels of isolation?
47. How can you start the SQL Server in the single user mode and the minimal configuration mode?
48. How can you know that statistics should be updated?
49. What is replication in SQL Server?