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 ?