<\/span>Intermediate Level SQL Database Administrator Interview Questions<\/span><\/h2>\n\n\n\nHere is a list of commonly asked database administrator interview questions and answers for SQL to help you prepare for intermediate-level roles.<\/p>\n\n\n\n
\n- How would you create a new login and user in SQL Server?<\/strong><\/li>\n<\/ol>\n\n\n\n
First, create a login at the server level using CREATE LOGIN. Then map it to a database user using CREATE USER. Assign roles or permissions as needed.<\/p>\n\n\n\n
\n- How would you troubleshoot a slow-running query?<\/strong><\/li>\n<\/ol>\n\n\n\n
I start by checking the execution plan to find bottlenecks. I also review indexes, outdated statistics, and missing indexes. Sometimes, I rewrite the query or break it into smaller parts.<\/p>\n\n\n\n
\n- What is the purpose of SQL Server Agent?<\/strong><\/li>\n<\/ol>\n\n\n\n
SQL Server Agent automates tasks like backups, index maintenance, and running scheduled jobs. It helps manage routine operations without manual effort.<\/p>\n\n\n\n
\n- Explain what a deadlock is and how you would resolve it.<\/strong><\/li>\n<\/ol>\n\n\n\n
A deadlock happens when two processes block each other, waiting for resources. I identify the processes involved using SQL Server Profiler or Extended Events. Then, I tune queries, change locking order, or use deadlock priorities to prevent it.<\/p>\n\n\n\n
\n- What is the difference between FULL and BULK-LOGGED recovery models?<\/strong><\/li>\n<\/ol>\n\n\n\n
In the FULL recovery model, every transaction is logged fully, allowing point-in-time recovery. BULK-LOGGED minimizes logging for bulk operations, but point-in-time recovery isn’t possible during minimal-logged operations.<\/p>\n\n\n\n
\n- How would you migrate a database from one server to another?<\/strong><\/li>\n<\/ol>\n\n\n\n
First, I take a full backup from the source server. Then, I restore it on the target server with the right recovery options. After restoring, I fix orphaned users, update server-specific settings, and test the application connection.<\/p>\n\n\n\n
Also Read - Top 30+ PL\/SQL Interview Questions and Answers<\/a><\/strong><\/pre>\n\n\n\n