SQL Server DBA Basic Ques & Ans Part-4

SQL Server Database Administrator Questions & Answers

Q. After which phase database will available or online?
A: In SQL 2000 and SQL 2008 except Enterprise edition, After Undo Phase
In SQL 2008 and above for Enterprise edition, After Redo Phase. As fast recovery is possible because transactions that were uncommitted when a crash occurred reacquire whatever locks they held before the crash. While these transactions are being rolled back, their locks protect them from interference by users.

Q.Which SQL Server database does not follow ACID properties? Explain the reasons behind your answer. ?
A: The tempdb database does not follow the ACID property of "Durability".
Whenever Microsoft SQL Server is restarted, the tempdb is recreated. Any data that was committed to the tempdb is lost and replaced by a fresh copy from the model database, therefore violating the durability property which states that the effect of a transactoin must be persisted within the database irrespective of the state of the system.

Q. What is MSX / TSX in SQL Agent ?
A. Master Server (MSX)
The master server (MSX) is the host server where jobs, job steps, and schedules are created. Additionally, job results will flow from the target server(s) to the master server. You can view results from all job on the master server.

Target Server (TSX)
The target server (TSX) consists of one or more servers that are configured to accept jobs from a master server, and to report the results when those jobs are run back to the master server (MSX). The target servers will occasionally connect to the MSX server to download jobs, and updates to jobs.

Q. How to check SQL Server Version & Edition?
A. There is the command to get SQL version details:
SELECT
SERVERPROPERTY ('productversion') As ProductionVersion,
SERVERPROPERTY ('productlevel') As ProductionLevel,
SERVERPROPERTY ('edition') As Edition,
@@version As Version;
GO

Q. What are the page verify options in SQL Server 2008 and above?
A: A page verify is basically used to validate IO or corruption of pages. When the data is writes to a page in the disk, there are three types of page verify options in SQL Server to validate the page corruption- NONE, Torn Page Detection & CHECKSUM.

Q. What is the difference between Torn Page Detection & CHECKSUM? Which one is the better option?
A: When it writes a page, Torn Page Detection grabs the first 2 bits of every 512 byte sector on each page and stores those bits in the page header. When the page is later read back in from disk, SQL Server compares those header bits with the bits from the sector, to make sure they’re still the same. Now, that check is better than nothing, but you can see where it might potentially miss corruption in the rest of the sector, right?
The Checksum option, on the other hand, creates a checksum value using the content of the entire page, and saves that value in the header. When a page is read from disk, a checksum is created again and compared to the saved checksum. Since any difference in the bytes read for that page will result in a different checksum value, this is a far more thorough method for validating IO.

Q.What is the use of SQL Server Browser ?
A. SQL Server Browser contributes to the following actions:
1.Browsing a list of available servers.
2.connecting to correct server instance.
3.Connecting to dedicated administrator connection(DAC).
For more details click here

Q. Can we perform a tail log backup if .mdf file is corrupted?
A:Yes we can perform a tail log as long as the ldf if not corrupted and no bulk logged changes.
A typical tail log backup is having two options, 1. WITH NORECOVERY 2.Continue After Error.
1. WITH NORECOVERY: To make sure no transactions happens after the tal log backup
2. CONTINUE AFTER ERROR: Just to make sure log backup happens even though some meta data pages corrupted.

Q. 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?
A: In that case we can actually add a new log file on other drive and rerun the restore operation using the system stored procedure “sp_add_log_file_recover_suspect_db”. Parameters are the same as while creating a new log file.

Q. Let’s say we have a situation. We are restoring a database from a full backup. The restores operation runs for 2 hours and failed with an error 1105 (Insufficient space on the file group). And the database went to suspect mode. How do you troubleshoot this issue?
A: In that case we can actually add a new data file on another drive and rerun the restore operation using the system stored procedure “sp_add_data_file_recover_suspect_db”. Parameters are the same as while creating a new data file.

Q. Can you describe reasons that causes the log file grow?
A:Here are the few factors which can be the causes:
- CHECKPOINT has not occurred since last log truncation
- No log backup happens since last full backup when database is in full recovery
- An active BACKUP or RESTORE operation is running from long back
- Long running active transactions
- Database mirroring is paused or mode is in high performance
- In replication publisher transactions are not yet delivered to distributer
- Huge number of database snapshots is being created

Q. How do you troubleshoot a Full transaction log issue?
A: Columns log_reuse_wait and log_reuse_wait_desc of the sys.databases catalog view describes what’s the actual problem that causes log full is / delay truncation.
SELECT log_reuse_wait, log_reuse_wait_desc, * FROM sys.databases
If there we have active transaction, take below action
-Back up the log.
-Freeing disk space so that the log can automatically grow.
-Moving the log file to a disk drive with sufficient space.
-Increasing the size of a log file.
-Adding a log file on a different disk.
-Completing or killing a long-running transaction.