1. What is the Guest user account in SQL Server? What login is it mapped to it? Ans: The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object. It is not mapped directly to...
Full backup size is 300 GB, usually my diff backup size varies between 300 MB and 5 GB, one day unfortunately diff backup size was increased to 250 GB? What might be the reason any idea?
Q. See I have an environment, Sunday night full backup, everyday night diff backup and every 45 min a transactional backup. Disaster happened at 2:30 PM on Saturday. You suddenly found that the last Sunday backup has been corrupted. What’s your recovery...
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:...
