SQL DBA interview questions+ Wipro

Wipro 5+:

1. What is the performance counters used to check memory utilization.

2. Which DMV's we can use to check perform counters?

3. Which DMFs can we use to check the complete command running in a session.

4. Which exe file can be used to trace the server actitivites.

5. Statistics - How to update on a particular table index.

6. Memory configuration.

        If there is 16GB RAM, how can we make the server to use min 12 and          max 14.

        Lock pages in memory

7. How to improve the performance of Log Shipping.

8. How to move system dbs

        * TempDb

        * master

9. Execution plans

10.Why use DAC? How to connect?

11.Regular issues?

 

Best practices for installing service packs, cumulative updates and hotfixes for SQL Server

Test on a test/dev SQL Server first and only after you have confirmed that all applications are working as expected then install it on a production SQL Server. 

Review the Readme for Service Pack/cumulative update/hotfix. Any concerns/recommendations will be found in the readme. 

Run DBCC CHECKDB on ALL databases (user and system databases) and ensure that there were no errors reported. 

Backup ALL databases (user and system databases) and full-text catalogs (if applicable). This is NOT required but highly recommended. 

Stop Monitoring and Anti-virus services 

Make sure you have the proper permissions to install (administrative privilege on server/cluster node) 

The below points are for clustered SQL Server instance 

Make sure ALL SQL resources come online on ALL cluster nodes 

Make sure that ALL disk resources (even the ones that not being used by SQL Server) are online and not in failed state. 

Verify that there are no dependencies other than those created by the SQL Server setup on any SQL Server cluster resources. 

Run MPSRPT_SQL.exe on all cluster nodes (not required but recommended) 

Make sure all remote desktop connections are closed. You can connect to the node (you are running the setup from) 

using remote desktop connections but you should disconnect any remote connections to other cluster nodes.

SQL Server Interview Questions + CTS

1.     What are the differences between SQL 2005 and 2008?

2.     How to find which files are there in backup, without restoring?

3.     How to find locks?

4.     How to find log file size?

5.     How to check fragmentation?

6.     What are problems that you face in your carrier, Can you explain some situations?

7.     How to connect your sql server machine?

8.     What are the services that your company provides?

9.     What is your data base backup strategy?

10.  Difference between truncate and shrink?

Truncate: It truncates commited trns from logfile and not reduce file size, In the case of Shrink it reduce file size

11.  Is it possible to take backup of TempDB? No

12.  How many Clustered nodes are supported for sqlserver 2008R2, In Enterprise Edition?

Up to 16 nodes — Enter prise edition. (Windows 2008 server), in 2003 server 8 nodes

Up to 2 nodes — Enter prise edition.

SQL DBA interview questions+ HCL

1.     What is SQL Server Architecture?

2.     What is page?

3.     What is extent?

4.     What are the different types of extents?

Ans: Uniform extents: consists pages from same object, mixed mode extents: consists from different objects

5.     What is the difference between those two extents?

6.     Which pages are available in extents?

7.     What is fill factor?

8.     How to take backup of  DB when the db is in Log shipping by taking that backup without changing LSN?

Ans: By taking copy only backups

9.     How to change port number for sql server?

Ans: To assign a TCP/IP port number to the SQL Server Database Engine
In SQL Server Configuration Manager, in the console pane, expand SQL Server 2005 Network Configuration, expand Protocols for, and then double-click TCP/IP.

In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear, in the format IP1, IP2, up to IPAll. One of these are for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you wish to configure.

10.  Is it possible to change port for mirroring after configuring mirroring?

Ans: Yes it is possible, by using above mechanism

11.  What is the major difference between Merge replication and Transactional replication?

Ans: In Merge replication the both publisher and subscriber can work independently

12.  How to resolve conflicts in merge replication?

13.  What is quorum in clustering?

14.  What happened when quorum gone?

15.  Is it possible to start service when quorum is gone?

Ans: -No quorum

16.  How to take backup of 400GB DB with in less time?

