IBM | How to answer IBM interview questions | TARGETjobs

IBM Interview Questions:

1.       I have taken the ‘Full Backup’, then I put the DB in ‘Bulk-Logged Recovery’. After some point of Time I have inserted 50 Records, Then I have taken T.Log Backup(Server  Crashes after I taken T.Log Backup)…So can we restore Full, T.Log Backup?  Is it enough to restore Full, T.Log backup to recover the DB?

2.       What is Log-Shipping, How many JOBS will be there in the Log-Shipping. Is it in which Recovey model? And after I said Full, Bulk-Logged Recovery models, he asked me why not Simple Recovery? What is TUF in Log-Shipping.

3.       Explain Mirroring, Difference b/w Log-Shipping & Mirroring.

4.       Replication Types & what is Publisher and Subscriber.

5.       Explain Clustering, What is the major difference in 2005, 2008 Clustering Installation.

6.       What’re Isolation Levels?  What’s Default Isolation?, What’re  ACID Properties?

7.       How do you find  Data files of Master DB, When SQL Server Services are not Running.

8.       Explain about types of Backups & Recovery Model and their Importance.

9.       In Log-Shipping if we want to Keep the Secondary Server in Read-Only mode to view the Reports, They How do u do it?

10.   Why do we need to keep the DB in Bulk-Logged recovery?

11.   What is Virtual Log File.

12.   What Is RDB (Resource DB), what is it’s functinality? If we face any issues after applying Patches/SP, then How to roll back in SQL Server 2008.

13.   Difference b/w Blocking & Dead Lock.

14.   What are SQL Server Startup Parameters?

15.   Difference Between Checkpoint & Lazy Writer.

16.   Installation failed in Middle, then how to Troubleshoot it?

17.   He asked some challenging Issues, which I have face in previous Organizations.

18.   Explain brief about Upgrade & Migration, and What Upgrade advisor does?

19.   Tell me 3 DBCC Commands which you use weekly basis & Have faced any issue after you run the DBCC commands?

20.   What is AWE? If we don’t enable AWE then what happens?

21.   What is GAM, SGAM, DCM?

22.   Suppose One query is running Very slow so what are the things you suppose to do? (Bloaking, Profiler, DMVs. Diagnose,)

23.   New feature in SQL Server 2008, 2008R2

24.   Master database LDF needs to move to some other disk, can we do it?

25.   He asked me more abt troubleshooting Concepts

1.Difference between Mirroring and Replication.Tell scenarios when you use mirroring and replication.

2. I always see Mirror database being behind principal database in mirroring.How will you try to resolve this issue?

3.Do you know how to find slow running queries on a database?

4.In aspect of performance tuning which type of mirroing asynchronous or synchronous mirroring is useful?

5.What performance counters you monitor and why do you monitor?

6.Write the steps one by one in query form how you set up mirroring wihtout using UI?

7.If one of the store procedure is throwing time out errors.How do you find which stored proc is having issue and how do you fix it?

8.If the principal database is down and autofailover happens changes the mirror DB to principal and we have a separate Witness server.After few moment we find the new principal server also goes offline?What are you ways to fix it?

9.When do you suggest we need to get servers into production and assume the exisiting servers cannot handle the requests?

10Have you run query plan and what parameters you look when you run a query plan?

1.Finding clustering

2.how to determine the nodes

3.what is ‘IS ALIVE’ and ‘LOOKS ALIVE’

4.How can you check the isalive

5.how can you find which one is active

6.if active fails where it will be recorded

7.what is heart beat

8.I want to configuring clustering what are all the things needed

9.what is clustering

10. what is san

A. Sand area network, It stores the transactions information.

11. what is active and passive

12. how to apply the on clustering

13.how many ip address want to install the clustering....

 Here are the Answers

(i) select serverproperty('isclustered')----if it returns 1 then it is clustered instance.(OR) Right Click on the instance go for properties in general tab you can find isclustered property.

(ii)SELECT NodeName, status, status_description, is_current_owner FROM sys.dm_os_cluster_nodes; 

(iii)LooksAlive is a lightweight check initiated by the Windows failover cluster that basically goes

out to the application and says, “Are you there?” By default, this process runs every 5 seconds.

• IsAlive is a more in-depth application-level check that can include application-specific calls.

For a clustered SQL Server instance, the IsAlive check issues the Transact-SQL query SELECT

@@SERVERNAME. The query used by the IsAlive check is not user configurable. IsAlive requires

that the failover cluster has access to SQL Server to issue this query. IsAlive has no mechanism

for checking to see whether user databases are actually online and usable; it knows only if

SQL Server (and the master database) is up and running. By default, the IsAlive process runs

