70-431 Royal Pack Testengine pdf
100% Actual & Verified — 100% PASS
Unlimited access to the world's largest Dumps library! Try it Free Today!Get it now →
Exam Number/Code: 70-431
Exam name: Microsoft SQL Server 2005 Implementation & Maintenance
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing
Question No. 1
You work in San Francisco at the main office of your company. You are responsible for managing a SQL Server 2005 database. The sales department wants a report that compares customer activity in the previous quarter between the main office in San Francisco and the branch office in Paris. They want the data sorted by surname and first name. You restore a recent backup of the Paris database onto your server. You write queries to build the report, ordering the data by the Surname and FirstName columns. You review the data and notice that the customer list from the Paris database is sorted differently. The sales department needs the revised data within 15 minutes for a presentation. You need to implement the fastest possible solution that ensures that the data from both databases is sorted identically. What should you do?
A. Use the Copy Database Wizard to copy the data in the Paris database to a new database with the same collation as the San Francisco database.
B. Use the SQL Server Import and Export Wizard to copy the data from the Paris database into new tables, specifying the same collation as the San Francisco database.
C. Modify the format file to specify the same collation as the San Francisco database. Import the table again.
D. Modify the query on the Paris database to use the COLLATE setting in the ORDER BY clause. In the query, specify the same collation as the San Francisco database.
Question No. 2
A full backup of your database named DB1 is created automatically at midnight every day. Differential backups of DB1 occur twice each day at 10:00 and at 16:00. A database snapshot is created every day at noon. A developer reports that he accidentally dropped the Pricelist table in DB1 at 12:30. The last update to Pricelist occurred one week ago. You need to recover the Pricelist table. You want to achieve this goal by using the minimum amount of administrative effort. You must also minimize the amount of data that is lost. What should you do?
A. Restore the most recent backup into a new database named DB1bak. Apply the most recent differential backup. Copy the Pricelist table from DB1bak to DB1.
B. Delete all database snapshots except the most recent one. Restore DB1 from the most recent database snapshot.
C. Recover DB1 from the most recent backup. Apply the most recent differential backup.
D. Copy the Pricelist table from the most recent database snapshot into DB1.
Question No. 3
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.)
A. DBCC CHECKTABLE
B. ALTER INDEX REORGANIZE
C. UPDATE STATISTICS
D. CREATE STATISTICS
E. DBCC SHOW_STATISTICS
F. DBCC UPDATEUSAGE
Question No. 4
You are implementing transaction log shipping for a database named DB1 from a server named SQL1 to a server named SQL2. Because DB1 is 100 GB in size, it is too big to transfer over the network in a reasonable amount of time. You need to minimize the impact on the network while you initialize the secondary database. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A. Specify the simple recovery model for DB1.
B. Specify either the full or the bulk-logged recovery model for DB1.
C. Perform a complete backup of DB1 to portable media. Restore the secondary database from that backup; specify the RECOVERY option.
D. Perform a complete backup of DB1 to portable media. Restore the secondary database from that backup; specify the STANDBY option.
E. Before you activate transaction log shipping to the secondary database, execute the following statement on the primary server.BACKUP LOG DB1 WITH TRUNCATE_ONLY
Question No. 5
You are creating a SQL Server 2005 application that will store and manage legal documents. The original documents are stored as XML documents on a file server. The application will be used to insert the documents into the database. Later, the documents must be retrieved from the database. These documents must be identical to the originals. You need to design a table to store the document data. What should you do?
A. Store the XML in a column that has a data type of nvarchar(max).
B. Shred the XML and store it in a relational structure.
C. Store the XML in a column that has a data type of XML.
D. Store the XML in a column that has a data type of varchar(8000).
Question No. 6
Your company uses SQL Server 2005. Users report with increasing frequency that they receive deadlock error messages in an order processing application. You need to monitor which objects and SQL Server session IDs are involved when deadlock conditions occur. You want information about each participant in the deadlock. What should you do?
A. Trace the Lock:Timeout event by using SQL Server Profiler.
B. Observe the SQLServer:Locks - Number of Deadlocks/sec counter by using System Monitor.
C. Trace the Lock:Deadlock event by using SQL Server Profiler.
D. Trace the Lock:Deadlock Chain event by using SQL Server Profiler.
Question No. 7
Your company has a SQL Server 2005 computer. You have been assigned the task of retrieving information about a user who is currently logged in. You need to create a function that returns scalar information about the activity time for a particular user. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
A. Create a function that returns a list of values that represent the login times for the given user.
B. Create a function that returns a list of values that represent the people who have logged more hours than the current user has logged.
C. Create a function that returns a numeric value that represents the number of hours that a user has logged for the current day.
D. Create a function that returns a numeric value that represents the number of hours that a user has logged for the current month.
Question No. 8
Your SQL Server 2005 database contains a table that has 500 million rows of data. Some of the data is historical and some is current. You need to partition the data on a single server to increase performance and optimize maintenance. What should you do?
A. Implement horizontal partitioning.
B. Implement vertical partitioning.
C. Implement a raw partition.
D. Implement distributed partitioning.
Question No. 9
Your company uses SQL Server 2005. You are implementing a series of views that are used in ad hoc queries. The views are used to enforce your companys security policy of abstracting data. Some of these views perform slowly. You create indexes on those views to increase performance, while still maintaining the companys security policy. One of the views returns the current date as one of the columns. The view returns the current date by using the GETDATE() function. This view does not allow you to create an index. You need to create an index on the view. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A. Remove all deterministic function calls from within the view.
B. Remove all nondeterministic function calls from within the view.
C. Schema-bind all functions that are called from within the view.
D. Create the view and specify the WITH CHECK OPTION clause.
Question No. 10
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.)
A. In SQL Management Studio, right-click the CheckDB1 maintenance plan. On the context menu, click View History. See the record for the plan named CheckDB1.
B. In SQL Management Studio, right-click the Activity Monitor. On the context menu, click View Processes.
Look for a record that has "CheckDB1" in the Application column.
C. In Windows Explorer, navigate to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG folder. View the SQLAgent.out text file.
D. In Windows Explorer, navigate to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG folder. View the text file that has a name that starts with "CheckDB1_" followed by the execution year, date, and time.
Question No. 11
You work with a database named DB1, which is located on a SQL Server 2005 computer. You discover that a table scan on Table1 in DB1 causes a slow query. Table1 is a very large table that is used frequently. You ascertain that the query contains the following statement.
SELECT col1, col2 FROM table1 WHERE col3 = <value> You need to provide maximum query performance. Table1 must remain available to users. What should you do?
A. Update all statistics on Table1 in DB1.
B. Use the CREATE STATISTICS statement in DB1 to create missing statistics on col3 of Table1.
C. Set the priority boost server option to 1.
D. Execute the following statement.USE DB1GOCREATE INDEX index1 ON table1(col3) INCLUDE(col1,
col2) WITH (ONLINE = ON)GO
E. Execute the following statement.USE DB1GOCREATE INDEX index1 ON table1(col3, col2, col1)
Question No. 12
You configure a new SQL Server 2005 computer to use TCP/IP with all default settings. Your corporate policy requires that each server use a firewall. You find that you can connect to the SQL Server instance from the local computer. However, client computers cannot connect to the SQL Server instance. You need to identify the most likely cause of the connection issues. What should you do first?
A. Ensure that port 1433 is open in your firewall.
B. Ensure that port 443 is open in your firewall.
C. Ensure that client computers connect by using Shared Memory protocol.
D. Ensure that the server is not paused.
Question No. 13
You are creating a stored procedure that will delete data from the Contact table in a SQL Server 2005 database. The stored procedure includes the following Transact-SQL statement to handle any errors that occur. BEGIN TRY BEGIN TRANSACTION DELETE FROM Person.Contact WHERE ContactID = @ContactID COMMIT TRANSACTIONEND TRYBEGIN CATCH DECLARE @ErrorMessage nvarchar(2000) DECLARE @ErrorSeverity int DECLARE @ErrorState int SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE() RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH; You test the stored procedure and discover that it leaves open transactions. You need to modify the stored procedure so that it properly handles the open transactions. What should you do?
A. Add a COMMIT TRANSACTION command to the CATCH block.
B. Remove the COMMIT TRANSACTION command from the TRY block.
C. Add a ROLLBACK TRANSACTION command to the CATCH block.
D. Add a ROLLBACK TRANSACTION command to the TRY block.
Question No. 14
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. RESTORE LABELONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak
B. RESTORE HEADERONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak
C. RESTORE VERIFYONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak
D. RESTORE DATABASE MSDBFROM DISK = N\\Server1\Backup\DB_Backup.bak
Question No. 15
You manage a SQL Server 2005 computer that was installed using default settings. After a power failure, the SQL Server (MSSQLSERVER) service on your database server does not start. You need to find out the cause of the problem. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)
A. In Event Viewer, view the system log.
B. In Event Viewer, view the application log.
C. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog.1 file.
D. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog file.
E. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAgent.out file.
Question No. 16
You manage a database named DB1, which is located on a SQL Server 2005 computer. You receive a warning that the drive on which the DB1 log file is located is near capacity.
Although the transaction log is backed up every five minutes, you observe that it is steadily growing. You think that an uncommitted transaction might be the cause and you want to investigate. You need to identify both the server process ID and the start time of the oldest active transaction in DB1. What should you do?
A. Connect to the DB1 database. Execute DBCC OPENTRAN. View the SPID and Start time rows.
B. Connect to the master database. Execute DBCC OPENTRAN. View the SPID and Start time rows.
C. In SQL Server Management Studio, open the Activity Monitor. Select the Process Info page and apply the following filter settings. Database = DB1 Open Transactions = YesView the Process ID and Last Batch columns.
D. Open a query window. Connect to the master database. Execute the following statement.SELECT TOP 1 spid, last_batch FROM sys.sysprocesses WHERE dbid = db_id('DB1') AND open_tran > 0 ORDER BY last_batch