SQL SERVER INTERVIEW QUESTIONS

SECTION 1

1) How many servers you were supporting?

2)  How were you maintaining the servers?

3)  What are your daily activities?

4)  How were you scheduling the jobs, through agent (or) any 3^rd party tool?

5)  What is DR strategy?

6)  Users are complaining that databases are responding very slow! How will you troubleshoot the issue?

7)  What are the different types of Replication?

8)  What types of maintenance jobs were there in your previous project?

9)  What will update statistics do? What is its significance?

10)  What are the few DBCC commands you use frequently?

11)  How will you check fragmentation on table or index?

12)  What is BCP and how is it useful?

13)  What are DTS packages and have you designed any such packages?

14)  What does SQL server Upgrade Advisor tool do?

15)  Why is there a possibility for only one clustered index?

16)  How will you troubleshoot a poor performing query?

17)  What are all the filters you will be seeing in SQL Server Profiler?

18)  What are the different types of Backup?

19)  What is the difference between FULL and Differential Backup?

20)  What is your backup strategy?

21)  What are DBCCINDEX, DBCC INDEXDEFRAGE commands used for?

22)  How will you find log space usages of all databases?

23)  How much memory does SQL Server requires?

24)  How will you monitor log shipping?

25) How will you setup Log Shipping? Explain in detailed?

26)  What are AWE configurations?

27)  What are all the jobs associated with Log Shipping?

28)  What is reorganizing index, rebuild index and what situations can you use these?

29)  What is check pint?

30)   If tempdb is full what u can do?

31)  Can you move tempdb one drive to another drive if business is running?

32)  If master database is corrupt in sql server what u can do?

33)  How many files in a database. If a ldf file is deleted in a database what you can do(Backup is not available) and mdf file is deleted what u can do?

34) What is the usage of ndf file in a sqlserver database?

35)  If blocking occurs in sql server how to identify you in sql server and how to resolve it?

36)  If deadlock occurs in sql server how to identify you in sql server and how to resolve it ?

37)  In all how many ways can we move databases? Which is the best one?

38)  How will you move databases(system and user)to different instance?

39)  How to move a particular table in a SQL Server instance?

40)  An application is slow, how will you troubleshoot from db perspective?

41)  A user was able to connect yesterday but is unable to connect today? What will you do?

42)  Difference between Logshipping and mirroring?

43)  Ticketing tool + CR tool (are both the same and different?)

44)  What are isolation levels in sql server 2000?

45)  Difference between read committed and seriallizable in sql server 2000?

46)  How to give select and update permission in sql server?

47)  Tell me new features in sql server 2005 as DBA?

48)  Difference between truncate and shrink?

49)  Which monitoring tool used tells me three main things in monitoring tool?

50)  If an error occurs in sql server how to identify that error is application error or database error?

51)  What is view and usage of a view?

52)  What is execution plan in sql server?

53)  What is usage of non-clustered index in sql server?

54)   If a primary server is fail how to up the secondary server?

55)  If tlog is deleted in primary server in logshipping how to restore in secondary server?

56)  What type of issues resolves in sql server recently?

57)  What type of maintenance plan in sql server?

58)  How to identify if a job is fail?

59)  How to configure error logs?

60)  What is statistics in sql server?

61)  What are statistics, under what circumstances they go out of date, how do you update them?

62)  What is stored procedure and trigger?

63)  How long the update statistics job used to run on the largest database(of size 1300GB)?

64)  If u found a block on the server when update statistics is running then what u do?

65)  How was u maintaining OLTP applications having terabytes of data?

66)  Update statistics run successfully on Sunday. After that on Wednesday large volume of data is inserted into?

67) What will you do in case of poor performance of query?

68)  Pre requisite for migrate from 2000 to 2005?

69) How many types of modes in database mirroring?

70)  What is database snapshot? How do you do that?

71)  How will u kill a process?

72)  How do you find out which process is getting blocked?

73)  How many cluster index and non cluster index are there?

