Microsoft-PRO Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005 Set 2

You are a database administrator for Certkiller .com. A database named OLTP_Report makes data available to Reporting Services reports. You use Reporting Services to create a report named SalesDeviation, which displays sales goals and sales representatives' actual sales. You need to manage report subscriptions. All sales representatives located in the Windows Sales group who do not meet sales goals must automatically receive a SalesDeviation report every Monday morning. What should you do?

You work as DBA at Certkiller .com. You administer a SQL Server 2005 computer named CertkillerA. Certkiller A hosts a database named DB1. DB1 serves as the backend database for an application that tracks issues that help desk technicians receive and the associated trouble tickets. When installed, the help desk application loaded several stored procedures and views that are used for data manipulation and reporting purposes. DB1 contains more than 50 tables. The bulk of the data is stored within the Employees, Issues, Assets, and AssetTracker tables. An application developer inadvertently deletes the AssetTracker table in DB1. The AssetTracker table is referenced by several views and functions in the database. You need to ensure that users are not able to remove critical tables such as this that are referenced by views or functions. What should you do?

You are a database administrator for Certkiller .com. You create a maintenance plan that includes shrinking your SQL Server 2005 databases and reorganizing your indexes once a week. You test the plan and discover that the database does not shrink as expected. You need to troubleshoot why the database is not shrinking. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

You are the database administrator for Certkiller .com. The company uses a SQL Server 2005 database to track online sales. Employees who run the In-Stock report experience slow performance. This performance problem is caused by the contention between the In-Stock report and high volume of data entry to the tables that are accessed in this report. The company wants the In-Stock report returned immediately even though other employees might be changing the data within their data-entry transactions. Any changes made in other transactions while the report is running must be visible to the employees who are running the In-Stock report. You need to design a solution to achieve this goal. What should you do?

You are a database administrator for Certkiller .com. You have separate SQL Server 2005 development and production environments. You use the Business Intelligence Development Studio to create a SQL Server Integration Services (SSIS) package in your development environment. Then, you use the SSIS package to import data into your development environment from one of your company's trading partners. You need to deploy the SSIS package to your production environment. Your production environment uses different table names than your development environment. What should you do?

You are a database administrator for Certkiller .com. Users report that a SQL Server 2005 application is performing poorly. You use System Monitor and discover that disk activity on the volumes that contain the database data files is very high. You need to find out which SQL Server activity is causing the high disk activity. You want to accomplish this task by using SQL Server Profiler. What should you do?

You are a database administrator for Certkiller .com. You administer a SQL Server 2005 database named Contracts. The Contracts database becomes corrupted. You perform a restore by using the following Transact- SQL script. RESTORE DATABASE Contracts FROM contracts_bu_device WITH CONTINUE_AFTER_ERROR, CHECKSUM, RECOVERY After the restore, the SQL Server logs show errors in some of the page restores. You need to repair the database pages that are corrupt. You need to achieve this goal by using the method that facilitates the fastest possible repair. Which Transact-SQL statement should you use?

You are a database administrator for Certkiller .com. You run SQL Server Profiler by using the SP:Recompile event class. You notice a large number of recompiles occurring. You need to identify the possible cause of the large number of recompiles. What is the most likely cause of the problem?

You are a database administrator for Certkiller .com. The company uses a SQL Server 2005 database. A full backup of the database is made each night. Transaction log backups are made each morning and each afternoon. You notice that the database transaction log file grows very large throughout the day. You need to minimize the size of the transaction log file. You also need to minimize data loss in the event of a failure. What should you do?

You work as DBA at Certkiller .com. You administer a SQL Server 2005 computer. The performance of the server has degraded to a great extent over time. You are troubleshooting the indexing environment. You rebuild the clustered indexes that are used the most frequently. You want to optimize the most frequently accessed nonclustered index. You access the sys.dm_db_index_usage_stats dynamic management view (DMV). The results are shown in the following table. You need to identify which nonclustered index is being used the most frequently when the entire index is not read. Which object_id and index_id combination should you use?