SQL DBA interview questions+ CSC

CSC:

1.     What is backup strategy?

2.     How to rename SQL Server?

sp_dropserver 

GO

sp_addserver , local

GO

Remote Logins - If the computer has any remote logins, running sp_dropserver might generate an error similar to the following:

Copy

Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44

There are still remote logins for the server 'SERVER1'.

To resolve the error, you must drop remote logins for this server.

To drop remote logins

·         For a default instance, run the following procedure:

Copy

sp_dropremotelogin old_name

GO

·         For a named instance, run the following procedure:

Copy

sp_dropremotelogin 'old_name\instancename'

GO

3.     How to trouble shoot temp db?

4.     How to solve connection issue?

5.     How to solve recovery status issue, if my db box has 100 databases, In that only one db has go to suspect mode? How to solve?

6.     IS Full text search service comes as default service?

7.     How to find fragmentation level by using command prompt?

8.     What are maintenance plans that you executed?

9.     What type of alerting system that you configured for your sql server box?

10.  What is your database size?

11.  How to solve Log file growing issue?

12.  Is it possible to take back up from primary server what was participated in log shipping?

13.  What is end point in mirroring?

14.  What are the security concerns that you execute while we configure mirroring?

Security in SQL Server 2005 VS SQL Server 2000

SQL Server 2000

1.Security:Owner = Schema, hard to remove old users at times

2.Encryption:No options built in, expensive third party options with proprietary skills required to implement properly.

3.High Availability:Clustering or Log Shipping require Enterprise Edition. Expensive hardware

4.Scalability:Limited to 2GB, 4CPUs in Standard Edition. Limited 64-bit support.



SQL Server 2005

1.Security:Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates.

2.Encryption:Encryption and key management build in.

3.High Availability:Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware.

4.Scalability:4 CPU, no RAM limit in Standard Edition. More 64-bit options offer chances for consolidation.

1)-In SQL SERVER 2000 there where maximum 16 instances but in 2005 you can have up to 50 instances.

2)-Database mirror concept supported in SQL SERVER 2005 which was not present in SQL SERVER 2000.

3)-SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000.It was a separate installation for SQL Server 2000.

4)-SQL Server 2005 introduces a dedicated administrator connection (DAC) to access a running server even if the server is not responding or is otherwise unavailable. This enables you to execute diagnostic functions or Transact-SQL statements so you can troubleshoot problems on a server. which was not present in SQL SERVER 2000.

SQL DBA interview questions+ IBM

1.     What are tools that are used for backup and ticketing?

a.     Lite speed, BMC Remedy

2.     How to restore lite speed backup into sqlserver, is it possible to restore directly?

a.     That is not possible, because media type should be same

3.     What is the difference between User and login?

a.     User is data base level and login is server level

4.     How to add drive for cluster?

a.     Go to cluadmin

b.    Choose groups from left side

c.     Right click on add

d.    Choose drive which we want to add for existing cluster

5.     What is heart beat?    

6.     What are problems that you face for log shipping?

a.     Establish network tunnel

b.    Reduce transaction backup time from 30 min to 15 min

7.     What is your backup strategy?

8.     What are the major differences between 2000 and 2005 in terms of security?

a.     Owner = Schema, hard to remove old users at times in 2000

b.    Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates. In 2005

c.     In 2000 there is no concept of synonyms, in 2005 it is introduced

d.    In 2000 there in policy based management, in 2008 it’s there

9.     What are the steps for restoring data base?

a.     Go through the restore wizard in sql server management studio

10.  How to find deadlocks and how to resolve that?

a.     Just switch on the trace for 1204, 1222, 3605

b.    ex:DBCC Traceon(1204,1222,3505,-1)

11.  How to move logfile location from once drive to another drive?

a.     By attach and detach

12.  My log file is full how to solve it?

a.     By apply the shrink command

b.    Or by applying truncate command

13.  Can you tell some regular use of DBCC Commands?

a.     Dbcc checkdb()

