You are a database administrator for AIOTestKing.com. You discover that a SQL Server 2005 application performs slowly when you execute the following query. SELECT * FROM Orders WHERE OrderStatus = ‘InProgress’ The application executes this query as an ad hoc query. This query usually executes much more quickly. The OrderStatus column is indexed. Only a small number of the rows contain the value InProgress.
However, the OrderStatus column index is not being used, even though the index is optimal for this query. You need to improve the performance of this query. However, you cannot modify the application that executes the query. Your solution must also minimize the impact on end users. What should you do?

You are a database administrator for AIOTestKing.com. Users report that a SQL Server 2005 application is performing slowly. You check the SQL Server error log and discover that the server frequently runs out of memory to manage locks. The application designer informs you that dirty reads are acceptable. You need to resolve the problem indicated by the error log. What should you do?

You work as DBA at AIOTestKing.com. You administer a SQL Server 2005 computer named AIOTestKingA. Users regularly report that they receive error messages stating that the transaction log is full. When they get this message, their data changes fail. You notice that disk space on AIOTestKing A is limited. You need to configure AIOTestKing A to automatically prevent users’ data changes from failing due to full transaction logs. What should you do?

You work as DBA at AIOTestKing.com. You administer two SQL Server 2005 computers named AIOTestKing A and AIOTestKing B. AIOTestKing A and AIOTestKing B contain a copy of a database named Sales. The database is replicated between AIOTestKing A and AIOTestKing B by using transactional replication. A full backup of each database is performed every night.
Transaction log backups are performed every hour. Replication latency is typically less than two minutes. One afternoon, the Sales database on AIOTestKing A becomes corrupted.
You are unable to repair the database. The Sales database on AIOTestKing B is unaffected.
You need to return the Sales database on AIOTestKing A to normal operation as quickly as possible. You must ensure a minimum loss of data and minimal impact to users of either server. What should you do?

You are the administrator of a SQL Server 2005 computer named SQL1. SQL1 hosts a database named AssetTrack. You need to design a strategy to quickly perform point-in-time and page-level restores of the AssetTrack database. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

You are a database administrator for AIOTestKing.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 AIOTestKing.com. You administer a Microsoft Windows Server 2003 computer named AIOTestKingA. AIOTestKing A runs SQL Server 2005 and is a member of a Microsoft Active Directory domain named Contoso.com. The domain is configured with all of the default settings. Several members of the information security team have requested access to AIOTestKingA. The information security team uses the Microsoft Windows XP Professional, Linux, and Macintosh operating systems to perform vulnerability assessments. The information security team members ask you to create two new logon accounts for their use. They would like the logon accounts to have the following user names and passwords: User name\password for first account:
pentest\P@ssw0rdUser name\password for second account: hacktest\password You need to provide the information security team with the requested access to AIOTestKing A.
Which Transact-SQL script should you use?

You are a database administrator for AIOTestKing.com. Your SQL Server 2005 computer contains one user database that holds sales transaction information. Users report that the queries and stored procedures that they use every day are taking progressively longer to execute. You also notice that the amount of free disk space on the SQL Server computer is decreasing. You need to create a maintenance plan to correct the performance and storage problems. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

You are a database administrator for AIOTestKing.com. Developers need access to create, alter, and drop tables in your SQL Server 2005 database. A list of production tables is maintained in a table named Valid_Production_Tables in the database. You need to be able to automatically roll back any commands that drop or alter current production tables.
You need to do this while minimizing the processor resources being used. What should you do?

You are a database administrator for an electronics distribution company. You are documenting your company’s requirements for data quality. You need this information so that you can store part numbers, components, and stock levels from your company’s trading partners in your SQL Server 2005 environment. You need to determine the standards for data quality. You need to consult with the business analysts and other database administrators (DBAs) in your organization to do so. What are three possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose three.)