Your company uses SQL Server 2005. A user reports that an order processing application stopped responding

in the middle of an order transaction. The users SQL Server session ID is 54.

You need to find out if session?54 is blocked by another connection. If it is, you need to identify the blocking

session ID.

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 runs SQL Server 2005 Enterprise Edition. SQL1 has 2 GB of

RAM, 1.6?GB of which are used by the default SQL Server database engine instance. The average data growth of

all databases combined is 100 MB a month.

Users state that report execution times are increasing. You want to assess whether more RAM is needed.

You need to use System Monitor to create a counter log that will help you decide whether to add RAM.

Which performance object should you add to the counter log?

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 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?

A support engineer reports that inserting new sales transactions in a SQL Server 2005 database results in an

error.

You investigate the error. You discover that in one of the databases, a developer has accidentally deleted some

data in a table that is critical for transaction processing. The database uses the full recovery model.

You need to restore the table. You need to achieve this goal without affecting the availability of other data in 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?

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?

Your company uses SQL Server 2005. Users report that report execution is slow. You investigate and discover

that some queries do not use optimal execution plans. You also notice that some optimizer statistics are missing

and others are out of date.

You need to correct the problem so that reports execute more quickly.

Which two Transact-SQL statements should you use? (Each correct answer presents part of the solution. Choose

two.)

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 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?