74)  What is significance of update statistics command?

75)  What is blocking and how it is different from deadlock?

76)  How to transit the new Databases from Development team to Production Support?

77)  How identify property plays a role in case of Replicztion?

78)  Authentication Modes? Explain Mixed Mode?

79)   Database crash what will you do immediately?

80)   What will you do after installing a new SQL Server 2005 instance?

81)  Configure and Managing SQL Server?

82)  Difference between role and privilege?

83)   A database has 10 tables and user has to access only one table for a user to access?

84)  Logshipping primary crashes, how will you bring the secondary as primary?

85)  Profiler how frequently will you use?

86)  How to handle issues during installation upgrade? How will you handle those errors?

87)  Dotnet framework 2.0 minimum is required for SQL Server?

88)  What is latency period of Log shipping?

89)  Copy wizard in SQL Server 2005?

90)  How many instances do you handle?

91) Tempdb space is increasing very rapidly what will you do?

92)  If you use Truncate only option you will loose current transactions, how will you stop that from doing?


SECTION 2

1.     Difference between internal fragmentation and external fragmentation?

2.     How will you know whether sql server starts are not if services are not available?

3.     How will you trouble shoot when jobs fail?  Where will you monitor?

4.     How will you bring back sql server from hung mode?

5.     In mirroring when transactions moving from principle to mirror the mirror server crashed? What happen to those transactions?

6.     What are the configuration settings stored under master database?

7.     In to how many files your database divided? Why it is divided like that?

8.     What is your mirroring and log shipping frequency?

9.     How you know when your servers start last time?

10.   Can we take tail log back up when server is in active?

11.   What is the reason for to run data base consistency commands?

12.   How will you trouble shoot after identifying long running query?

13.   What is the schema for user login?

14.   How much time it take to full backup of 800gb ? What is size of database after you take back up?

15.   How will you move when orphaned users when they are more?

16.   How will you trouble shoot login problem   when ever user is trying to login he gets alert that “ User already logined”?

17.  What are issues u faced in installing Hot fixes and pathes?

18.  log shipping failed when it is configuring ? Will reconfigure or what?

19.  u r installed service pack up to 70% it got failed what will you do?

20.   What is live lock ?

21.   How u r data stores in data file Sequential or random?

22.  What is u r next step if server crash?

23.   What is execution plan in sql server?

24.   Difference between OLTP and OLAP?

25.  What is the usage of ndf file in a sqlserver?

26.  Reasons for database corruption?

27.  Where will SSIS packages stores? How will you run this packages ?

28.  Publisher we have space where as in subscriber space is not there for transactions ? what will u do in this case ?

29.  Some important data delete we need to recover from backup but the deletion time not provided ? how u restore data?

30.  How to move to Msdb and model databases? What will you do if msdb crash?

31.  What is service Broker ?

32.  How to tune a Stored Procedure?

33.  When ever crash happened in logshipping how will ur recover last backup by using query (not tail logup query)?

34.  How will move if publication database is more size (around 800gb) , is there any another way apart from moving as snapshot ?

35.  How will you configure linked server by using queries?

36.   How will you find the no of user hitting Sqlserver.

37.   What queries you use to get data from Jobs schedule , job history.  

38.   While running restore job in logshipping it failed continuously and throws a error what is reason?

39.  What are top 5 DMV’s u use?

40.   In repilication you can not able to connect to subscriber . how will you trouble shoot?

41.  Components in clustering configuration ?

42.  What is tuff file in log shipping? What it stores?

43.  What is autonomy, latency and transactional consistency in trasactional repilication?

44.  Diff between clusteradmin in win2003 server and win2008?

45.  How to strat sql server in miminal configuration mode ?

46.  What is raid? What are different types of RAID configurations?

47.  What are reasons for inconsistent data ?

48.  What is file stream in sql server ?

49.  What are the types of data compressions in sqlserver 2008?

50.  What is execution plan ? when you use it ?