SQL Server DBA Interview question and answers Part 8

1. What is unsent log in SQL Server Mirroring ?

You can find the unsent log option in Mirroring monitor .

Unsent Log: When principle database\server is not online then what ever the transactions are running principle should be store in "UNSENT LOG"

 Unsent log size increases more if mirror database not present for longer time.

 This unsent log is part of principle server database

 Unsent log value should be "0" kb.

2. What is Unrestored log ?

Unrestored log: After some time mirror server \database present the data which is pending in principle server unsent log will be sent to mirror unrestored log.

3.  What is Mirror Commit Overhead ?

Mirror commit overhead: How much time take to commit any transaction in mirror server.

4.  How can we confirm mirror is in sync status ?

In Replication monitor , Mirroring state contains Unsent Log and Unrestored Log columns these column values should be "0" kb.

5. How you know mirroring endpoints are in running status ?

Select role_desc , state_desc from sys.database_mirroring_endpoints

                                             (or)

sp_dbmmonitorresults @database name = ‘DBNAME’

6. How to do manual fail over in Mirroring ?

T-SQL : Alter database <Database Name > set Partner FailOver . Do it on Prinicipal database.

GUI --> SSMS --> Click on principal database --> Task --> Mirror --> FailOver

7. What is role switching ?

Interchanging of roles like principal and mirror databases are called role switching.

8. How to create end points ?

Create endpoint <endpoint name > state = started / stopped / disabled as tcp (listner_port=5022/5023 for database_mirroring (role=partner/witness)

9. Can we change the recovery model in Mirroring ?

For mirrored configured database, you cannot be able to change the recovery model from full to any other bulk-logged\simple.

Note: why the reason: mirroring supports only full recovery model. If you change the recovery model mirroring is going to fails.

If you need to change, break the mirroring and then perform the recover model change operation. But again can't configure mirroring hence mirroring supports only Full recovery model .

10. What is the role of Quorum in DB Mirroring ?

Quorum contains the information of which instance is currently acting as principle (online) and which instance is acting as a mirror server.

Note: When witness included in mirror configuration, always before perform automatic failover witness reads from QUORUM to know from which instance to failover.

11. What is the role of  .TUF file in Logshipping ?

TUF file is a transaction undo file and it contains only uncommitted transactions and create in secondary server but not in primary...ONLY SECONDARY DB IS IN STANDBY MODE

A .TUF file is a Microsoft SQL Server Transaction Undo file.

The TUF file basically contains the information with respect to any modifications that were made as part of incomplete transactions at the time the backup were performed.

This file contains uncommitted transactions

This file creates only in STANDBY mode

This file resides always under secondary server> copy folder

Note: If .tuf deleted, log shipping is not going to work.

12. Is there any way to re-create .tuf file... i have configured log shipping but unfortunately tuf file has been removed and now log shipping has been stop and we are unable to up the same.?

Impact is only restore job failed in secondary. We have reconfigure the log shipping one more time to re-create .TUF file

Note: If I delete .TUF file then impact to restore job but not copy and backup jobs.

Tuf file updates dynamically whenever any log backup file restores

TUF file only creates in secondary server > copy folder>

 No .tuf file creates in “restoring “mode.

13.  What is the role of .wrk (work file ) file in Logshipping ?

Work file creates in secondary server and the main purpose is to manage the file copy process from Primary server to Secondary server, .WRK file is generated temporarily only and when file copied completely at secondary server, they renamed to the .trn extension .

The temporary naming using the .wrk extension indicates/ensure that the files will not picked up by the restore job until successfully copied.

Wrk file creates in both no recovery and standby mode.

14. If I delete records in Primary database what happens to secondary database ?

 Yes secondary server database records will be deleted.

15. If I truncate records in Primary database what happens to secondary database ?

 Yes secondary server database records will be truncated.