Microsoft TS:MS SQL Server 2008,Implementation and Maintenance Sample Questions:

1. You maintain a SQL Server 2008 instance that contains a database named Finance.
You suspect that three large tables in the Finance database are corrupt. You plan to execute the DBCC CHECKTABLE statement concurrently on the three tables to minimize downtime.
You need to set the database option to prevent users from accessing the database during the execution of the statement.
Which database option should you turn on?

A) READ_ONLY
B) OFFLINE
C) SINGLE_USER
D) RESTRICTED_USER

2. You administer a SQL Server 2008 instance that contains a database named SpatialDB. The SpatialDB database includes spatial data types.
You need to perform a database consistency check on SpatialDB to include the spatial indexes. You also need to ensure that the effect on the database concurrency is minimized.
Which Transact-SQL statement should you execute?

A) DBCC CHECKDB (SpatialDB) WITH EXTENDED_LOGICAL_CHECKS:
B) DBCC CHECKALLOC (SpatialDB) WITH TABLOCK;
C) DBCC CHECKDB (SpatialDB) WITH TABLOCK. PHYSICAL_ONLY:
D) DBCC CHECKCATALOG (SpatialDB);

3. You administer a SQL Server 2008 instance that contains a database named Sales.
A SQL Server login named SalesAppLogin maps to a database user named SalesAppUser in the Sales database.
You need to ensure that the database user can perform only the following tasks:
- Execute all stored procedures that currently exist in the Sales database. - Execute all stored procedures that will be created in the Sales database.
What should you do?

A) Add SalesAppUser to the appropriate fixed database roles.
B) Grant the appropriate database-level permissions to SalesAppUser.
C) Grant the appropriate server-level permissions to SalesAppLogin.
D) Grant the appropriate object-level permissions to SalesAppUser.

4. You troubleshoot a SQL Server 2008 instance.
You suspect that an application uses an inefficient locking strategy and causes
concurrency problems.
You need to identify the following:
---
The sessions that wait for a resource The resource needed The sessions that block the resource
Which dynamic management view should you use?

A) sys.dm_exec_requests
B) sys.dm_os_wait_stats
C) sys.dm_os_waiting_tasks
D) sys.dm_tran_active_transactions

5. You are managing a SQL Server 2000 instance which includes a database called Products for Home.com. You decide to install a SQL Server 2008 instance for business. You should
make sure that third-party applications could run normally on both database instances, you could get the results by conforming to the listed conditions:1. The existed application environments keep common. 2. Minimal database administrative effort.Which is the correct answer?

A) Before setting the new application to utilize the new instance. You could install SQL Server 2008 as a named instance
B) You should update the SQL Server 2000 application to utilize SQL Server 2008.
C) You should update the SQL Server 2000 instance application to utilize SQL Server 2008 instance.
D) Before setting the new application to utilize the default instance. You could install SQL Server 2008 as a named instance

Solutions: