SQL Server DBA Interview Questions - SQL Server DBA FAQs

Steps to improve performance of a poor performing query - Maximum use of indexes, stored procures should be done, Avoid excessive use of complicated joins and cursors........
Deadlock and live lock - A deadlock occurs when two or more processes waits for a resource that is acquired by or is under the control of another process.....
What is blocking and how would you troubleshoot it? - Blocking occurs when two or more rows are locked by one SQL connection and a second connection to the SQL server requires a conflicting on lock on those rows........
Different types of BACKUPs available in SQL Server - Complete database backup: This type of backup will backup all the information in the database. Used most commonly for disaster recovery and takes the longest time to backup........
What is database isolation in SQL Server? - Isolation in database defines how and when changes made by one transaction can be visible to other transactions......
What is a Schema in SQL Server 2005? - A schema is used to create database objects. It can be created using CREATE SCHEMA statement.......
Create a Scrollable Cursor with the SCROLL Option - Using the SCROLL keyword while declaring a cursor allows fetching of rows in any sequence.......
Create a Dynamic Cursor with the DYNAMIC Option - When a cursor is declared as DYNAMIC, the cursor reflects all changes made to the base tables as the cursor is scrolled around......
SQL Server System database - Describe in brief system database. You can find answer to this question in this series.
SQL Server Transaction Architecture - What operations do SQL Server transaction logs support?, Explain the purpose of check points in a transaction log., What is write-ahead transaction log?
What is use of DBCC Commands? - Database Consistency Checker Commands give details in form of statistics about the SQL Server.......