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