every 60 seconds and should never be changed, unless directed by Microsoft Support.

(iv)it is not done by us as mentioned above it is taken care by SQL Server.

(V)it can be verified through cluster Administrator tool (type cluadmin in run for windows 2003)  (type cluadmin.msc in run for windows 2008) or there are some command line options which I will check and update you

(Vi)I Hope it is Quorum & Cluster Configuration Database....Need to explain more about this but in the interview we can say it is Quorum.

7)A private cluster network: Sometimes known as the intracluster network, or more commonly, the heartbeat network, this is a dedicated network that is segregated from all other network traffic and is used for the sole purpose of running internal processes on the cluster nodes to ensure that the nodes are up and running—and if not, to initiate failover. The private cluster

network does not detect process failure. The intervals these checks happen at are known as

heartbeats.

8) Will Explain it Later Will compose mail separately on this......

11)Active/Passive mode: In this mode, only the active server serves the client requests. One instance of SQL Server is installed on both the server systems. In this mode, the second server in the cluster is configured as passive node and becomes active node only in cases where the first server fails. Note that only one SQL Server license is required in this mode.

Active/Active mode: In this mode, both the servers serve the client requests. The MSCS arbitrarily chooses one of the servers to serve the client requests. If any of the active nodes fails, its resources are moved to another active node. To configure this mode, one SQL Server license per server is required.

6.if active fails where it will be recorded?

Not very clear is this related to failover?

Normally if a failover happens that information is recorded in cluster.log & Error logs.......

Please read the below content which explains the functionality of Quorum.............................

OK, now that I have given you all of the necessary background information, let’s move on to the big question. What is a quorum? To put it simply, a quorum is the cluster’s configuration database. The database resides in a file named \MSCS\quolog.log. The quorum is sometimes also referred to as the quorum log.

Although the quorum is just a configuration database, it has two very important jobs. First of all, it tells the cluster which node should be active. Think about it for a minute. In order for a cluster to work, all of the nodes have to function in a way that allows the virtual server to function in the desired manner. In order for this to happen, each node must have a crystal clear understanding of its role within the cluster. This is where the quorum comes into play. The quorum tells the cluster which node is currently active and which node or nodes are in stand by.

It is extremely important for nodes to conform to the status defined by the quorum. It is so important in fact, that Microsoft has designed the clustering service so that if a node can not read the quorum, that node will not be brought online as a part of the cluster.

The other thing that the quorum does is to intervene when communications fail between nodes. Normally, each node within a cluster can communicate with every other node in the cluster over a dedicated network connection. If this network connection were to fail though, the cluster would be split into two pieces, each containing one or more functional nodes that can not communicate with the nodes that exist on the other side of the communications failure.

When this type of communications failure occurs, the cluster is said to have been partitioned. The problem is that both partitions have the same goal; to keep the application running. The application can’t be run on multiple servers simultaneously though, so there must be a way of determining which partition gets to run the application. This is where the quorum comes in. The partition that “owns” the quorum is allowed to continue running the application. The other partition is removed from the cluster.

 I am sending some issues, CGI people asked me in
interview. Actually
i answered some trouble shooting technics but i dont know that is
correct or not.
So any one knows how to resolve those, please share to this group id....

1) issues:- if cpu growth is high what can u do?

Check which process is taking high... :) If SQL Server, then check which transaction is taking high cpu and check with App team. Also make sure, if any DBA Maintenance jobs were running or not.

You can see my article in srikanthsqldba.blogspot.com I've written what to do when sql server hits 100 CPU.

2)in error logs if u findout the stack dump, what is this and what u can do?

Should be related to lite Speed. 

Temoorary Fixing Should be restarting SQL Server.

Permemance Should be Upgrading the Lite Speed Version.

If there is no Lite Speed,

Raise a case with Microsoft, Probably they recommend to upgrade to the latest service pack.

If the SQL is running on latest version, not sure what to do... ^^.. :P

3)if ldf size is 100% and database is not connected, how to resolve this problem?

You can try using tsql commands to expand the database, sometimes this will work. 

or

I Should run transaction backup. 

or

Fire select * from sys.databases and check log_reuse_wait_desc, this will help us in understanding what the issues is 

and where to start.

or

If the database is completly unusable, will take it offline and bring it online... if App team are happy... :p

or

run dbcc opentran on db and ask if app team are happy to kill it. and see if problem resolves.

4)in mirroring principal server is down, all the data was transfer to mirror server after failover activity. if in this case 

mirror server is acting like a principal. once principal server is came to up

how to sync the data from mirror to principal?

Not sure. Has been ages since I worked on Mirroring... makes me sick.

