A power failure occurs on the storage area network (SAN) where your SQL Server 2005 database server is located. You need to check the allocation as well as the structural and logical integrity of all databases, including their system catalogs. What should you do?

You are responsible for maintaining scheduled jobs for the SQL Server 2005 databases in your department. One of the jobs aggregates data from multiple sources for reports. This job runs daily. The job consists of multiple steps. Each step aggregates data for a specific report. A user reports that the data for some of the reports has not been updated recently. You need to ensure that every step of the job that aggregates data executes even when errors occur. What should you do?

You are moving a SQL Server 2005 database named Timesheet to a new database server. The Timesheet database is used by the TimeEntry application. You need to minimize the amount of time that the TimeEntry application is unavailable while you move the database. What should you do?

You discover that the msdb database on a SQL Server 2005 computer is corrupt and must be restored. Databases are backed up daily. The database backup files are written to a network share, but the file names do not clearly indicate which databases are in each file. You need to
locate the correct backup file as quickly as possible. The first file in the list is named DB_Backup.bak.

Which Transact-SQL statement should you use?

You are responsible for configuring and maintaining the backup jobs for a SQL Server 2005 computer. Backup files are written to a tape drive that is connected to the database server. In the future, your department will install a new backup server. Then backup files will be written to disk on the new server. To simplify maintenance, you need to configure the backup jobs so that minimal changes will be necessary to back up to the new server. What should you do?

You discover that all indexes of the Sales table on your SQL Server 2005 database server are heavily fragmented. You need to decrease the fragmentation of all indexes of the Sales table to a minimum, while keeping the Sales table available to all users. What should you do?

You manage a SQL Server 2005 database that contains a table with many indexes. You notice that data modification performance has degraded over time. You suspect that some of the indexes are unused. You need to identify which indexes were not used by any queries since the last time SQL Server 2005 started. Which dynamic management view should you use?

Your database named DB1 is one of 15 databases located on a SQL Server 2005 computer named SQL1. Recently, users who work with DB1 report that they experience long query execution times. You want to reproduce and analyze this problem in a test environment that holds all copies of the production databases. You need to record the workload for an entire day on SQL1 for later execution on the test server. You must ensure that all events are captured without skipping any, even if SQL1 is under stress. What should you do?

You use a SQL Server 2005 computer that was installed with the default settings. A database named DB1 is located on the server. Due to the recent failure of a hard disk controller, you decide to check the integrity of DB1. You create a maintenance task named CheckDB1 that uses default settings. Because this check must not interfere with user transactions, you schedule it to run over the weekend. The following Monday, you need to retrieve the outcome details of the CheckDB1 maintenance plan. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

Your company has a server named SQL1 that is dedicated to SQL Server 2005. SQL1 is configured for SQL Server and Windows Authentication mode. Using Windows Authentication, you have sysadmin privileges on SQL1. All users who work with SQL1 report that all applications that use SQL1 stop responding. Your supervisor asks you to investigate the problem. In Task Manager, you notice that CPU usage is at 100 percent. You try to connect to SQL1 by using SQL Server Management Studio. SQL1 does not respond. You need to connect to SQL1 to diagnose the problem. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)