Ans: Generally 1 GB take 1 min, If we use stripped backups we can reduce time, If we have 4 processors and 4 drives.

17.  How to find what are the driver’s available in our machine?

Ans: EXEC master.sys.xp_fixeddrives

18.  What is check point?

Ans:Check point is raised when we take backup of database or restarting sql server service

19.  Is it posible to raise chaeckpoint our self?

Ans:Yes

sp_configure 'recovery interval', 32767

go

reconfigure with override

20.  What is the difference between procedure and function?

21.  How to call procedure?

22.  What is the advantage of recompile statement in procedure?

Ans: I have noticed that after inserting many rows in one table many times the stored procedure on that table executes slower or degrades. This happens quite often after BCP or DTS. I prefer to recompile all the stored procedure on the table, which has faced mass insert or update. sp_recompiles marks stored procedures to recompile when they execute next time.

23.  How to know the current connected connections?

24.  How to set maximum connections?

Ans: SP_Configure

25.  How to start sql service without raising checkpoint?

SHUTDOWN WITH NOWAIT

26.  Have you work on cmd prompts?

Ans:

Runà CMDà net start mssqlserver

Runà CMDà net stop mssqlserver

27.  What is transaction?

Ans: IT is nothing but sequence of actions

28.  Can you say syntax of transaction?

Ans: Begin Tran

           Statements

        Commit Tran

 

29.  What is difference between cascade drop of table?

Ans: Cascade option allows the user to delete all the tables which are defined by foreign key relation

 

30.  What happened if we issue drop table command?

Ans: If the tables are involved in foreign key relation, if we try to delete those tables. It will not allow deleting. If we delete under any circumstances we define that as cascade

 

31.  What are the several recovery models?

Ans: Full, simple, Bulk logged recovery model

 

32.  Why log shipping is not supported in simple recovery model?

 

33.  What is the default port for mirroring?

Ans: 5022

 

 

34.  How to change default port for sqlserver?

Ans: Go to sqlserver configuration managerà serviceà protocolsà choose TCP/IPà right clickà Take propertiesà Go to advancedà Change IP for which are not loop backed ip

35.  What is resource governor?

36.  What is heart beating in clustering?

37.  Which cable is used for heart beat, Is it cross cable or plain cable?

 ANS: Cross cable

 

38.  What are the different types of indexes?

39.  What are the limits for cluster and non cluster indexes? ANS: 1- 255

40.  Why we have only one cluster index for table?

41.  What is the importance of statistics?

42.  Scenario: My server running fast upto the yester day, today onwords slows, As a DBA what you have to do?

43.  How to find what are the queries that are running in particular SPID?
sys.dm_exec_sql_text

44.  How to find sql server version?

Ans: select @@version

45.  What is another port number for sqlserver?

Ans: for TCP/IP: 1433, UDP: 1434

46.  I have backup and I want to restore upto particular time only?

Ans: Restore database wowzzy to disk=’’ with stopat=’time stamp’

47.  How to create user, how to assign read and write permissions only for that user?

48.  What is Super admin, who is having total rights in system level? Ans: SA

49.  What is full form of SA? Ans: System Administrator

50.  What are the different locks?

51.  What are different isolation levels?

52.  What is serelizable?

53.  What is exclusive lock?

54.  I have done some schema change (Add column for table) in merge replication for an article, is it applied for subscriber?

55.  Can you list some system tables?

         56.Difference between shrink and truncate?

o    Truncate cannot remove empty space

o    Shrink can remove empty space, so space can recollect

SQL DBA interview questions+ Infosys

1.     What is your backup strategy?

2.     What is quorum?

3.     How to install quorum?

4.     What is DMV?

5.     What normalization?

6.     Solve backup failure scenario?

7.     How many instances are there for your production box?

8.     How to create Login?

9.     How many CPU’s are there for your environment?

10.  What is the size of DB?

11.  What are differences between sql server 2000 to sql server 2005?

12.  What are the different versions in sql server?

13.  What is your current version?