What are the phases of sql server database restore process? Ans: 1. Copy Data: Copies all data,log and index pages from backup file to database mdf, ndf and ldf files 2. REDO: Rollfoward all committed transactions to database and if it finds any...
How MAXDOP impacts SQL Server? Ans: The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. This option determines the computing...
Consider a situation where publisher database log file has been increasing and there there is just few MB available on disk. As an experienced professional how do you react to this situation? Remember no disk space available and also we can’t create a...
. Does “Truncate” works in transactional replication? Ans: No! As per MSDN blogs information we can’t use TRUNCATE on published database against the published article instead we have to use “DELETE” without where clause.
How do you troubleshoot a Full transaction log issue? Ans: Columns log_reuse_wait and log_reuse_wait_desc of the sys.databases catalog view describes what is the actual problem that causes log full / delay truncation. Backing up the log. Freeing disk...
Can you describe factors that causes the logfile grow? Ans: 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...
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 (Insufficientlogspace). And the database went to suspect mode. How do you troubleshoot this issue? Ans:...
Can we perform a tail log backup if .mdf file is corrupted? Ans: 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....
Can we perform a tail log backup if .mdf file is corrupted? Ans: 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....
Replication Commands: Publisher.SP_ReplTran: Checks the pending transactions at p Distributor.MSReplCommands and MSReplTransactions: Gives the transactions and commands details. Actual T_SQL data is in binary format. From the entry time we can estimate...
