New QuestionYou administer a Microsoft SQL Server 2016 instance. The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week.You plan to define a backup strategy for the database. You need to ensure that the following requirements are met:– No more than 5 minutes worth of transactions are lost.– Data can be recovered by using the minimum amount of administrative effort.What should you do? Choose all that apply.

A. Configure the database to use the SIMPLE recovery model.B. Create a DIFFERENTIAL database backup every 4 hours.C. Create a LOG backup every 5 minutes.D. Configure the database to use the FULL recovery model.E. Create a FULL database backup every 24 hours.F. Create a DIFFERENTIAL database backup every 24 hours.

Answer: BCDE

New QuestionYou administer a Microsoft SQL Server 2016 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented.You need to reduce fragmentation.You need to achieve this goal without taking the index offline.Which Transact-SQL batch should you use?

New QuestionYou administer a Microsoft SQL Server database named Sales.The database is 3 terabytes in size.The Sales database is configured as shown in the following table. You discover that all files except Sales_2.ndf are corrupt. You need to recover the corrupted data in the minimum amount of time.What should you do?

A. Perform a restore from a full backup.B. Perform a transaction log restore.C. Perform a file restore.D. Perform a filegroup restore.

Answer: A

New QuestionYou administer a Microsoft SQL Server 2016 server. You plan to deploy new features to an application. You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.What should you do?

New QuestionYou administer a Microsoft SQL Server 2016 database named Contoso on a server named Server01.You need to write messages to the Application Log when users are added to or removed from a fixed server role in Server01.What should you create?

New QuestionYou administer a Microsoft SQL Server 2016 database named Contoso on a server named Server01.You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.What should you create?

New QuestionDrag and Drop QuestionYou need to recommend a backup process for an Online Transaction Processing (OLTP) database.The process must meet the following requirements:– Ensure that if a hardware failure occurs, you can bring the database online with a minimum amount of data loss.– Minimize the amount of administrative effort required to restore any lost data.What should you include in the recommendation? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Answer:

New QuestionYou use SQL Server 2014 Enterprise Edition. Your database contains a partitioned table named AuditData. AuditData is partitioned by year. Partition 1 contains data from the year 2010 and prior.Management has decided to archive all AUDITDATA records from 2010 and prior. Management wants the records to be removed from the database entirely and provided to the backup team as a zipped text file. The data must no longer reside in the database. There is very little tolerance for performance degradation in your environment.You need to remove all 2010 and prior data from the AuditData table by using the least amount of system resources possible.Develop the solution by selecting and arranging the required SQL actions in the correct order.You may not need all of the actions.

Answer:

Explanation:Note:– Create a new partitioned table with the partition function you want, and then insert the data from the old table into the new table by using an INSERT INTO…SELECT FROM statement.– SPLIT RANGE ( boundary_value )Adds one partition to the partition function. boundary_value determines the range of the new partition, and must differ from the existing boundary ranges of the partition function. Based on boundary_value, the Database Engine splits one of the existing ranges into two. Of these two, the one where the new boundary_value resides is considered the new partition.– BCP can be used top produce the zipped text file.– Example:plitting a partition of a partitioned table or index into two partitionsThe following example creates a partition function to partition a table or index into four partitions. ALTER PARTITION FUNCTION splits one of the partitions into two to create a total of five partitions.CREATE PARTITION FUNCTION myRangePF1 (int)AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );GO–Split the partition between boundary_values 100 and 1000–to create two partitions between boundary_values 100 and 500 –and between boundary_values 500 and 1000.ALTER PARTITION FUNCTION myRangePF1 ()SPLIT RANGE (500);

New QuestionDrag and Drop QuestionYou are planning to deploy a database to Windows Azure SQL Database.You need to design a stored procedure to update rows. The stored procedure must meet the following requirements:– If more than one row is updated, an error must be raised to the application and the update must be discarded.– The stored procedure must be designed to maximize concurrency.What should you include in the design? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Answer:

Explanation:Box 1: Begin an explicit transaction.Box 2: Perform an update in a try block.Box 3: Read the @@ROWCOUNT system variable.Box 4: Raise an error and roll back the transaction if the row count is less than 1.Box 5: Commit the transaction in a finally block.Note:– Read Committed is SQL Server’s default isolation level.– @@ROWCOUNTeturns the number of rows affected by the last statement.– Using TRY…CATCH in a transactionThe following example shows how a TRY…CATCH block works inside a transaction. The statement inside the TRY block generates a constraint violation error.– BEGIN TRANSACTION;– BEGIN TRY— Generate a constraint violation error.DELETE FROM Production.ProductWHERE ProductID = 980;END TRYBEGIN CATCHSELECTERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;IF @@TRANCOUNT > 0ROLLBACK TRANSACTION;END CATCH;IF @@TRANCOUNT > 0COMMIT TRANSACTION;GO