Following are some “sql server 2008 dba interview questions” to help you in interviews.
How to Handle Sexual Harassment at Workplace
I have got many requests to publish first set of MSSQL DBA Interview questions. I observed that many interviewers are interested in finding out whether you worked on DB corruption or not. Here you go http://mssqlcorruptiontackle.blogspot.com/2010/12/ms-sql-corruption.html . Go ahead with ten most important Microsoft SQL Server DBA Interview Questions.
Interviewers are testing at least basic knowledge of clustering. Read following wires to be ready for this surprise: MSSQL Interview Question Clustering- Part1, Part2, Part3 and Part4
1. What is the difference between lock, block and deadlock?
2. What is the meaning of lock escalation and why/how to stop this?
3. How to truncate the log in sql server 2008?
4. How to Start Service Using Powershell Commands?
5. What changes in the front end code is needed if mirroring is implemented for the high availability?
6. Where does the copy job runs in the logshipping… Primary or secondary?
7. What are the ways to find what code is running for any spid?
8. When you get following error? Error 3154: The backup set holds a backup of a database other than the existing database.
9. Does DBCC CHECKDB requires DB to be in SINGLE_USER mode?
10. How to view the error log for any specific instance?
———————————————-
Answer 1:
Lock: DB engine locks the rows/page/table to access the data which is worked upon according to the query.
Block: When one process blocks the resources of another process then blocking happens. Blocking can be identified by using
SELECT * FROM sys.dm_exec_requests where blocked <> 0
SELECT * FROM master..sysprocesses where blocked <> 0
Deadlock: When something happens as follows: Error 1205 is reported by SQL Server for deadlock.
Answer 2: When the DB engine would try to lock page first and then it escalates locks to page and then table. If we
understand that whole table would be locked for the processing thenn this is better to use TABLOCK hint and get complete table blocked. This is a nice way to avoid the wastage of sql server DB engine processing for lock escalation. Somewhere you may also need to use TABLOCKX when you want an exclusive lock on the table in the query.
Answer 3: BACKUP LOG TestDB WITH TRUNCATE_ONLY is gone. SQL server doesn’t allow you to truncate the log now otherwise whole purpose of a DB is defeated. Read article http://mssqlcorruptiontackle.blogspot.com/2010/12/mssql-server-dbcc-loginfo-status-2-log.html to surprise interviewer with your answer. You have to make sure whether you need log or not. If you don’t need log then have the recovery model simple instead of full. If you don’t want the log to be accumulated in some particular bulk logging then change the recovery model BULK LOGGED for that duration and take one tlog backup just before and after this change. I shall discuss this later in my later blog. BACKUP LOG command backs up the t-log and frees the space in the log file.
Answer 4: How to Start Service Using Powershell Commands.
Answer 5: You need to add only FAILOVER PARTNER information in your front end code. “Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;”.
Answer 6: Secondary server. This question is basically asked to find out whether you have a handson work on logshipping or not. I came through many cases when candidates have mentioned logshipping experience in many projects and they can’t answer this question. I never selected any candidate if he/she don’t answer these kind of small questions.
Answer 7: Well there are many ways to do this.
1. find the spid which you want to analyze. An spid is assigned as soon as a client connection is established with the SQL server. To find the spid you can run any of the following command:
a) SP_WHO2 ‘ACTIVE’ — This will give you only active spids.
b) SELECT * FROM sys.dm_exec_requests
2. Get the spid from above two queries and use any of the following query to get what is happening behind that spid.
a) dbcc inputbuffer(<spid>)
b) sql2005 and sql2008 – SELECT * FROM sys.dm_exec_sql_text(<sqlhandle of that spid>)
c) sql2005 and sql2008 – SELECT * FROM fn_get_sql(<sqlhandle of that spid>)
Answer 8: The error comes when you are trying to restore the DB which already exists. Use WITH REPLACE option to restore the DB with a different name.
Answer 9: Yes and No. This is tricky question. If you are using repair option with CHECKDB then you have to have the DB in single user mode. Following is the method to have your DB in a single user mode.
Use master
go
sp_dboption dbname, single, true
Following is the error which you get when you run the DBCC CHECKDB with repair option w\o having the DB in single user mode.
The same is true for DBCC CHECKDB also.
Answer 10: There are many ways but I prefer following method. Take a scenario when you want to find the error log when the DB was put in a single user mode.
CREATE TABLE #Errorlog (Logdate Datetime, Processinfo VARCHAR(20),Text VARCHAR(2000))
INSERT INTO #Errorlog
EXEC xp_readerrorlog
SELECT * FROM #Errorlog
WHERE Text Like ‘%SINGLE%USER%’
Most of the readers of this blog also read following interview questions so linking them here:
Powershell Interview Questions and Answers
SQL Azure Interview Questions and Answers Part – 1
SQL Azure Interview Questions and Answers Part – 2
Read following blogs to surprise your interviewer and create chances to get into IT sector:
- https://tuitionaffordable.wordpress.com/sql-server-dba-interview-questions/
- http://mssqlcorruptiontackle.blogspot.com/2010/12/ms-sql-corruption.html
- http://mssqlcorruptiontackle.blogspot.com/2010/12/sql-server-2008-r2-foreign-key-delete.html
- http://mssqlcorruptiontackle.blogspot.com/2010/12/mssql-server-dbcc-loginfo-status-2-log.html
- http://mssqlcorruptiontackle.blogspot.com/2010/12/ssrs-report-adding-header-and-footer.html
Regards,
Online SQL Classes @ http://tuitionaffordable.webstarts.com
It wonderes me to see how numerous comments this site is getting. I guess it have a lot of enteries. how do you achieve that type of traffic?
I’ve been browsing online more than three hours today, yet I never found any interesting article like yours. It’s pretty worth enough for me. In my opinion, if all webmasters and bloggers made good content as you did, the net will be a lot more useful than ever before.
Solid post, nice work. It Couldn’t be written any improved. Reading this post reminds me of my previous boss! He usually kept babbling about this. I will forward this article to him. Pretty sure he will have a superb read. Thanks for sharing!
I have been browsing online more than three hours today, yet I never found any interesting article like yours. It’s pretty worth enough for me. In my view, if all webmasters and bloggers made good content as you did, the internet will be much more useful than ever before.
I’ve been here a few times and it seems like your articles get more informative each time. Keep it up I enjoy reading them.
thanks, and maintain up the great work
I loved as much as you will receive carried out right here. The sketch is attractive, your authored subject matter stylish. nonetheless, you command get got an shakiness over that you wish be delivering the following. unwell unquestionably come further formerly again as exactly the same nearly a lot often inside case you shield this increase.
Well these interesting facts you experience elaborated. Are actually worth reading and sharing, I must enunciate the fashion you get pent this info has really helped me alter the channelize of regard about this resource, considerably I am sure there is lot to come, this subject cannot exist satisfied by entirely this information. Will actually postponement for more info and elaboration on this. Nice information similar this forever wants you to die for more!
I really like the fresh perpective you did on the problem. Really was not expecting that when I started off studying. Your concepts were simple to understand that I wondered why I never looked at it prior to. Glad to know that there’s an individual out there that certainly understands what he’s discussing. Great job
this is cool
I’ll reccomend this post to others
where did you get it from?
Great blog here! Also your web site loads up very fast! What host are you using? Can I get your affiliate link to your host? I wish my web site loaded up as quickly as yours lol
Nice post. The information presented here was the greatest I could discover all day lengthy, and I have been searching hard on the Internet. I believe you should put this up on a big social bookmarking site, you will discover that it spreads like wildfire – Cheers – dave
Wow, incredible blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your site is excellent, as well as the content!
Hello there, You’ve done a great job. I’ll certainly digg it and personally suggest to my friends. I’m sure they will be benefited from this website.
Way cool! Some very valid points! I appreciate you penning this write-up
plus the rest of the website is really good.
Hey there would you mind letting me know which web host you’re working with? I’ve loaded your blog in 3 completely different browsers and I must say this
blog loads a lot quicker then most. Can you suggest a good hosting provider at a honest price?
Thanks, I appreciate it!
Very good write-up. I definitely appreciate this site. Keep it up!
streetdirectory (Delila)
I’ve been browsing on-line more than three hours these days, but I never discovered any interesting article like yours. It is beautiful value enough for me. Personally, if all web owners and bloggers made good content material as you probably did, the web will likely be a lot more helpful than ever before.
This is very interesting, You are a very skilled blogger. I have joined your rss feed
and look forward to seeking more of your magnificent post.
Also, I have shared your website in my social networks!
this is very good ans
Wow! In the end I got a blog from where I be capable of
really take valuable facts regarding my study and knowledge.
T-SQL/MSBI Knowledge Share Videos : http://www.youtube.com/results?search_query=T+SQL/MSBI+Knowledge+Share+Videos
http://skatageri.blogspot.in
Noow I amm going to do my breakfast, when having my breakfast cooming aggain
to read further news.
wow!!!