QUESTION 1
Certkingdom has hired you as their Database administrator. You create a database on named
Development on ABC-DB01 that hosts an instance of SQL Server 2005 Enterprise Edition.
You perform weekly maintenance and conclude that the Development database growing at about
100 MB on a monthly basis. The network users started complaining about poor performance of
queries run against the database.
There is 2GB RAM installed on DB01 and the database consumes 1.6 GB of RAM.
How would you determine whether additional RAM should be acquired for ABC-DB01?
A. You should consider monitoring the SQL Server: Memory Manager – Target Server Memory
(KB) Page Splits/sec counter in System Monitor.
B. You should consider monitoring the SQL Server: Buffer Manager counter in System Monitor.
C. You should consider monitoring the System – Processor Queue counter in System Monitor.
D. You should consider monitoring the SQL Server: Access Methods – Page Splits/sec counter in System Monitor.
Answer: B
Explanation: The SQL Server: Buffer Manager object is utilized to view the information related to
bottlenecks.
QUESTION 2
You create a database on ABC-DB01 that is running an instance of SQL Server 2005 Enterprise Edition.
Certkingdom recently suffered a power outage to the building which forces you to restart ABC-DB01
which now fails to start the SQL Server (MSSQLSERVER). Certkingdom wants you to troubleshoot
the service failure.
What must be done to determine the cause of the service start failure?
A. You should consider reviewing the Event Viewer logs listed below:
The Event Viewer Applications log.
The Event Viewer System logs.
Microsoft Notepad should be utilized to manually view the Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\ErrorLog file.
B. You should consider reviewing the Event Viewer logs listed below :
The Event Viewer Windows logs.
The Event Viewer Setup logs.
The Event Viewer Application log.
C. You should consider reviewing the Event Viewer logs listed below :
The Event Viewer Forwarded Event logs.
The Event Viewer Hardware events.
The Event Viewer Security log.
D. You should consider reviewing the Event Viewer logs listed below :
The Event Viewer Setup Events logs.
The Event Viewer Windows logs.
The Event Viewer Applications and Services logs.
Answer: A
QUESTION 3
Certkingdom has recently opened an office in Perth where you work as the database administrator.
The Certkingdom database infrastructure runs on computers utilizing SQL Server 2005 Enterprise
Edition. You create a Import database with a backup schedule configured in the table below:
The Imports database contains a table named incoming which was updated a week ago. During
the course of the day A network user informs you that a table has been dropped from the Imports
table at 16:10. Certkingdom wants the incoming table restored to the Imports database.
What must be done to restore the table using minimal effort and ensuring data loss is kept to a minimum?
A. You should consider restoring the database from the recent deferential backup.
B. You should consider having the differential of Monday and snapshot backup from Tuesday restored.
C. You should consider deletion of all differential and database snapshots except the recent backup.
D. You should consider having the database table recovered from the recent database snapshot.
Answer: A
QUESTION 4
Certkingdom hired you as the network database administrators. You create a database named
Customers on ABC-DB01 running an instance of SQL Server 2005 Enterprise Edition.
A custom application is used to access and query the database. The users recently reported that
the custom application experiences deadlock conditions constantly.
How would you determine which Server session ID is related to the deadlocks?
What must be done to observer the SQL Server session ID involved with the deadlock scenario?
A. You should consider monitoring the SQL Server Profiler to monitor Error and Warning events.
B. You should consider monitoring the SQL Server Profiler to monitor Lock Deadlock Chain events.
C. You should consider monitoring the SQL Server Profiler to monitor Objects.
D. You should consider monitoring the SQL Server Profiler to monitor performance.
Answer: B
QUESTION 5
You are the system administer of a SQL Server 2005 Enterprise Edition server named DB01 that
uses Windows Authentication mode. Certkingdom uses a custom developed application for running
queries against the database on DB01. Users complain that the custom application stops
responding. Yu notice that the CPU utilization at 100% capacity.
You then try to connect to DB01 by utilizing the SQL Server Management Studio but DB01 still
does not respond. Certkingdom wants you to connect to ABC-DB01 to determine the problem.
What should be done to successfully determine the problem?
A. You should consider utilizing the osql –L command from the command prompt.
B. You should consider utilizing the sqlcmd –A command from the command prompt.
You could additionally use SQL Server Management Studio to access Database Engine Query for
connecting to DB01 with the SQL Server Authentication mode.
C. You should consider utilizing the osql –E command from the command prompt.
D. You should consider utilizing the sqlcmd –N command from the command prompt.
E. You should consider utilizing the sqlcmd –R command from the command prompt.
Answer: B
Explanation: The sqlcmd –A command is utilized to ensure that a dedicated administrative connection is utilized.
Post your comments