Microsoft R&D SQL Server Oops Lead

1. What happens when a transaction runs on SQL server? Let’s say simple update statement “Update Table set col1 = value where col2 = value”
​​Ans:

​It issues an update lock and upgrades it to Exclusive Lock

The corresponding page would be captured from disk to memory

The modified page will be modified at Memory

The operation “Update *******” will be written to LDF.
Check point happens and the modified page will be written back to Disk and the operation at LDF marked as committed.

Lazy writer is responsible for cleaning the committed transactions from LDF.

2. What is fragmentation? How it happens?

3. See we have a full backup on Sunday 8 PM, Diff and every day : 8 PM and log bkp on every 15 min. DB Crashed on Saturday afternoon 2:55 PM. How to rebuild the database? If suppose the last Sunday full backup is corrupted then how can you restore the database in current in time?

4. I have an instance on which there are databases in both FULL and SIMPLE recovery models. If I restart the sql service, what is the difference between these databases in recovering or what happens while restarting the services?

5. I have a log file which is of 250 GB. Log is full. We don’t have a disk space on any other drive for creating .ndf, auto growth is ON, and essentially there are no options to allocate new space for the file. What’s your action plan?

6. Can we do replication with mirroring? If yes what are the limitations?

7. Can we perform a tail log backup if .mdf file is corrupted?

8. Task manager is not showing the correct memory usage by SQL Server. How to identify the exact memory usage from SQL Server?

9. What is the option”Lock Pages in Memory”?

10. How to apply service pack on Active / Passive cluster on 2008 and 2012?

11. Can we configure log shipping in replicated database?

12. How to configure replication on cross domain instances?

13. Let’s say we have a situation. We are restoring a database from a full backup. The restore operation ran for 2 hours and failed with an error 9002 (Insufficient logspace). And the database went to suspect mode. How do you troubleshoot this issue?

14. Consider a situation where publisher database log file has been increasing and there there is just few MB available on disk. As an experienced professional how do you react to this situation? Remember no disk space available and also we can’t create a new log file on other drive

15. Can we add an article to the existing publication without generating a snapshot with all articles?