Explain the steps involved in database replication setup.<\/strong><\/li>\n<\/ol>\n\n\n\nFirst, configure the distributor. Then set up the publisher, which sends data. Next, create the subscriber, which receives data. Finally, define articles, start the agents, and monitor replication using Replication Monitor.<\/p>\n\n\n\n
<\/span>Scenario-Based Interview Questions in SQL Server<\/span><\/h2>\n\n\n\nHere are some SQL Server scenario-based interview questions to help you practice real-world problem-solving for your interviews.<\/p>\n\n\n\n
\n- What would you do if a user complains about slow report generation?<\/strong><\/li>\n<\/ol>\n\n\n\n
First, I would check if the underlying queries are slow. I would review the execution plans, check for missing indexes, and confirm if too much data is being pulled. Sometimes optimizing the report design itself also helps.<\/p>\n\n\n\n
\n- How would you recover a database that has gone into suspect mode?<\/strong><\/li>\n<\/ol>\n\n\n\n
I would first check the error logs to find the cause. Then, I would set the database to EMERGENCY mode, run DBCC CHECKDB to repair issues if possible, and finally bring the database back online after confirming data integrity.<\/p>\n\n\n\n
\n- If a query is running fine today but slows down tomorrow, how would you troubleshoot it?<\/strong><\/li>\n<\/ol>\n\n\n\n
I would compare the execution plans from both days. Changes in statistics, parameter sniffing, blocking, or server resource issues are common reasons. Updating statistics or recompiling the query often solves the problem.<\/p>\n\n\n\n
\n- What would you check if transaction log files are growing too large?<\/strong><\/li>\n<\/ol>\n\n\n\n
I would check if the database is in full recovery mode without regular log backups. I would also review long-running transactions and replication status if used. Regular log backups or shrinking logs after fixing issues can control file size.<\/p>\n\n\n\n
\n- How would you move a large database from one server to another with minimum downtime?<\/strong><\/li>\n<\/ol>\n\n\n\n
I would take a full backup, restore it with NORECOVERY on the new server, and apply transaction log backups until the final cutover. This keeps downtime very low during migration.<\/p>\n\n\n\n