5)if blocking occured, u have fired sp_who2 cmd to find out spid but that cmd is taking time for execution. what u can 

do? and how to resolve this issue?

select * from sys.sysprocesses where blocked <> 0

I need answers, these  question  are recently  i faced  in CSC interview.

1. What r the maintenance plans follw weekly?
2.How to monitor the Server through monitor tool like SCOM?
3.How to check previous backup running successfully run r not?  How to verify backup taken in the earlier shift timing?
4.how to change the DTA packages to SSIS while upgrading the 2000 to 2005?
5.How to monitor buffer cache hit Ratio?
6.What r the pre and post steps u following while  applying the Service Pack or  Hot fix(cumulative updates)? 

  

 Please find the answers for your questions. If there are any mistakes please feel free to correct me.

1) To Answer the first Question it depends up on the Database size and the environment where we are in.

  

   if Database sizes are small We will generally go with Rebuild of indexes followed by DBCC CHECKDB & Full backup of the Databases ----on Weekend(Saturday)

if Database sizes are huge then it will be difficult to perform Rebuild so we will go with 

1)Re-org of indexes

2)update statistics

3)DBCC CHECKDB

I am not saying that this should be followed but this is the recommended practice. 

2)To get sound knowledge on SCOM please check for the below Links

http://sqlbits.com/Sessions/Event6/Performance_Management_for_SQL_Server_with_SCOM_2007. 

http://www.simple-talk.com/sql/database-administration/monitor-your-database-backups-using-operations-manager/

http://www.itwalkthru.com/2010/12/configuring-system-center-operations.html

But in the interview if some one asks about Monitoring I would recommend you to say any other Monitoring tool apart from SCOM....if you want to go with SCOM then tell them that SCOM has been installed in one of the servers (generally called as Centralized server)from there all other instances are added to it. And say if anything goes wrong we will  get notification as an alert or sometimes we will log in to the server and check for the alerts section by opening SCOM.

it's Hard to understand at this point unless and until we work on it.

3)This query will give you the backups information (the one below will provide you the details of the last backup happened for the Database) if you need for previous days just change the content which is highlighted in Bold letters (Red)

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_start_date,

msdb.dbo.backupset.backup_finish_date,

msdb.dbo.backupset.expiration_date,

CASE msdb..backupset.type

WHEN 'D' THEN 'Database'

WHEN 'L' THEN 'Log'

END AS backup_type,

msdb.dbo.backupset.description 

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1) and msdb..backupset.type = 'D'

ORDER BY

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_finish_date 

4) http://www.mssqltips.com/sqlservertip/1224/upgrade-sql-server-dts-packages-to-integration-services-packages/   please use the link which clearly explains the upgrade of DTS to SSIS

5) Kamesh has provided all the details with respect to Buffer Cache Hit Ratio. So I hope that Information is Handy. Thank you Kamesh for the Detailed explanation.

6) I think Deepak has provided all the details that we need to look for when we are applying service Packs. I feel Personally those points are good enough (both on clustered as well as standalone).If you still need some reference please check the below link

http://blogs.technet.com/b/beatrice/archive/2009/02/10/best-practices-for-installing-sql-server-service-packs-hotfixes-cumulative-updates.aspx 

How to monitor buffer cache hit Ratio:

What is the SQL Server Buffer Cache?

The Buffer Cache (sometimes called the Data Cache) is an area of the SQL Server Buffer Pool (which in turn is part of the overall SQL Server Process space) and in simple terms is the amount of your database data (data pages) that are stored in memory.

You can find out how many data pages are currently stored in memory for each of your databases by referring to my post How Much Memory is Each SQL Server Database Using?.

What is Buffer Cache Hit Ratio?

The performance measure buffer cache hit ratio expresses as a percentage, how often a request for a database data page, can be served from the Buffer Pool. The alternative action is for SQL Server to have to fetch the data page from disk.

So for example, if you have a 500MB database, a server with 4GB and SQL Server is configured to use all available memory, you will have a very good Buffer Cache Hit ratio of 99% or above because your entire database can easily fit into memory.

How can I determine the Buffer Cache Hit Ratio of my server?

In order to determine you Buffer Cache Hit Ratio you can use the Windows Performance Monitor tool.

1.    Just go to: Start – Programs –  Administrative Tools – Performance

2.    Right click the graph and choose: Add Counters.

3.    Select Performance object: SQLServer:Buffer Manager 

4.    Add: Buffer Cache Hit Ratio.

Windows Performanc Monitor - Biffer Cache Hot Ratio

How can I get more detailed information about SQL Servers overall memory usage?

In order to get a detailed report of SQL Servers memory usage I like to use the DBCC statement:

DBCC MEMORYSTATUS