b.    Dbcc showcontig()

c.     Dbcc dbreindex()

d.    Dbcc sqlperf()

e.     Dbcc traceon()

f.     Dbcc traceoff()

g.    Dbcc shrinkdatabase()

h.     Dbcc shrinkfile()

i.      Dbcc help()

j.      Dbcc ind()

k.     Dbcc checkident()

l.      Dbcc showfilestats()

m.   Dbcc inputbuffer()

n.     Dbcc outputbuffer()

o.    Dbcc log()

p.    Dbcc page(‘wowzzy’,1,945894,3)—check page is corrupted or not

14.  What is importance of dbcc showcontig()?

SQL DBA interview questions+ HCL + 2nd round

1.     What is the difference between sys.databases and sysdatabases

2.     How can we know linked servers information?—Ans: select * from sys.servers

3.     How to know all databases info that are in box? Ans: select * from sysdatabases

4.     Are we run query on mirror server?

5.     Are we run query on secondary server of log shipping?

6.     What is default port for sqlserver? 1433(tcp/Ip), UDP:1434

7.     What is dynamic port in sqlserver?

8.     How to set default backup directory path?

9.     How to add full text service after installation?

10.  How to find orphan logins?

11.  What is quorum?

12.  What is the advantage of active & active two node clustering?

13.  What are the reasons when a particular user account is not working?

14.  What are the reasons for connection is slow?

15.  What is the default time for remote connections? 600

16.  What is auditing?

17.  Which server role is assigned for backup? ANS: DB_BACKUPOPERATOR

18.  I have 30GB Ram, How can we configure for production environment? Ans: Depends SLA

19.  What are problems that you faced for log shipping?

20.  What is the distribution database?

21.  What does distribution database contains is it contains data?

22.  What is fill factor?

23.  How to shrink database? What are the steps?

24.  How to move temp data base?

25.  What are suggestible raid levels for mdf and log files?mdf-raid1,  log – raid5

26.  How many temp db’s are recommended as per Microsoft standards?

27.  Have you used profiler?

28.  Is it possible to save profiler data into table? Yes, in text file aslo

29.  What are table partitions?

30.  What is schema binding?

31.  Can I create index on table partitions?

32.  Can I get error while index is rebuilt?

33.  I have 4 databases, in that having 4 users, those 4 users are mapped with some server roles, and those 4 users are there in one database. If I am deleting that major db, is remaining db users are working or not? Yes, they have access with reaming db’s. Those are called orphan logins

34.  How to take resource data base backup?

Backing up the Resource database 
Since the Resource database is not available from the SQL Server tools, we cannot perform a backup similar to how we do it with the other databases. You can backup the database using the following options:

1.    You can use a simple xcopy command to copy from the source location to a destination where you keep your daily database backups. Use the -Y option to suppress the prompt to confirm if you want to overwrite the file. You can create a scheduled task to do this on a daily basis. If you want to keep multiple copies of the database files, you can create an automated script to rename them after the copy process.

xcopy :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf /Y xcopy :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf /Y

2.     

3.     

4.    You can use your file-based backup utilities such as NTBackup, IBM Tivoli Storage Manager, Symantec BackupExec, etc.

Restoring the Resource database 
It is important to document the location of your master database as part of your disaster recovery process. In previous versions of SQL Server, all we need to do to restore the server instance is to worry about the masterdatabase.

After a SQL Server 2005 instance has been rebuilt a restore of the master database will be done, the Resourcedatabase files should go along with it should a WITH MOVE option be required. This means that if the old location of themaster database will be different from the one after the restore, the Resource database files should already be there prior to restoring the master database. This is very critical if a hardware failure occurred and you need to move the system databases on a different drive during the server instance rebuild.

To restore the Resource database, just copy the database files to the location of the master database files. If you have an older version of the Resource database, it is important to re-apply any subsequent updates. This is why the recommended approach is to simply do a daily backup of these files.

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?

SQL DBA interview questions+ HCL

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?