SQL Server Question Answer


Hello friends in this post we are going to discuss about SQL Server L1 Multiple choice question | SQL Server L1 Objective type questions | SQL Server L1 Multiple choice questions | SQL Server MCQ Question answers | SQL Server TrendNXT Myskillz Wipro Question answers

  Question:  1
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
__ number of CPUs are supported by SQL Server 2005 Standard Edition
  A) 1
  B) 2
  C)  4
  D) Max Supported by OS
  Answer: C)  4

  Question:  2
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
All system tables are allocated in the _.
  A) Secondary filegroup
  B) Primary filegroup
  C)  User-defined filegroups
  D) None of the Options
  Answer: B) Primary filegroup

  Question:  3
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
Automatic recovery in the pages is available in
  A) 2008
  B) 2005
  C)  6.5
  D) 7
  Answer: A) 2008

  Question:  4
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
By default files not specifically assigned to any file group reside in __
  A) Primary file group
  B) Secondary file group
  C)  User-defined file group
  D) Log file group
  Answer: A) Primary file group

  Question:  5
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
Collation setting does not include which of the following?
  A) character set
  B) sort order
  C)  locale-specific setting
  D) None of the above
  Answer: D) None of the above

  Question:  6
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
Collation setting in SQL Server can not be set at the ___ level.
  A) Server
  B) Database
  C)  Table
  D) Column
  Answer: C)  Table

  Question:  7
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
Default port for SQL Server
  A) Port 1432
  B)  Port 1433
  C)  Port 1434
  D) Port 1435
  Answer: B)  Port 1433

  Question:  8
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
Default value of Max Worker Thread in SQL Server
  A) 255
  B) 256
  C)   0
  D) 200
  Answer: C)   0

  Question:  9
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
Distributed partitioned views is supported by which of the following editions of SQL Server. I. SQL Server Personal Edition II. SQL Server Standard Edition III. SQL Server Enterprise Edition
  A) I II
  B) II III
  C)  III
  D) I, II III
  Answer: C)  III

  Question:  10
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
DMV stands for?
  A) Data Management View
  B) Department of Motor Vehicle
  C)  Dynamic Management View
  D) Database Monitoring View
  Answer: C)  Dynamic Management View

  Question:  11
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
FILLFACTOR in an Index is applied at the _ level
  A) Intermediate
  B) root
  C)   leaf
  D) none
  Answer: C)   leaf

  Question:  12
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
For an in-place upgrade to SQL Server 2005, which version(s) of SQL Server can used? I. SQL Server 2005 RTM or later II. Only SQL Server 2000 SP 4 or later III. Only SQL Server 2000 SP 3 or later IV. SQL Server 7.0 SP 4 or later V. SQL Server 6.5 SP 4 or later
  A) I, II, III and IV
  B) I, III IV
  C)  I, II IV
  D) II, III, IV V
  Answer: B) I, III IV

  Question:  13
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
How can we configure SQL to allow remote connections
  A) SAC
  B) DAC
  C)  sp_configure
  D) Both 1 3
  Answer: D) Both 1 3

  Question:  14
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
how do you check the tcp port in sql 2000
  A) client network utility
  B) server network utility
  C)  sql configuration manager
  D) all of the above
  Answer: B) server network utility

  Question:  15
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
How many cluster index we can create in the table
  A) 249
  B) 1
  C)  0
  D) 146
  Answer: B) 1

  Question:  16
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
How many data and log file will be created by default if we use CREATE DATABASE command
  A) 1 mdf file, 1 ndf file and 1 ldf file
  B) 1 mdf file and 1 ldf file
  C)   1 mdf file and 2 ldf file
  D) 1 ndf file and 1 ldf file
  Answer: C)   1 mdf file and 1 ldf file

  Question:  17
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
How many databases can be created on each instance of SQL Server?
  A) 32767
  B) 32766
  C)  32747
  D) 32787
  Answer: A) 32767

  Question:  18
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
For the backup job to be successful, the SQL Server service account on the primary server instance in a logshipping setup and the proxy account of the backup job must have __ permissions to the backup directory.
  A) read
  B) write
  C)  read/write
  D) None of the above
  Answer: C)  read/write

  Question:  19
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
A company has several local stores. Central office needs data twice a month. What kind of replication will you use?
  A) Snapshot
  B) Merge
  C)  Transaction
  D) Transaction replication with immediate-updating subscribers.
  Answer: A) Snapshot

  Question:  20
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Automatic failover is not supported in
  A) Mirroring
  B)  Logshipping
  C)  Clustering
  D) All
  Answer: B)  Logshipping

  Question:  21
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Can we compress T-Log backup in Log Shipping
  A)  Possible only in SQL 2008
  B) Possible only in SQL 2005
  C)  Possible only in SQL 2000
  D) Possible in All Versions
  Answer: A)  Possible only in SQL 2008

  Question:  22
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Can we configure Log Shipping using T-SQL?
  A) Yes
  B)  No
  C)  Only in 2005 version
  D) Only in 2008 version
  Answer: B)  No

  Question:  23
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Can we configure Mirroring on DB if it is already configured for Log Shipping
  A)  Yes
  B) No
  C)  Possible in SQL 2005
  D) Possible in SQL 2008
  Answer: A)  Yes

  Question:  24
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Can we run any DML or DDL statement on Secondary Database
  A) Can run only DML Statement
  B) Can run only DDL statement
  C)  Yes
  D)  No
  Answer: D)  No

  Question:  25
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Database Snapshot
  A)  is the backup and update format of the database
  B) is the read only static views of the database
  C)  is the temporary database
  D) is system database
  Answer: B) is the read only static views of the database

  Question:  26
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Database state on secondary server if it is configured for logshipping
  A)  Restoring or Read Only
  B) Online
  C)  No Recovery
  D) Multi User
  Answer: A)  Restoring or Read Only

  Question:  27
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
For the copy job, in a log shipping setup, to be successful, the proxy account of the copy job must have ___ access to the copy directory
  A) read
  B) write
  C)  delete
  D) None of the above
  Answer: B) write

  Question:  28
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
For the copy job, in a logshipping setup, to be successful, the proxy account of the copy job must have __ permissions to the backup directory
  A) read
  B) write
  C)  delete
  D) None of the above
  Answer: A) read

  Question:  29
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
For the restore job to be successful, the SQL Server service account on the secondary server instance in a logshipping setup and the proxy account of the restore job must have _ access to the copy directory
  A) read
  B) write
  C)  read/write
  D) None of the above.
  Answer: C)  read/write

  Question:  30
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
How many jobs will be created while configuring the logshipping
  A) 3
  B) 4
  C)  5
  D) 6
  Answer: C)  5

  Question:  31
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
In logshipping the recovery model of Primary database should not be in
  A) Full
  B) Bulk logged
  C)   Simple
  D) None
  Answer: C)   Simple

  Question:  32
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
In order to setup logshipping a user should be a member of _ server role on each instance involved in Log shipping
  A) serveradmin
  B) sysadmin
  C)  setupadmin
  D) backupadmin
  Answer: B) sysadmin

  Question:  33
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
In replication, what is each set of the source data that is replicated from the source server called?
  A) Publisher
  B) Article
  C)  Data Source
  D) Magazine
  Answer: B) Article

  Question:  34
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
In Replication: you have finance db there are many publications on it. accounting group subscribes to one of the publications, but they want to subscribe only to one article. How can it be done with min admin steps?
  A) Filter on the publication so that they can subscribe to one article and use pull subs.
  B) Filter on the publication same as above but with push subs.
  C)  Create a new publication with the above article.
  Answer: C)  Create a new publication with the above article.

  Question:  35
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
In SQL Server 2005, how are the primary and secondary systems in log shipping coupled?
  A) tightly
  B) loosely
  C)  No coupling
  D) noosed
  Answer: B) loosely

  Question:  36
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
What factors are necessary to ensure minimal logging? I. Table not replicated II. TABLOCK hint used III. Recovery model set to Bulk-Logged IV. Logshipping disabled
  A) I, II III only
  B) I II only
  C)  II IV only
  D) I III only
  Answer: A) I, II III only

  Question:  37
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
Which counter is helpful to find the number of connection in the SQL Server
  A) System:user connections
  B) Processor:user connections
  C)  SQL Server: General Statistics: User Connections
  D) Database:user connections
  Answer: C)  SQL Server: General Statistics: User Connections

  Question:  38
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
_ is most appropriate to implement the lowest level of granular locking in SQL Server
  A) Database
  B) Table
  C)  Record
  D) Key
  Answer: C)  Record

  Question:  39
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
_ troubleshoots problems in SQL Server by capturing events on a production system and replaying them on a test system.
  A) SQL Interface
  B) SQL Audit Events
  C)  SQL Profiler
  D) SQL Audit Utility
  Answer: C)  SQL Profiler

  Question:  40
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
_ in SQL Server 2005 is a replacement of DTS feature in SQL Server 2000
  A) SSIS
  B) SSAS
  C)  SSRS
  D) Configuration Manager
  Answer: A) SSIS

  Question:  41
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
_ debugs Transact-SQL statements and stored procedures.
  A) SQL Audit Event
  B) SQL Interface
  C)  SQL Profiler
  D) Transact-SQL debugger
  Answer: D) Transact-SQL debugger

  Question:  42
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
A database originally created at size of 500M, now grows to 1G, the data is 200M, to reclaim 600M for OS:
  A)  DBCC SHRINKDATABSE (database, 50)
  B) DBCC SHRINKDATABSE (database, 400)
  C)  DBCC SHRINKFILE (database, 400)
  D) DBCC SHRINKFILE (database, 50)
  Answer: A)  DBCC SHRINKDATABSE (database, 50)

  Question:  43
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
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.
  Answer: D)  Copy the Pricelist table from the most recent database snapshot into DB1.

  Question:  44
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
A new employee needs access to a SQL Server 2005 database that is located on a server named SQL1. You create a login named ajones by using the following Transact-SQL statement. CREATE LOGIN ajones WITH PASSWORD = SQLServer$1 The new employee reports that when he logs in, he receives the following error message: Login faile The user is not associated with a trusted SQL Server connection. You need to resolve the error and allow the new employee to gain access to SQL1. What should you do?
  A)  Change the SQL Server security mode from Windows Authentication mode to SQL Server and Windows Authentication mode.
  B)  Change the SQL Server security mode from SQL Server and Windows Authentication mode to Windows Authentication mode.
  C)   Ensure that the login name is created with square brackets ([]).
  D)  Give the login access to a specific database by using the CREATE USER Transact-SQL statement.
  Answer: A)  Change the SQL Server security mode from Windows Authentication mode to SQL Server and Windows Authentication mode.

  Question:  45
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
A query which loads data on a Production Server runs. Transaction Log is approaching 500MB, less than 50% done. 3GB HD was free on the drive. DB growth was auto. The query blocks out other users. How could you give the general user quick access to the database for use.
  A) Kill update process
  B)  Let the update process run until it finishes
  C)  Kill the blocked processes
  D) Stop and restart the SQL server
  Answer: B)  Let the update process run until it finishes
 
Port number for database mirroring
5022
5023
5024
1434

Ans: 5022

Which of these is only used for DB design?
Toad
DB artisian
Erwin
None

Ans:

SQL job are stored in which DB?
TempDB
Master
Msdb
Model

Ans: msdb

How do we know % of back up completed
Inputbuffer (pid)

Outbuffer (pid)

Is used to comment a single line

%

$
^

Ans: —

select 5+null from dual
5
0
Null
Error
Ans null
ANSI SQL STANDARDS SQL 86, 89, 92,99,2003,2006,2008,2011
You may get question on this
How many sub queries can be there in a nested query

Ans 32

Which of these is true
1.Select can be used with stored procedure and function
2.Select can be used with stored procedure , but not with function
3.Select cannot be used with stored procedure but can be using with function
4.Select cannot be used with stored procedure neither with function
Ans:
Start sql server in single user mode

Ans : Net start sqlserver  –m

Start sql server in minimal config mode

Ans: net start sqlserver –f

  Question:  46
  UCF:  SQL 2.1
  Topic: WINDOWS_OS_5
/3GB Switch needs to be added in
  A) win.ini
  B)  boot.ini
  C)  windows.ini
  D) sql.ini
  Answer: B)  boot.ini

  Question:  47
  UCF:  SQL 2.1
  Topic: WINDOWS_OS_5
_ are the key identifiers to for accounts in Windows
  A) SPID
  B) SID
  C)  UID
  D) GUID
  Answer: B) SID

  Question:  48
  UCF:  SQL 2.1
  Topic: WINDOWS_OS_5
AWE is in 64 bit
  A) Not Available
  B) Automatically enabled
  C)  Manually
  D) both
  Answer: B) Automatically enabled

  Question:  49
  UCF:  SQL 2.1
  Topic: WINDOWS_OS_5
Built-in local group cannot be created or deleted. It can only be modified.
  A) TRUE
  B) FALSE
  Answer: A) TRUE

  Question:  50
  UCF:  SQL 2.1
  Topic: WINDOWS_OS_5
How much memory is supported by Windows 2003 Datacenter Edition when /PAE switch is enabled
  A) 16 GB
  B) 32 GB
  C)  24 GB
  D)  64 GB

  Answer: D)  64 GB

What is the recommended page file size in windows

Ans 1.5 x RAM

SQL 2.2

  Question:  1
  UCF:  SQL 2.2
  Topic: DBA_17
Create a predefined list of data sources that users can select at run time. When a user requests a report, the report provides a list of data sources. The user must select which data source to use prior to running the report. To add a data source selection list to a report, you use an expression What do you call this type of connection in SSRS?
  A) Dynamic data source connection
  B) Temporary data source connection
  C)  Linked data source connection
  D) Remote data source connection
  Answer: A) Dynamic data source connection

  Question:  2
  UCF:  SQL 2.2
  Topic: DBA_17
James works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer named bwSQL1 with the default collation settings. He installs SQL server 2005 on another computer named bwSQL2 with different collation settings. What will he do to configure bwSQL2 with the same collation settings as bwSQL1?
  A)  Reinstall SQL server 2005 on bwSQL2 with the same collation settings as bwSQL1.
  B) Use the fn_get_sql function to change the collation settings.
  C)  Use the SQL Server properties dialog box in SSMS to change the collation settings.
  D) Use the fn_helpcollation function to change the collation settings.
  Answer: A)  Reinstall SQL server 2005 on bwSQL2 with the same collation settings as bwSQL1.

  Question:  3
  UCF:  SQL 2.2
  Topic: DBA_17
What is the configuration setting, Execution Account used for in the Reporting Services Configuration Tool?
  A) Used for connecting to the Report Server DB
  B) Used for connecting and executing against ReportServer DB
  C)   used for remote connections during scheduled operations or when user credentials are not available
  D)  Used for connecting and executing against Reportservertempdb database
  Answer: C)   used for remote connections during scheduled operations or when user credentials are not available

  Question:  4
  UCF:  SQL 2.2
  Topic: DBA_17
What is the difference between Single user and Restricted User modes?
  A) Only one user can connect to the SQL Server in Single User mode All the members of db_owner , dbcreator , sysadmin groups can connect in Restricted User mode
  B) Only db_owners can connect to the SQL Server in Single User mode All the members of db_owner , dbcreator , sysadmin groups can connect in Restricted User mode
  C)  Choice A B
  D) Only one user can connect to the SQL Server in Restricted User mode All the members of db_owner , dbcreator , sysadmin groups can connect in Single User mode
  Answer: A) Only one user can connect to the SQL Server in Single User mode All the members of db_owner , dbcreator , sysadmin groups can connect in Restricted User mode

  Question:  5
  UCF:  SQL 2.2
  Topic: DBA_17
What type of Operating Mode that the Database Mirroring is working which has Safety set to Full having witness Server and Synchronous data transfer happens?
  A) A. High Protection
  B) High Availability
  C)  High Performance
  D) High Scalability
  Answer: B) High Availability

  Question:  6
  UCF:  SQL 2.2
  Topic: DBA_17
Which of the following is true when using SSIS Package Migration Wizard to migrate DTS packages to SSIS packages in sql server 2005?
  A) does not migrate transaction settings
  B) Migrates ActiveX scripts
  C)  Migrates complex DTS tasks
  D)  Migrates Dynamic Properties Tasks
  Answer: A) does not migrate transaction settings

  Question:  7
  UCF:  SQL 2.2
  Topic: DBA_17
Andrew works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer. The computer has a database named Purchase. Andrew creates a table named products. He writes some queries that are used to insert and update data in the table and delete data from the table. He now wants to check the performance of the queries. Which of the following will he use to accomplish the task?
  A) SP_UPDATESTATS
  B) SP_AUTOSTATS
  C)   SYS.DM_EXEC_QUERY_STATS
  D) SYS.DM_DB_INDEX_PHYSICAL_STATS
  Answer: C)   SYS.DM_EXEC_QUERY_STATS

  Question:  8
  UCF:  SQL 2.2
  Topic: DBA_17
Andrew works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer. The computer has a database named Orders. Andrew wants to maintain two copies of the database such that if insert, update, and delete operations are performed on the principal database, it should also apply on the other copy of the database. Which of the following processes will be applied by Andrew to accomplish the task?
  A) Failover clustering
  B) RAID installation
  C)  Replication
  D)  Database mirroring
  Answer: D)  Database mirroring

  Question:  9
  UCF:  SQL 2.2
  Topic: DBA_17
________ plays an important role in the performance of query
  A) Database Engine
  B) Query Parser
  C)  Query Optimizer
  D) File Manager
  Answer: C)  Query Optimizer

  Question:  10
  UCF:  SQL 2.2
  Topic: DBA_17
A full backup of your database named sales is created automatically at 23:00 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 orders table in sales at 12:30. The last update to orders occurred one week ago. You need to recover the orders 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 salesnew. Apply the most recent differential backup. Copy the orders table from salesnew to sales.
  B) Delete all database snapshots except the most recent one. Restore sales from the most recent database snapshot.
  C)  Recover sales from the most recent backup. Apply the most recent differential backup.
  D) Copy the orders table from the most recent database snapshot into sales.
  Answer: D) Copy the orders table from the most recent database snapshot into sales.

  Question:  11
  UCF:  SQL 2.2
  Topic: DBA_17
A power failure occurs on the storage area network (SAN) where your SQL Server 2005 database server is located. You need to check the allocation as well as the structural and logical integrity of all databases, including their system catalogs. What should you do?
  A) Execute DBCC CHECKFILEGROUP for each filegroup.
  B) Execute DBCC CHECKCATALOG.
  C)  Execute DBCC CHECKDB.
  D)  Execute DBCC CHECKTABLE for each table.
  Answer: C)  Execute DBCC CHECKDB.

  Question:  12
  UCF:  SQL 2.2
  Topic: DBA_17
Andrew works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer. The computer has a database named Purchase. Andrew creates a table named products. He writes some queries that are used to insert and update data in the table and delete data from the table. He now wants to check the performance of the queries. Which of the following will he use to accomplish the task?
  A) A.SP_UPDATESTATS
  B) B.SP_AUTOSTATS
  C)  SYS.DM_EXEC_QUERY_STATS
  D) D.SYS.DM_DB_INDEX_PHYSICAL_STATS
  Answer: C)  SYS.DM_EXEC_QUERY_STATS

  Question:  13
  UCF:  SQL 2.2
  Topic: DBA_17
Andrew works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer. The computer has a database named Sales. The company has its office in an area that is prone to power failure. Andrew has to take a backup that includes all the filegroups and database objects. Which of the following types of backups will he take to accomplish the task?
  A) A.Transaction Logbackup
  B) B.Partial backup
  ams)C)  Full database backup
  D) D.File backup
  Answer: ams)C)  Full database backup

  Question:  14
  UCF:  SQL 2.2
  Topic: DBA_17
Andrew works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer. The computer has a database named Sales. The company has several sales outlets, each having a SQL server 2005 computer. Andrew has to replicate data between the main office and its sales outlets. The database contains several agent profiles to monitor the replication. He wants to modify one of the agent profiles. Which of the following stored procedures will he use to accomplish the task?
  A) A.sp_alter_agent_profile
  B) sp_help_agent_profile
  C)  C.sp_add_agent_profile
  D) D.sp_modify_agent_profile
  Answer: B) sp_help_agent_profile

  Question:  15
  UCF:  SQL 2.2
  Topic: DBA_17
As the database owner, you grant Franz permissions to create views and create stored procedures in the Finance database. France creates a stored procedure that does an update on the dbo.prices table. He creates a view that selects price information for a report. He grants Suzanne SELECT permissions on the view and EXECUTE permissions on the stored procedure. What must be done so that Suzann e can obtain the results by using the view and stored procedure?
  A) A.You must grant Suzanne SELECT permissions on the view and the stored procedure.
  B) B.Franz must grant Suzanne SELECT permissions on the Prices table.
  C)  You must grant Suzanne SELECT and UPDATE permissions on the Prices table.
  D) D.Franz must grant Suzanne EXECUTE permissions on the stored procedure and SELECT permissions on the Prices table.
  Answer: C)  You must grant Suzanne SELECT and UPDATE permissions on the Prices table.

  Question:  16
  UCF:  SQL 2.2
  Topic: DBA_17
Can you run a differential backup on a database in the Simple recovery mode?
  A) Yes
  B) No
  Answer: A) Yes

  Question:  17
  UCF:  SQL 2.2
  Topic: DBA_17
Collation setting does not include which of the following?
  A) character set
  B) sort order
  C)  locale-specific setting
  D) None of the above
  Answer: D) None of the above

  Question:  18
  UCF:  SQL 2.2
  Topic: MT_15
What are the databases that will be created when you install SQL Server Reporting Services
  A) Tempdb, reportserver
  B) Reportserver, Reportservertempdb
  C)  Reportservertempdb, Reportserver, Tempdb
  D) None
  Answer: B) Reportserver, Reportservertempdb

  Question:  19
  UCF:  SQL 2.2
  Topic: MT_15
What does the waittype column, RESOURCE_QUEUE indicates
  A) You may notice this waittype during the long-running I/O-bound operations such as creating, expanding, or dropping a database file
  B) This waittype indicates that a batch has been received from a client application but that there are no worker threads that are available to service the request
  C)  These waittypes are all involved in parallel query execution
  D) This is an ordinary idle state for background threads in SQL Server
  Answer: D) This is an ordinary idle state for background threads in SQL Server

  Question:  20
  UCF:  SQL 2.2
  Topic: MT_15
What is the command used to update query optimization statistics?
  A) UPDATE STATISTICS
  B) STATS_UPDATE
  C)  DBCC UPDATE_STATISTICS
  D) SP_UPDATESTATISTICS
  Answer: A) UPDATE STATISTICS

  Question:  21
  UCF:  SQL 2.2
  Topic: MT_15
Cannot generate SSPI context error message, when connect to local SQL Server outside domain
  A) Connection configuration causes network library to choose TCP/IP provider and The hosting machine of SQL Server is connected to a network, including home network or dialup connection, but it is disconnected from its domain
  B) Invalid Service pack installation.
  C)  Port number is not configuered
  D) SQL Server has too many connections
  Answer: A) Connection configuration causes network library to choose TCP/IP provider and The hosting machine of SQL Server is connected to a network, including home network or dialup connection, but it is disconnected from its domain

  Question:  22
  UCF:  SQL 2.2
  Topic: MT_15
You are the administrator of a SQL Server 2000 computer. Each night you need to send a copy of the server s msdb database in an e-mail message to the administrator of another SQL Server computer. You create a job that contains the following steps: Detach the msdb database Send the database to the administrator in an e-mail message Reattach the msdb database You test the job, and it fails on the first step. You need to ensure that the msdb database is mailed to the administrator every night. What should you do?
  A) A.Ensure that the SQLServerAgent service is running under a user account that has database owner access to the msdb database
  B) B.Delete the first and last steps in the job
  C)  Configure the job to back up the msdb database to a temporary file Send the file to the administrator in an e-mail message
  D) D.Insert a new job step before the first step Configure the new step to terminate all processes that use the Msdb database
  Answer: C)  Configure the job to back up the msdb database to a temporary file Send the file to the administrator in an e-mail message

  Question:  23
  UCF:  SQL 2.2
  Topic: MT_15
A Company uses a SQL Server 2005 database. A full backup of the database is made each night. Transaction log backups are made each morning and each afternoon. You notice that the database transaction log file grows very large throughout the day. You need to minimize the size of the transaction log file. You also need to minimize data loss in the event of a failure. What should you do?
  A) Decrease the amount of time between transaction log backups.
  B) Configure the database to use the simple recovery model.
  C)  Configure the RECOVERY INTERVAL option to be a smaller value.
  D) Run DBCC SHRINKFILE on the transaction log file.
  Answer: A) Decrease the amount of time between transaction log backups.

  Question:  24
  UCF:  SQL 2.2
  Topic: MT_15
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 new database and apply the most recent differential backup
  B) Recover DB1 from most recent backup and apply the most recent Differential backup
  C)  Delete the database snapshots except the most recent one and restore DB1 from most recent database snapshot
  D) Copy the pricelist table from the most recent database snapshot into DB1
  Answer: D) Copy the pricelist table from the most recent database snapshot into DB1

  Question:  25
  UCF:  SQL 2.2
  Topic: MT_15
All database users belong to which role?
  A) everyone
  B) db_public
  C)  public
  D) db_datareader
  Answer: C)  public

  Question:  26
  UCF:  SQL 2.2
  Topic: MT_15
Andrew works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer. The computer has a database named Sales. The company has several sales outlets, each having a SQL server 2005 computer. Andrew has to replicate data between the main office and its sales outlets. The database contains several agent profiles to monitor the replication. He wants to modify one of the agent profiles. Which of the following stored procedures will he use to accomplish the task?
  A) sp_alter_agent_profile
  B)  sp_help_agent_profile
  C)  sp_add_agent_profile
  D) sp_modify_agent_profile
  Answer: B)  sp_help_agent_profile

  Question:  27
  UCF:  SQL 2.2
  Topic: MT_15
As SQL Server 2000 DBA, you should grant select permission to the Products table for all members of the Management department. There are Admin, Management, Tech and Sales database roles. Ravi has recently been transferred to the Management department from the Tech department and added to the Management role. The Tech role has been denied access to the Products table. All employees from the Management department have select permission to the Products table, but Ravi does not have. You should grant select permission to the Products table for Ravi. What should you do?
  A) GRANT SELECT ON Products TO Ravi
  B) sp_addrolemember db_owner , Ravi
  C)  sp_droprolemember Tech , Ravi
  D) sp_addrolemember Admin , Ravi
  Answer: C)  sp_droprolemember Tech , Ravi

  Question:  28
  UCF:  SQL 2.2
  Topic: MT_15
Backup strategy involves making full backup on every Saturday night, differential backup every day and transaction log backup every hour. The database crashes. How do you restore?
  A)  Restore the latest full backup
  B) Restore all differential backups in sequence
  C)  Restore all differential backups after the full backup in sequence
  D) Restore all transaction log backups
  Answer: A)  Restore the latest full backup

  Question:  29
  UCF:  SQL 2.2
  Topic: MT_15
Company s financial forecasting application uses a SQL Server 2005 database that enables business users to run both ad hoc and predefined queries. You suspect that some queries consume an excessive amount of server resources. You need to identify which queries consume the most resources. You want to achieve this goal as quickly as possible. What should you do?
  A) Use the DBCC INPUTBUFFER command.
  B) Use the sys.dm_exec_query_stats dynamic management view (DMV).
  C)  Use the sqldiag utility.
  D) Use the SHOWPLAN session option.
  Answer: B) Use the sys.dm_exec_query_stats dynamic management view (DMV).

  Question:  30
  UCF:  SQL 2.2
  Topic: MT_15
Create job that performs several maintenance tasks on the server s databases. You want the job to run whenever the server s processor utilization falls below 5 percent.You create a new schedule for the job and specify the Start whenever the CPU(s) become idle option. After several days, you notice that the job has never executed although the server s processor utilization has fallen below 5 percent several times. What should you do?
  A) Modify SQL Server Agent properties and specify a smaller idle time.
  B) Modify SQL Server Agent properties and specify a larger idle time.
  C)  Write a stored procedure that executes the job whenever the @@IDLE system variable is less than 5.
  D) Write a stored procedure that executes the job whenever the @@IDLE system variable is greater than 1.
  Answer: A) Modify SQL Server Agent properties and specify a smaller idle time.

  Question:  31
  UCF:  SQL 2.2
  Topic: MT_15
What SQL Server edition is require for setting up a server as log shipping monitor
  Enterprise
  Developer
  Standard
  Any of these
  Answer: Any of these

  Question:  32
  UCF:  SQL 2.2
  Topic: MT_15
Which database recovery model does not allow condifuring Log Shipping
  Simple
  Full
  Bulk-Logged
  all of them
  Answer: Simple

  Question:  33
  UCF:  SQL 2.2
  Topic: NETWORKING_5
__ Protocol is preferred for remote connections as it opens fewer ports in the firewall
  A) Named Pipes
  B) Shared Memory
  C)  TCP/IP
  D) None of the above
  Answer: C)  TCP/IP

  Question:  34
  UCF:  SQL 2.2
  Topic: NETWORKING_5
Microsoft SQL Server cannot service several protocols at the same time
  A) TRUE
  B)  False
  Answer: B)  False

  Question:  35
  UCF:  SQL 2.2
  Topic: NETWORKING_5
If your company s Web site is linked to a SQL Server 2005 server which performance counter would help you capture the sudden increase in Network Interface
  A)  Network Interface: Bytes Total/sec counter
  B) Network Interface: Output Queue Length
  C)  SQL Server: Buffer Manager: Buffer Cache Hit Ratio
  D) None of the above
  Answer: A)  Network Interface: Bytes Total/sec counter

  Question:  36
  UCF:  SQL 2.2
  Topic: NETWORKING_5
A unique, 12-digit (48-bit), hexadecimal number that the network interface card (NIC)   manufacturer burns into a computer s network interface card is called
  A) http address
  B) IP address
  C)  MAC Address
  D) FTP address
  Answer: C)  MAC Address

  Question:  37
  UCF:  SQL 2.2
  Topic: NETWORKING_5
Before any data can be transmitted through the TCP protocol, a reliable connection must be established. What is the process that TCP uses to establish this connection called
  A) network handshake
  B) windows handshake
  C)  two way handshake
  D) three way handshake
  Answer: D) three way handshake

  Question:  38
  UCF:  SQL 2.2
  Topic: REPLICATION_13
In SQL Server 2005, log_reuse_wait_desc column in sys.databases system table is showing as Replication and you are getting alerts that your Tlog is full, how do you resolve this?
  A) Update the column to Nothing and take Tlog backup
  B) Temporarily stop/disable the Replication, increase the Log file size
  C)  Restart SQL Service
  D) Take Tlog backup
  Answer: B) Temporarily stop/disable the Replication, increase the Log file size

  Question:  39
  UCF:  SQL 2.2
  Topic: REPLICATION_13
Which type of replication allows for reads and modifications to be performed on any of the databases participating in replication?
  A) Transactional Replication
  B) Snapshot Replication
  C)  None
  D) Peer to Peer replication
  Answer: D) Peer to Peer replication

  Question:  40
  UCF:  SQL 2.2
  Topic: REPLICATION_13
A distributed database can use which of the following strategies?
  A) Totally centralized at one location and accessed by many sites
  B) Partially or totally replicated single location sites
  C)  Partitioned into segments into single site
  D) none
  Answer: A) Totally centralized at one location and accessed by many sites

  Question:  41
  UCF:  SQL 2.2
  Topic: REPLICATION_13
A distributed database has which of the following advantages over a centralized database
  A) Software cost
  B) Software complexity
  C)  Slow Response
  D) Modular growth
  Answer: D) Modular growth

  Question:  42
  UCF:  SQL 2.2
  Topic: REPLICATION_13
A homogenous distributed database is which of the following?
  A) The same DBMS is used at each location and data are not distributed across all nodes.
  B) The same DBMS is used at each location and data are distributed across all nodes
  C)  A different DBMS is used at each location and data are not distributed across all nodes
  D) A different DBMS is used at each location and data are distributed across all nodes
  Answer: B) The same DBMS is used at each location and data are distributed across all nodes

  Question:  43
  UCF:  SQL 2.2
  Topic: REPLICATION_13
Error Message: Can not update column as it s used in replication ? What could be the probable cause?
  A) column not found in the replication DB
  B) Column does not have insert permission
  C)  Replication Agent is not working
  D) If this table is no longer involved in replication, there is a stored procedure to update the object s metadata and it is called sp_MSunmarkreplinfo (it takes a tablename as a parameter). Running sp_removedbreplication can be used to remove all traces of replication in the subscriber database, but obviously must only be done if this database is no longer needed as a publisher or subscriber.
  Answer: D) If this table is no longer involved in replication, there is a stored procedure to update the object s metadata and it is called sp_MSunmarkreplinfo (it takes a tablename as a parameter). Running sp_removedbreplication can be used to remove all traces of replication in the subscriber database, but obviously must only be done if this database is no longer needed as a publisher or subscriber.

  Question:  44
  UCF:  SQL 2.2
  Topic: REPLICATION_13
From where does the replication queue reader agent get its data?
  A) The publisher transaction log
  B) The distributor transaction log
  C)  From the distribution database
  D) From a message queue
  Answer: D) From a message queue

  Question:  45
  UCF:  SQL 2.2
  Topic: REPLICATION_13
George works as a Database administrator for Miracle Infocom. The company has its main office in Los Angels and 20 branch offices at other locations. The main office has a SQL server 2005 computer, which is a Publisher. Each branch office also has a SQL server 2005 computer, which is either Distributor or Subscriber. He wants to monitor the transaction logs of those databases that are configured for transactional replication. Which of the following replication agents will he use to accomplish the task?
  A)  Log Reader
  B) B.Distribution
  C)  C.Merge
  D) D.Snapshot
  Answer: A)  Log Reader

  Question:  46
  UCF:  SQL 2.2
  Topic: REPLICATION_13
How are schema changes(add columns) replicated in sql 2005?
  A) They are replicated by default
  B) It should be activated
  C)  They wont be replicated
  D) Reinitialize the subscription
  Answer: A) They are replicated by default

  Question:  47
  UCF:  SQL 2.2
  Topic: REPLICATION_13
How can I find numerical info about the transactions remaining to be sent?
  A) Subscription status
  B) publisher status
  C)  sp_replmonitorsubscriptionpendingcmds
  D) You can not view
  Answer: C)  sp_replmonitorsubscriptionpendingcmds

  Question:  48
  UCF:  SQL 2.2
  Topic: REPLICATION_13
How can Replication Alerts be written to Event Viewer ?
  A) Open up the replication alerts folder, double click on the alert you are interested, click on the browse
  B) No Function available
  C)  It will directly go to event Viewer
  D) None
  Answer: A) Open up the replication alerts folder, double click on the alert you are interested, click on the browse

  Question:  49
  UCF:  SQL 2.2
  Topic: REPLICATION_13
how to add column to a replicated article:
  A) sp_repaddcolumn
  B) sp_replsaddcolumn
  C)  sp_repladdcolmn
  D) sp_repladdcolumn
  Answer: D) sp_repladdcolumn

  Question:  50
  UCF:  SQL 2.2
  Topic: REPLICATION_13
If you want to establish a replication model that will allow three branch offices to update data at the corporate site and propagate the data back to each branch office. Which replication model should you use?
  A) Transactional replication
  B) Merge replication
  C)  Remote replication
  D) Snapshot replication
  Answer: B) Merge replication
 
 


SQL 3.1

  Question:  1
  UCF:  SQL 3.1
  Topic: INST_UPG_10
Which of the following statement is not correct about Clustering
  High reliability
  Offers hardware, OS, and software fault tolerance
  Low availabilty
  Automatic failover
  Answer: Low availabilty

  Question:  2
  UCF:  SQL 3.1
  Topic: INST_UPG_10
How many Virtual IP address is needed for installing 4 instances of sql server in sql 2005 cluster
  1
  2
  3
  4
  Answer: 4

  Question:  3
  UCF:  SQL 3.1
  Topic: INST_UPG_10
Recommended minimum size for Quorum is
  1024 MB
  500 MB
  256 MB
  128 MB
  Answer: 500 MB

  Question:  4
  UCF:  SQL 3.1
  Topic: INST_UPG_10
Is it necessary to configure MSDTC as a clustered resource for SQL Server clustering to work?
  yes it is required always for SQL Server clustering to work?
  no, it is not required
  it is required only if you need the ability to perform distributed transactions on your cluster
  none
  Answer: it is required only if you need the ability to perform distributed transactions on your cluster

  Question:  5
  UCF:  SQL 3.1
  Topic: INST_UPG_10
Which of the following option is not correct about Clustering
  The cluster service and SQL Server service accounts need to be a member of the Local Administrators group of each node
  The cluster service and SQL Server service accounts need to be domain accounts and members of the domain user group
  An sql instance must be installed fom the active node in the cluster.
  An SQL instances can be uninstalled from Add\Remove program in control panel
  Answer: An SQL instances can be uninstalled from Add\Remove program in control panel

  Question:  6
  UCF:  SQL 3.1
  Topic: INST_UPG_10
Can SQL Server 2000 be installed on one node of a cluster and SQL Server 2005 be installed on the other node?
  Yes
  no
  it depends on requirement
  none
  Answer: no

  Question:  7
  UCF:  SQL 3.1
  Topic: INST_UPG_10
What cluster resources should not belong to cluster group
  Cluster IP Address
  Cluster Name
  Quorum drive
  Physical Disk F:
  Answer: Physical Disk F:

  Question:  8
  UCF:  SQL 3.1
  Topic: INST_UPG_10
If you take any sql server resource in a cluster offline, what will happen?
  SQL Server Resources will be failed over to another node
  SQL Server dependent resources will be failed over to another node
  Only SQL Server resources will be offline on same node
  SQL Server and its dependent resources will be offline on same node
  Answer: SQL Server and its dependent resources will be offline on same node

  Question:  9
  UCF:  SQL 3.1
  Topic: INST_UPG_10
Upto how many instances can be supported in sql 2005 Enterprise Edition Clustering
  25
  16
  50
  32
  Answer: 25

  Question:  10
  UCF:  SQL 3.1
  Topic: INST_UPG_10
Upto how many instances can be supported in sql 2005 Standard Edition Clustering
  25
  16
  50
  32
  Answer: 16

  Question:  11
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which DBCC command is used to check the fragmentation of a Database?
  DBREINDX
  SHOWCONTIG
  DBCATALOG
  None of the Aove
  Answer: SHOWCONTIG

  Question:  12
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which DBCC command performs both DBCC CHECKTABLE and CHECKALLOC?
  CHECKDB
  SHOWCONTIG
  DBCHECKCATALOG
  None of the Aove
  Answer: CHECKDB

  Question:  13
  UCF:  SQL 3.1
  Topic: MON_TR_15
What does the ERROR_PROCEDURE() function return?
  Description of the error
  Name of the function
  Name of the stored procedure that caused the error
  All of the Above
  Answer: Name of the stored procedure that caused the error

  Question:  14
  UCF:  SQL 3.1
  Topic: MON_TR_15
What does @@options return?
  Current set options for the connection
  Current set options
  Current set options for the user
  None of the Above
  Answer: Current set options for the connection

  Question:  15
  UCF:  SQL 3.1
  Topic: MON_TR_15
Where do you think the users names and passwords will be stored in sql server?
  sysusers
  sysxlogins
  sysobjects
  sysproperties
  Answer: sysxlogins

  Question:  16
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which DBCC command is used to reduce the size of the file?
  SHRINKDB
  SHRINKFILE
  SHRINKFILES
  None of the Above
  Answer: SHRINKFILE

  Question:  17
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which trace flag can be used to trace the occurrence of Deadlocks?
  -1204
  205
  206
  305
  Answer: -1204

  Question:  18
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which DBCC command provides statistics about the use of T-log space in all databases?
  SQLPERF (LOGSPACES)
  SQLPERF (LOGS)
  SQLPERF
  SQLPERF (LOGSPACE)
  Answer: SQLPERF (LOGSPACE)

  Question:  19
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which stored procedure should be used to list the fixed Server roles in the current database?
  sp_helpsrvroles
  sp_helpsvrole
  sp_helpsrvrole
  None of the Above
  Answer: sp_helpsrvrole

  Question:  20
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which database is used as a template for all other databases?
  model
  master
  msdb
  tempdb
  Answer: model

  Question:  21
  UCF:  SQL 3.1
  Topic: MON_TR_15
How many SQL Server agent service runs per SQL Server Installation?
  2
  1
  3
  4
  Answer: 1

  Question:  22
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which stored procedure should you use to estimate the amount of space on disk required by a backup?
  sp_spaceused
  sp_spacesused
  sp_space
  None of the Above
  Answer: sp_spaceused

  Question:  23
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which DBCC command is used to display the status of trace flags?
  TRACESTATUS
  TRACEON
  TRACEOFF
  TRACEONS
  Answer: TRACESTATUS

  Question:  24
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which DBCC command, displays the syntax for any specified dbcc command?
  HELPS
  HELPSORTS
  HELPSORT
  HELP
  Answer: HELP

  Question:  25
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which DBCC command is used to reduce the size of the database?
  SHRINKDB
  SHRINKDBS
  SHRINKDATABASE
  None of the Above
  Answer: SHRINKDATABASE

  Question:  26
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Is it possible to create a Clustered index without a Primary Key?
  Yes
  No
  It is only possible with a Partner Key
  None of the above
  Answer: Yes

  Question:  27
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Which SP can be used to perform same actions for all databases?
  sp_mseachdb
  sp_msforeachdb
  sp_msforeachdatabase
  All of the above
  Answer: sp_msforeachdb

  Question:  28
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Which is the default Isolation level in SQL Server 2000?
  Read Uncommitted
  Serializable
  Read Committed
  None of the above
  Answer: Read Committed

  Question:  29
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Which is the most restrictive Isolation level?
  Read Uncommitted
  Read Committed
  None of the above
  serializable
  Answer: serializable

  Question:  30
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
How many pages makes an Extent?
  8
  16
  32
  64
  Answer: 8

  Question:  31
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Which one of them is not a TABLE HINT?
  NOLOCK
  HOLDLOCK
  TABLOCK
  DBDLOCK
  Answer: DBDLOCK

  Question:  32
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
A table exists without a Primary key, however the user needs to create a clustered index on it. Is it possible?
  No
  Yes
  Only a Non-Clustered index can be created
  None of the above
  Answer: Yes

  Question:  33
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
What is a table called, if it does have neither Cluster nor Non-cluster Index?
  Heap
  Extent
  Page
  None of the above
  Answer: Heap

  Question:  34
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Which component of SQL Server 2005 handles messaging between the Sender and Receiver?
  CLR
  Service Broker
  Data Encryption
  HTTP EndPoints
  Answer: Service Broker

  Question:  35
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
When shall we ignore running UPDATE STATISTICS command?
  Deletions of Data
  Modifications of Data
  Deletion of Database
  None of the above
  Answer: Deletion of Database

  Question:  36
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
How can we view the current collation Settings?
  sp_sorts
  sp_helpsorts
  sp_sort
  sp_helpsort
  Answer: sp_helpsort

  Question:  37
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Which is the term referrred for periodic flushing of all dirty buffers to stable media?
  CHECKPOINT
  Deadlocks
  Dirty Reads
  None of the above
  Answer: CHECKPOINT

  Question:  38
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Which Isolation level does not issue shared locks while reading data?
  Read Committed
  Read Uncommitted
  Serializable
  None of the above
  Answer: Read Uncommitted

  Question:  39
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Which stored procedure can be used to change the object owner?
  sp_objowner
  sp_changedobjectowner
  sp_changeobjectowner
  None of the above
  Answer: sp_changeobjectowner

  Question:  40
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Which one of them is not an advanced option under SQL Server 2000 query analyzer for troubleshooting purpose?
  SET SHOWPLAN_ALL ON
  SET SHOWPLAN_TEXT ON
  SET STATISTICS IO ON
  SET SHOWPLAN IO OFF
  Answer: SET SHOWPLAN IO OFF

  Question:  41
  UCF:  SQL 3.1
  Topic: STORAGE_10
For which of the following Microsoft applications is DPM v2 designed to provide the best backup and most reliable restore?
  Microsoft SQL Server/ Windows file services
  Microsoft Exchange Server
  SharePoint Portal Server
  All of the above
  Answer: All of the above

  Question:  42
  UCF:  SQL 3.1
  Topic: STORAGE_10
DPM version 2 was specifically built to protect and recover SQL Server using the best aspects of
  Continuous data protection (CDP)
  Traditional tape backup
  Continuous data protection (CDP) with traditional tape backup
  Protecting changed files to secondary disk
  Answer: Continuous data protection (CDP) with traditional tape backup

  Question:  43
  UCF:  SQL 3.1
  Topic: STORAGE_10
How does DPM V2 complete data protection and recovery solution for a core Microsoft SQL server?
  Continuously sends the server workloads to a DPM server, which then provides disk-based recovery and tape-based archival
  Directly takes Online snapshots and offline tape backups.
  Protects changed files in a secondary disk
  None of the above
  Answer: Continuously sends the server workloads to a DPM server, which then provides disk-based recovery and tape-based archival

  Question:  44
  UCF:  SQL 3.1
  Topic: STORAGE_10
DPM fails when protecting SQL Server clusters,SQL Servers with Log Shipping or Simple Recovery Model configurations and mirrored SQL 2005 servers.
  True
  False
  None of the above
  Answer: False

  Question:  45
  UCF:  SQL 3.1
  Topic: STORAGE_10
How do we restore Microsoft SQL Server with Microsoft Data Protection Manager (DPM)?
  Restore a SQL database directly back to the original server/ recovery database on the original server
  Copy database files to an alternate server
  Copy the database file to tape
  All of the above
  Answer: All of the above

  Question:  46
  UCF:  SQL 3.1
  Topic: STORAGE_10
Unique to DPM v2 is lossless recovery of SQL Server data
  TRUE
  False
  patially true
  None of the above
  Answer: TRUE

  Question:  47
  UCF:  SQL 3.1
  Topic: STORAGE_10
RAID stands for
  Redundant Array of Inexpensive Disks
  Redundant Array of Inexpensive Disks
  Redundant Array of Inexpensive/ Independent Disks
  Redundant Array of Important Disks
  Answer: Redundant Array of Inexpensive/ Independent Disks

  Question:  48
  UCF:  SQL 3.1
  Topic: STORAGE_10
What were the Motivating factors for RAID to be conceptualized?
  An attempt to bridge the widening gap between Performance of Processor v/s Performance of disks
  To support faster data access required by video and multi-media applications – warranted new storage architecture
  When disks were small and expensive, an attempt to create a big virtual disk in order to provide a large amount of data.
  All of the above
  Answer: All of the above

  Question:  49
  UCF:  SQL 3.1
  Topic: STORAGE_10
RAID can recover data during which of the following scenarios?
  System crashes while I/Os are in progress on RAID or a single disk in RAID configuration fails
  System crashes and is followed by a disk failure or a double disk failure occurs
  Disk fails followed by uncorrectable biterror during reconstruction.
  All of the above
  Answer: System crashes while I/Os are in progress on RAID or a single disk in RAID configuration fails

  Question:  50
  UCF:  SQL 3.1
  Topic: STORAGE_10
Which of the following scenarios does RAID lose data?
  System crashes while I/Os are in progress on RAID
  A single disk in RAID configuration fails
  System crashes and is followed by a disk failure or a double disk failure occurs
  None of the above
  Answer: System crashes and is followed by a disk failure or a double disk failure occurs
 
 


  SQL 3.2

  Question:  1
  UCF:  SQL 3.2
  Topic: CONCEPT_12
Which components are part of Report Server
  Report Processor
  Scheduling and Delivery Processor
  Report Manager
  Report Processor, Scheduling and Delivery Processor Report Server Database
  Answer: Report Processor, Scheduling and Delivery Processor Report Server Database

  Question:  2
  UCF:  SQL 3.2
  Topic: CONCEPT_12
The Report Processor gets the report definition from
  Report Definition File
  Report Database
  Rendering extension
  None of these
  Answer: Report Database

  Question:  3
  UCF:  SQL 3.2
  Topic: CONCEPT_12
PollingInterval configuration parameter of RSReportServer.config file interacts with
  Report Designer
  Scheduling and Delivery Processor
  Data extension
  Report Processor
  Answer: Scheduling and Delivery Processor

  Question:  4
  UCF:  SQL 3.2
  Topic: CONCEPT_12
Subscription and schedule definitions for a report is stored in
  Report Definition File
  ReportServerTempDb Database
  ReportServer Database
  RSReportServer.config
  Answer: ReportServer Database

  Question:  5
  UCF:  SQL 3.2
  Topic: CONCEPT_12
Reporting Services is implemented as
  SQL Server Service
  Web Service
  SQL Server Integration Services
  Microsoft Windows service and as a Web service
  Answer: Microsoft Windows service and as a Web service

  Question:  6
  UCF:  SQL 3.2
  Topic: CONCEPT_12
Reporting Services requires this to connect to local and remote Report Server
  SQL Server Agent
  Web Service
  SQL Server
  Windows Management Instrumentation
  Answer: Windows Management Instrumentation

  Question:  7
  UCF:  SQL 3.2
  Topic: CONCEPT_12
Report definition in Report Definition file (.rdl) file is written in
  VB.net
  C#
  XML
  JavaScript
  Answer: XML

  Question:  8
  UCF:  SQL 3.2
  Topic: CONCEPT_12
Request for a cached report or snapshot is handled by
  Report Manager
  Report Processor
  Report Builder
  Scheduling Delivery Processor
  Answer: Report Processor

  Question:  9
  UCF:  SQL 3.2
  Topic: CONCEPT_12
Pushing of reports to E-mail is done by this component
  SQL Server Database Mail
  SMTP Server
  Scheduling and Delivery Processor
  All of these
  Answer: Scheduling and Delivery Processor

  Question:  10
  UCF:  SQL 3.2
  Topic: CONCEPT_12
RSReportServer.config file is found in this folder
  90\shared
  \Reporting Services\ReportServer
  Instid\Reporting Services\RSTempfiles
  Instid\Reporting Services\reportManager
  Answer: \Reporting Services\ReportServer

  Question:  11
  UCF:  SQL 3.2
  Topic: CONCEPT_12
Report server and Report Manager are _ applications
  VB.net
  C#
  ASP.NET
  None of these
  Answer: ASP.NET

  Question:  12
  UCF:  SQL 3.2
  Topic: CONCEPT_12
How many peformance counter objects installed as part of Reporting Services
  Two
  One
  Three
  Five
  Answer: Two

  Question:  13
  UCF:  SQL 3.2
  Topic: MIRROR_10
Sql 2005 Standard editions installed in a server without applying servicepacks .To enable mirroring we need run the trace flag
  1400
  1401
  1402
  1403
  Answer: 1400

  Question:  14
  UCF:  SQL 3.2
  Topic: MIRROR_10
Default port for creating Mirroring is
  5021
  5022
  5023
  5024
  Answer: 5022

  Question:  15
  UCF:  SQL 3.2
  Topic: MIRROR_10
Operating modes in mirroring has been classified into
  1
  2
  3
  4
  Answer: 2

  Question:  16
  UCF:  SQL 3.2
  Topic: MIRROR_10
Roles used in EndPoints are
  1
  2
  3
  4
  Answer: 4

  Question:  17
  UCF:  SQL 3.2
  Topic: MIRROR_10
Endpoints informations can be reterived from
  tcp_endpoint
  endpoints_tcp
  endpoints_tcpdetail
  tcp_endpoints
  Answer: tcp_endpoints

  Question:  18
  UCF:  SQL 3.2
  Topic: MIRROR_10
Automatic failover duration can be increased in mirroring by the paramter
  partner duration
  partner timeout
  partner time
  partner =50
  Answer: partner timeout

  Question:  19
  UCF:  SQL 3.2
  Topic: MIRROR_10
when principal database was not able to failover mirror database then mirror status will be
  Pending_failover
  suspended
  unsynchronized
  expozed
  Answer: Pending_failover

  Question:  20
  UCF:  SQL 3.2
  Topic: MIRROR_10
when principal database was not able to communicate with mirror and withness then the mirror state will be
  suspended
  unsynchronized
  expozed
  exposed
  Answer: exposed

  Question:  21
  UCF:  SQL 3.2
  Topic: MIRROR_10
Mirroring state can be viewed by using the table
  Mirroring_view
  database_Mirroring_state_change
  Mirroring_state_change
  Mirroring_change_state
  Answer: database_Mirroring_state_change

  Question:  22
  UCF:  SQL 3.2
  Topic: MIRROR_10
In ADO.Net To specify the mirror parameter we have to use the parameter
  Failover partner
  Partner failover
  mirror server
  Mirror Partner
  Answer: Failover partner

  Question:  23
  UCF:  SQL 3.2
  Topic: REPORT_13
which is the method by which SSRS CANNOT be deployed
  Using Report Builder
  TSQL
  Visual Studio 2008
  rs.exe
  Answer: TSQL

  Question:  24
  UCF:  SQL 3.2
  Topic: REPORT_13
While deploying reports using BIDS, which is the property that is NOT available for configuring
  Deploy
  Debug
  DebugLocal
  Release
  Answer: Deploy

  Question:  25
  UCF:  SQL 3.2
  Topic: REPORT_13
Which utility uses script files written in VB .NET to access the Web service and allows us to call any of the published methods.
  spsrvs.exe
  msrs.exe
  ssrs.exe
  rs.exe
  Answer: rs.exe

  Question:  26
  UCF:  SQL 3.2
  Topic: REPORT_13
What does the following command do rs -i Reports.rss -s https://iag.wipro.com/whalecom59fde2e7b008764732/whalecom0/reportserver
  Deploy Report
  Checks the existence of report in the given URL
  Publish Report
  Checks whether the report is error free
  Answer: Publish Report

  Question:  27
  UCF:  SQL 3.2
  Topic: REPORT_13
You must be logged in as a user who is _ to add new schedules.
  Member of the SSRS Deploy Administrator role
  DBO permission on the ReportingServices DB
  Sysadmin
  Member of the SSRS System Administrator role
  Answer: Member of the SSRS System Administrator role

  Question:  28
  UCF:  SQL 3.2
  Topic: REPORT_13
when a job, such as a subscription or an execution snapshot, is scheduled to run within SSRS, a SQL Server job is created using the SQL Server Agent. Jobs can be monitored through
  Report Builder
  Report Manager
  Query the ReportingServices DB
  Report Server
  Answer: Report Manager

  Question:  29
  UCF:  SQL 3.2
  Topic: REPORT_13
which is NOT true with respect to a cached report
  Caching a report sometimes result in less performance as multiple copies of the report needs to be stored
  The subsequent user must access the report within the time interval before the cached report is set to expire.
  If the cached report has parameters that change during subsequent executions, and each user receives a new report based on that parameter, each report that s generated becomes a cached copy specific to the parameter value
  The report s data source isn t set to Windows authentication or to prompt the user for login credentials.
  Answer: Caching a report sometimes result in less performance as multiple copies of the report needs to be stored

  Question:  30
  UCF:  SQL 3.2
  Topic: REPORT_13
which is not ture with respect to the Snap Shot of a report
  point in time
  generated as execution snapshots
  can contain multiple copies of a report at given points in time.
  can be updated using methods from .Net Frame work.
  Answer: can be updated using methods from .Net Frame work.

  Question:  31
  UCF:  SQL 3.2
  Topic: REPORT_13
Which CANNOT be considered as a management role in SSRS
  Content management
  Performance
  Report Authoring
  Report execution
  Answer: Report Authoring

  Question:  32
  UCF:  SQL 3.2
  Topic: REPORT_13
Using Report Server Web service, you can create custom applications that control all aspects of the server and cover the entire reporting life cycle. Which among the list cannot be controlled?
  Folder and resource management
  Report rendering
  Report history
  Report Authoring
  Answer: Report Authoring

  Question:  33
  UCF:  SQL 3.2
  Topic: REPORT_13
Which is NOT an SSRS 2008 Class for Use with WMI
  MSReportServer_ ConfigurationSetting
  MSReportServer_ ReportSecuritySetting
  MSReportServerReportManager_ ConfigurationSetting
  All the above
  Answer: MSReportServer_ ReportSecuritySetting

  Question:  34
  UCF:  SQL 3.2
  Topic: REPORT_13
Which among the list is not part of SSRS security model
  Data encryption
  Report audits
  Authentication and user access
  Report Delivery
  Answer: Report Delivery

  Question:  35
  UCF:  SQL 3.2
  Topic: REPORT_13
SSRS natively supports encrypting the data it stores in the
  ReportServer DB
  ReportServer$SQL2008TempDB
  In configuration files located on IIS server
  In configuration files located on DB server
  Answer: ReportServer DB

  Question:  36
  UCF:  SQL 3.2
  Topic: TU_15
Litespeed can be converted to SQL native file by using the command
  Extractor
  Split
  Convert
  Utility
  Answer: Extractor

  Question:  37
  UCF:  SQL 3.2
  Topic: TU_15
While Converting Litespeed to Native parameter N denotes
  to encrypt
  Number of files to generate
  path of Native backupfile
  None of the above
  Answer: Number of files to generate

  Question:  38
  UCF:  SQL 3.2
  Topic: TU_15
Default Encryption Level in Litespeed is
  56 bit RC2
  40 bit RC2
  80 bit RC2
  60 bit RC2
  Answer: 40 bit RC2

  Question:  39
  UCF:  SQL 3.2
  Topic: TU_15
File Corrupted can be verified by using the command
  checksumonly
  checksumonly_check
  xp_checksum
  checksumutility
  Answer: xp_checksum

  Question:  40
  UCF:  SQL 3.2
  Topic: TU_15
In Litespeed audit log can be viewed by the SP
  view_sls_auditlog
  xp_sls_auditlog
  xp_sls_view_auditlog
  xp_view_sls_auditlog
  Answer: xp_view_sls_auditlog

  Question:  41
  UCF:  SQL 3.2
  Topic: TU_15
Average Compression ration in Litespeed can be
  70-75
  75-80
  80-85
  90-95
  Answer: 75-80

  Question:  42
  UCF:  SQL 3.2
  Topic: TU_15
Percentage of Back up Speed higher in Litespeed when compare to Native backup
  60
  25
  50
  75
  Answer: 50

  Question:  43
  UCF:  SQL 3.2
  Topic: TU_15
Failed to login into server in Litespeed can be resolved by removing
  SQl service accounts
  Administrators
  BUILTIN\Administrators
  None of the above
  Answer: BUILTIN\Administrators

  Question:  44
  UCF:  SQL 3.2
  Topic: TU_15
Query timeout occurs when you run extended stored procedure in Litespeed can be resolved by checking permissions in
  checking sql server
  checking registry
  checking settings in database
  None of the above
  Answer: checking registry

  Question:  45
  UCF:  SQL 3.2
  Topic: TU_15
Error opening registry key for SQLLiteSpeed can be resolved by
  User and SQL service account should be sync in privlieges
  user should have sysadmin
  User should be a dbowner
  None of the above
  Answer: User and SQL service account should be sync in privlieges

  Question:  46
  UCF:  SQL 3.2
  Topic: TU_15
Restoring File backup may arise issues due to
  File is not valid
  File is corrupted one
  File is not an litespeed format
  File is already in use
  Answer: File is not an litespeed format

  Question:  47
  UCF:  SQL 3.2
  Topic: TU_15
File header can be checked by the command
  xp_restore_headeronly
  xp_restore_header
  xp_restore_headerlist
  restore_headeronly
  Answer: xp_restore_headeronly

  Question:  48
  UCF:  SQL 3.2
  Topic: TU_15
No of editions in litespeed are
  2
  3
  4
  5
  Answer: 4

  Question:  49
  UCF:  SQL 3.2
  Topic: TU_15
Progress of current sql activity can be viewed by using the command
  xp_ReadProgress
  xp_slsReadProgress
  xp_slsRead
  xp_slsReaProgress
  Answer: xp_slsReadProgress

  Question:  50
  UCF:  SQL 3.2
  Topic: TU_15
Table restore can be done by using the command
  xp_obj_recover
  xp_obj_recovery
  object_recovery
  xp_object_recovery
  Answer: xp_object_recovery
 
 


SQL 4.1

  Question:  1
  UCF:  SQL 4.1
  Topic: MT_15
Which of the below Reporting tools provides complex reporting but requires in-depth understanding of the data
  Report Builder
  Report Designer
  Report Manager
  Report Server
  Answer: Report Designer

  Question:  2
  UCF:  SQL 4.1
  Topic: MT_15
In SQL Server2005, The purpose of making This a Shared Data Source checkbox whilh creating a report through Report Builder is
  to make it available to the future reporting apps
  to share the data across networkt
  Answer: to make it available to the future reporting apps

  Question:  3
  UCF:  SQL 4.1
  Topic: MT_15
While modifying a report, what is not possible?
  You can change the available data or the sort order for the report by modifying the query on the Data tab.
  You can resize or rearrange controls on the Layout tab.
  You can use the Properties window to change properties of individual controls including their font, alignment, colors, and so on.
  You can include more tables ot join columns
  Answer: You can include more tables ot join columns

  Question:  4
  UCF:  SQL 4.1
  Topic: MT_15
You are the local administrator of a windows server which hosts a SQL Server Databse Engine and SSRS. You created a report in the Report Designer and now you want to publish it to the Reporting server. You recive an error that zour login has insufficient privileges. In which security role your login need to be added in SSRS?
  Add your account to the sysadmin fixedserver role of the SQL Server
  Add the Windows user ASPNET to the Administrator group of the local server.
  Grant your login db_owner access to ReportServerTempdb database
  Answer: Add the Windows user ASPNET to the Administrator group of the local server.

  Question:  5
  UCF:  SQL 4.1
  Topic: MT_15
To publish a report,In Configuration Manager, Active Soultion Configuration must be selected to ————
  Debug
  Debuglocal
  Production
  Any of theabove
  Answer: Production

  Question:  6
  UCF:  SQL 4.1
  Topic: MT_15
While publishing a report, ————– should be selected from the configuration Manager
  Build only
  Deploy only
  Both Build and Deploy
  No need to select explicitily. It will automatically pick up
  Answer: Both Build and Deploy

  Question:  7
  UCF:  SQL 4.1
  Topic: MT_15
Which of the below Reporting tools is intended for Business Analysts and non-developers
  Report Builder
  Reort Manager
  Report Designer
  None of the above
  Answer: Report Builder

  Question:  8
  UCF:  SQL 4.1
  Topic: MT_15
What does a Data model contai? (Choose all that apply)
  Data Sources connect the data model to actual data.
  Data Source Views draw data from data sources.
  Report Models contain entities that end users can use on reports.
  All of the above
  Answer: All of the above

  Question:  9
  UCF:  SQL 4.1
  Topic: MT_15
Which is not a rule in the Data Model generation?
  Create entities for all the tables
  Create indexed views
  Create numeric aggregates
  Create roles
  Answer: Create indexed views

  Question:  10
  UCF:  SQL 4.1
  Topic: MT_15
In addition to viewing data, what are the other tasks performed bz Report Manager
  Enables view historical snapshots
  Enables Subscription of reports to the end users
  Only A)  
  Both a and b
  Answer: Both a and b

  Question:  11
  UCF:  SQL 4.1
  Topic: MT_15
Installing SSRS creates two new virtual directories under the default website on IIS. They are
  Reports and ReportServer
  ReportTemp and ReportServer
  Report and ReportserverTemp
  None of the above
  Answer: Reports and ReportServer

  Question:  12
  UCF:  SQL 4.1
  Topic: MT_15
Which is true about Reports site
  hosts a web service for running and managing reports
  allows you upload reports from a browser and run reports over the web.
  Both A)  and B)  
  None of the above
  Answer: allows you upload reports from a browser and run reports over the web.

  Question:  13
  UCF:  SQL 4.1
  Topic: MT_15
It is possible to add new parameters, maz or maz not be in the query in the Lazout of Report Designer
  Yes
  No
  Cant Say
  Answer: Yes

  Question:  14
  UCF:  SQL 4.1
  Topic: MT_15
Which is not a SSIS object
  Event handler
  Data and Control flows
  Tasks
  Connections
  Answer: Tasks

  Question:  15
  UCF:  SQL 4.1
  Topic: MT_15
What is true about connection manager in SSIS?
  Controls the data flow
  Integrate different data sources into packages.
  Handles event
  None of the above
  Answer: Integrate different data sources into packages.

  Question:  16
  UCF:  SQL 4.1
  Topic: PT_15
What factor is a scale to measure the performance of a query
  Operators
  Query Cost
  Indexes
  Statistics
  Answer: Query Cost

  Question:  17
  UCF:  SQL 4.1
  Topic: PT_15
What tool would you use to analyze a query and its cost
  Estimate Query Plan
  Profiler
  Index tuning Wizard
  all of the above
  Answer: Estimate Query Plan

  Question:  18
  UCF:  SQL 4.1
  Topic: PT_15
To identify a slow performing query what data would you look in profiler
  text
  blocked by
  duration
  hostname
  Answer: duration

  Question:  19
  UCF:  SQL 4.1
  Topic: PT_15
Currently running CPU intensive batches/procedures can be found using
  dm_exec_query_plan
  Edison
  dm_exec_query_stats
  dm_exec_cached_plans
  Answer: dm_exec_query_stats

  Question:  18
  UCF:  SQL 4.1
  Topic: PT_15
To identify a slow performing query what data would you look in profiler
  text
  blocked by
  duration
  hostname
  Answer: duration

  Question:  21
  UCF:  SQL 4.1
  Topic: PT_15
On a case sensitive database, out of the two queries which one would be faster A)  SELECT column_name FROM table_name WHERE LOWER(column_name) = name B) SELECT column_name FROM table_name WHERE column_name = NAME or column_name = name
  a
  b
  Both are same
  none
  Answer: b

  Question:  22
  UCF:  SQL 4.1
  Topic: PT_15
What is the order of execution in a SELECT query using WHERE, GROUP BY, HAVING
  GROUP BY, HAVING, WHERE
  WHERE, HAVING, GROUP BY
  WHERE, GROUP BY, HAVING
  HAVING, WHERE, GROUP BY
  Answer: WHERE, GROUP BY, HAVING

  Question:  23
  UCF:  SQL 4.1
  Topic: PT_15
While comparing data in two tables which of the following function is a top performer
  Not EXISTS
  NOT IN
  LEFT Join
  none
  Answer: Not EXISTS

  Question:  24
  UCF:  SQL 4.1
  Topic: PT_15
Sorting often occurs when any of the following Transact-SQL statements are executed:
  ORDER BY
  UNION
  SELECT DISTINCT
  all of the above
  Answer: all of the above

  Question:  25
  UCF:  SQL 4.1
  Topic: PT_15
Which of the below query peforms better A)  SELECT customer_number, customer_name FROM customer WHERE customer_number in (1000, 1001, 1002, 1003, 1004) B)  SELECT customer_number, customer_name FROM customer WHERE customer_number BETWEEN 1000 and 1004 C)   SELECT customer_number, customer_name FROM customer WHERE customer_number =1000 or customer_number = 1001 or customer_number = 1002 or customer_number = 1003 or customer_number = 1004
  a
  b
  c
  b and c
  Answer: b

  Question:  26
  UCF:  SQL 4.1
  Topic: PT_15
How would you find out when an index/statistics was last updated
  stats_upd_date()
  update_stats()
  stats_date()
  sp_updatestats
  Answer: stats_date()

  Question:  27
  UCF:  SQL 4.1
  Topic: PT_15
In a Query plan Index Seak is used when
  it is highly selective queries
  non clustered indexes present
  it is a heap
  no statistics is present
  Answer: it is highly selective queries

  Question:  28
  UCF:  SQL 4.1
  Topic: PT_15
SYS.DM_DB_INDEX_OPERATIONAL_STATS is helpful in
  identifying when a table/index is last used since start of service
  identify lock IO contention for a table/inded
  identifying how many times a table/index is used since start of service
  Identify the Fragmentation level
  Answer: identify lock IO contention for a table/inded

  Question:  29
  UCF:  SQL 4.1
  Topic: PT_15
How would you find the Fragmentation level of an Index/Table?
  sys.dm_db_index_usage_stats
  sys.dm_db_index_operational_stats
  sys.dm_db_index_fragment_stats
  sys.dm_db_index_physical_stats
  Answer: sys.dm_db_index_physical_stats

  Question:  30
  UCF:  SQL 4.1
  Topic: PT_15
What is covering index?
  an index which includes all the columns of the table
  index which includes first and last columns of the table
  Index which includes all Char columns of the table
  when all the referenced columns in SELECT, WHERE are part of INDEXKEY
  Answer: when all the referenced columns in SELECT, WHERE are part of INDEXKEY

  Question:  31
  UCF:  SQL 4.1
  Topic: SOL_5
Which of the statement is True about DB Back-up s having different SQL Version
  Backups created on later versions of SQL Server cannot be restored to earlier versions of SQL Server
  Back-up is possible if the SQL Server DB have no Consistency errors in later
  DB Back-up in later version occupy more disk space
  None of these
  Answer: Backups created on later versions of SQL Server cannot be restored to earlier versions of SQL Server

  Question:  32
  UCF:  SQL 4.1
  Topic: SOL_5
Database can be Restored with
  Having Differential Tlogs Back-up s
  You have all the transactions stored in .ldf you will be able to restore the database completely without .mdf and .ndf files
  Full Back-up of DB is Mandatory to Restore the DB
  All of these
  Answer: Full Back-up of DB is Mandatory to Restore the DB

  Question:  33
  UCF:  SQL 4.1
  Topic: SOL_5
Which RECOVERY model best suites the Point-in time Recovery of data
  Simple
  Full
  both Simple and Full
  All of These
  Answer: Full

  Question:  34
  UCF:  SQL 4.1
  Topic: SOL_5
Transaction logs are required for
  Data Consistency
  Avoid Data loss
  Check Point for Transactions
  All of the these
  Answer: All of the these

  Question:  35
  UCF:  SQL 4.1
  Topic: SOL_5
Can we restore a SQL Server 2000 database backup to a SQL Server 7.0 server?
  yes, DBRestore is possible if the SQL Server DB have no Consistency errors
  No. The only way to move a SQL Server 2000 database to a SQL Server 7.0 server is by transferring the data using a method such as DTS, bcp, or use of a query between linked servers.
  yes ..Restore DB is possible if .ndf and .ldf files are present though .mdf files are missing
  Yes ..Database is in Full Recovery mode can be Restored
  Answer: No. The only way to move a SQL Server 2000 database to a SQL Server 7.0 server is by transferring the data using a method such as DTS, bcp, or use of a query between linked servers.

  Question:  36
  UCF:  SQL 4.1
  Topic: SSIS_5
SSIS is a version upgrade of DTS from SQL 2000?
  True
  Entirely New product
  False
  An upgrade of MSDTC
  Answer: Entirely New product

  Question:  37
  UCF:  SQL 4.1
  Topic: SSIS_5
You cannot edit DTS packages in Business Intelligence Development Studio You can edit SSIS packages only in BI Development Studio?
  True
  False
  Partially True
  None of the above
  Answer: True

  Question:  38
  UCF:  SQL 4.1
  Topic: SSIS_5
To view DTS packages in Management Studio connect to an instance of SQL Server and look in the Object Browser under?
  from Management to legacy to Database maintenance plans
  from Management to legacy to SQL Mail
  None of the Above
  from Management to legacy to Data Transformation Services
  Answer: from Management to legacy to Data Transformation Services

  Question:  39
  UCF:  SQL 4.1
  Topic: SSIS_5
You can install DTS and SSIS on the same computer and run them?
  You can install but cant run
  cant install both
  Can install and run
  cant install both
  Answer: Can install and run

  Question:  40
  UCF:  SQL 4.1
  Topic: SSIS_5
If you are a previous or current user of Microsoft SQL Server 2000 Data Transformation Services (DTS) you have the following options when you install Microsoft SQL Server 2005 Integration Services (SSIS)?
  Installing Support for SQL Server 2000 Data Transformation Services Packages
  Upgrading an earlier version of Data Transformation Services to SSIS
  Migrating existing Data Transformation Services packages to SSIS
  All of the above
  Answer: All of the above

  Question:  41
  UCF:  SQL 4.1
  Topic: STO_10
Which one of the following is not an important area which needs attention while doing capacity planning for your sql servers
  Disk storage
  Memory
  Processor efficiency
  OS Version
  Answer: OS Version

  Question:  42
  UCF:  SQL 4.1
  Topic: STO_10
What are the most important factors you look for while purchasing CPUs for your Server
  CPU Speed, CPU Made
  CPU Speed, L2 Cache
  CPU Made, L2 Cache
  CPU Speed only
  Answer: CPU Speed, L2 Cache

  Question:  43
  UCF:  SQL 4.1
  Topic: STO_10
While ordering for a RAID 5 Array, which of the following gives better read/write performance
  4 – 36GB drives
  6 – 17GB drives
  7 – 18GB drives
  8 – 19 GB drives
  Answer: 7 – 18GB drives

  Question:  44
  UCF:  SQL 4.1
  Topic: STO_10
Which is the best RAID option available for SQL Server databases, considering you want optimum I/O performance and your databases are more write intensive.
  RAID 1
  RAID 2
  RAID 5
  RAID 10
  Answer: RAID 10

  Question:  45
  UCF:  SQL 4.1
  Topic: STO_10
Ideally the OS, OS Swap file and other SQL Server executables should be located on which RAID Levels
  RAID 1
  RAID 2
  RAID 10
  RAID 5
  Answer: RAID 1

  Question:  46
  UCF:  SQL 4.1
  Topic: STO_10
Tempdb data files are avoided on this RAID level
  RAID 2
  RAID 5
  RAID 10
  RAID 1
  Answer: RAID 5

  Question:  47
  UCF:  SQL 4.1
  Topic: STO_10
You have already planned for your Tempdb size requirements but you want to make sure the size doesn t reach its limit and follow some best practices, which one of the following is not a best practice
  Set the recovery model of tempdb to simple
  Preallocate space for all tempdb files by setting the file size to a larger value
  Always place the tempdb files on the same drive as your database log files reside
  Create as many tempdb files as needed to maximize disk bandwidth
  Answer: Always place the tempdb files on the same drive as your database log files reside

  Question:  48
  UCF:  SQL 4.1
  Topic: STO_10
Which of the following is not one of the factors that help you in estimating your database size
  Determine the no. of rows
  Determine the size of each row
  calculate the no. of data pages required
  determine the no. of tables
  Answer: determine the no. of tables

  Question:  49
  UCF:  SQL 4.1
  Topic: STO_10
How to get the actual storage space for your table once you calculated the no. of data pages required to store your table
  mulitply the number by 8096
  multiply the number by 1024
  multiply the number by 2048
  multiply the number by 4096
  Answer: mulitply the number by 8096

  Question:  50
  UCF:  SQL 4.1
  Topic: STO_10
What is the maximum no. of cluster nodes supported by Microsoft Windows server 2003
  2
  4
  8
  12
  Answer: 8
 
 


SQL 1.1

  Question:  1
  UCF:  SQL 1.1
  Topic: BASIC ADMIN_8
Which of the following backup model is used, if we do not need transaction log backups?
  A) Simple
  B) Bulk-Logged
  C)  Full
  D) Simple and Full
  Answer: A) Simple

  Question:  2
  UCF:  SQL 1.1
  Topic: BASIC ADMIN_8
__ role has the capability to back up the database.
  A) db_backuplogger
  B) db_backup
  C)  db_backupoperator
  D) db_backadmin
  Answer: C)  db_backupoperator

  Question:  3
  UCF:  SQL 1.1
  Topic: BASIC ADMIN_8
To recover a database to the point of failure which recovery model can be used?
  A) Simple
  B) Bulk-Logged
  C)  Full
  D) Mirror
  Answer: C)  Full

  Question:  4
  UCF:  SQL 1.1
  Topic: BASIC ADMIN_8
Which of the following database consists of system tables that keep track of the server installation as a whole and all other databases that are subsequently created
  A) tempdb database
  B) master database
  C)  model database
  D) pubs database
  Answer: B) master database

  Question:  5
  UCF:  SQL 1.1
  Topic: BASIC ADMIN_8
CTE stands for
  A) Correct Table Extract
  B) Constant Table Expressions
  C)  Common Table Expressions
  D) none of the above
  Answer: C)  Common Table Expressions

  Question:  6
  UCF:  SQL 1.1
  Topic: BASIC ADMIN_8
SQL stands for
  A) Strong Question Language
  B) Structured Question Language
  C)  Structured Query Language
  D) none of the above
  Answer: C)  Structured Query Language

  Question:  7
  UCF:  SQL 1.1
  Topic: BASIC ADMIN_8
Which SQL keyword is used to retrieve a maximum value?
  A) TOP
  B) MOST
  C)  UPPER
  D) MAX
  Answer: D) MAX

  Question:  8
  UCF:  SQL 1.1
  Topic: BASIC ADMIN_8
We refer to a join as self-join when
  A) We are joining more than 2 tables
  B) We are joining table to itself
  C)  We are using left and right join together
  D) None of the above
  Answer: B) We are joining table to itself


  Question:  9
  UCF:  SQL 1.1
  Topic: MON TRBL_5
Aamir was recently moved to the ETL group in a company. He should be able to modify data on the all the databases available on the SQL Server. Which of the following database role should he be a member o
  A) db_datawriter
  B) db_denydatareader
  C)  db_datareader
  D) all of above
  Answer: A) db_datawriter

  Question:  10
  UCF:  SQL 1.1
  Topic: MON TRBL_5
Sirisha changes the definition of a view by issuing a ALTER VIEW statement. Which of the following are changed? 1. stored procedure 2. Triggers 3. permissions on the view
  A) 1 2
  B) 23
  C)  13
  D) 1,23
  Answer: D) 1,23

  Question:  11
  UCF:  SQL 1.1
  Topic: MON TRBL_5
Ajay wants to check whether the latest service pack has been installed on the SQL Server. Which command will give Ajay the required output?
  A) @@servername
  B) @@server
  C)  @@servicepack
  D) @@version
  Answer: D) @@version

  Question:  12
  UCF:  SQL 1.1
  Topic: MON TRBL_5
Which stored procedure do you run to identify locks on SQL Server?
  A) sp_identifylocks
  B) sp_livelocks
  C)  sp_who2
  D) sp_deadlocks
  Answer: C)  sp_who2

  Question:  13
  UCF:  SQL 1.1
  Topic: MON TRBL_5
Which stored procedure do you use to set the compatibility of a database to a previous version of SQL Server?
  A) sp_dblevel
  B) sp_dbcmptlevel
  C)  sp_cmptlevel
  D) sp_level
  Answer: B) sp_dbcmptlevel

  Question:  14
  UCF:  SQL 1.1
  Topic: NETWORKING_2
Which utility in windows 2003 Enterprise edition is used to copy big files across different network
  A) Xcopy
  B) Copy
  C)  robocopy
  D) networkcopy
  Answer: C)  robocopy

  Question:  15
  UCF:  SQL 1.1
  Topic: NETWORKING_2
Before any data can be transmitted through the TCP protocol, a reliable connection must be established. What is the process that TCP uses to establish this connection called
  A) network handshake
  B) windows handshake
  C)  two way handshake
  D) three way handshake
  Answer: D) three way handshake

  Question:  16
  UCF:  SQL 1.1
  Topic: SRV BASICS_5
With SQL, how can you return the number of records in the “Employee” table?
  A) SELECT COUNT() FROM Employee   B) SELECT COLUMNS() FROM Persons
  C)  SELECT COLUMNS() FROM Persons
  Answer: A) SELECT COUNT(*) FROM Employee

  Question:  17
  UCF:  SQL 1.1
  Topic: SRV BASICS_5
Which SQL statement is used to return only distinct values?
  A) SELECT ALL
  B) SELECT UNIQUE
  C)  SELECT DISTINCT
  D) SELECT DIFFERENT
  Answer: C)  SELECT DISTINCT

  Question:  18
  UCF:  SQL 1.1
  Topic: SRV BASICS_5
With SQL, how do you select a column named “FirstName” from a table named “Employee”?
  A) EXTRACT FirstName FROM Employee
  B) SELECT Employee.FirstName
  C)   OPEN Employee.FirstName
  D) SELECT FirstName FROM Employee
  Answer: D) SELECT FirstName FROM Employee

  Question:  19
  UCF:  SQL 1.1
  Topic: SRV BASICS_5
which of the following fall under Constraints?
  A) NOT NULL CHECK
  B)  UNIQUE
  C)  PRIMARY KEY, FOREIGN KEY
  D)  All the above
  Answer: D)  All the above

  Question:  20
  UCF:  SQL 1.1
  Topic: SRV BASICS_5
How to know which index a table is using?
  A) SELECT table_name,index_name FROM user_constraints
  B) SELECT index_name FROM indexes
  C)  SELECT table_name,index_name FROM user
  D) SELECT table_name FROM indexes_tables
  Answer: A) SELECT table_name,index_name FROM user_constraints

  Question:  21
  UCF:  SQL 1.1
  Topic: TOOLS UTIL_5
what is the order for database restore in simple recovery
  A)  1- Recent full backup 2- recent diff backup
  B) 1- recent full backup 2- recent diff backup 3- all the Tran backups from the latest diff backup
  C)  1- recent full backup 2- all the diff backups taken since latest full backup
  D) 1- recent full backup 2- all the diff backups taken since latest full backup 3- all the Tran backups from the latest diff backup
  Answer: A)  1- Recent full backup 2- recent diff backup

  Question:  22
  UCF:  SQL 1.1
  Topic: TOOLS UTIL_5
what immediate actions need to be taken for log file full issue for db in simple recovery?
  A) Add another log file in another spacious drive
  B) Increase the drive space where logfile resides
  C)  take a logbackup and shrink the file
  D) All the above
  Answer: A) Add another log file in another spacious drive

  Question:  23
  UCF:  SQL 1.1
  Topic: TOOLS UTIL_5
what immediate action you take if db is switched from simple to full
  A) take full backup before switching from simple to full
  B) take full backup after switching from simple to full
  C)  take log backup after switching from simple to full
  D) Both 2 3
  Answer: B) take full backup after switching from simple to full

  Question:  24
  UCF:  SQL 1.1
  Topic: TOOLS UTIL_5
which command does not log the deletion of rows
  A)  truncate
  B) delete
  C)  drop
  D) None
  Answer: A)  truncate

  Question:  25
  UCF:  SQL 1.1
  Topic: TOOLS UTIL_5
which command is used to check the database space
  A)  sp_spaceused
  B) sp_space
  C)  dbcc sqlperf(space)
  D) dbcc sqlperf(logspace)
  Answer: A)  sp_spaceused
 
 


SQL 2.1

  Question:  1
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
SQL Server 2005 Developer Edition has the same features as _______
  A) SQL Server 2005 Enterprise Edition
  B) SQL Server 2005 Workgroup Edition
  C)  SQL Server 2005 Express Edition
  D) SQL Server 2005 Standard Edition
  Answer: A) SQL Server 2005 Enterprise Edition

  Question:  2
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
syslogins table is the part of
  A) MSDB Database
  B) TempDB Database
  C)   Master Database
  D) Model Database
  Answer: C)   Master Database

  Question:  3
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
The HashBytes() in SQL Server 2005
  A) is used to transform data passed into the function, but you cannot recover the original value
  B) is used to display the cluomunvalue
  C)  is used to store the hash value
  D) is used in the database creation
  Answer: A) is used to transform data passed into the function, but you cannot recover the original value

  Question:  4
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
The maximum number of Secondary data files in a database
  A) 32767
  B) 32676
  C)  32776
  D) 32766
  Answer: D) 32766

  Question:  5
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
There are triggers for
  A) DDL statements
  B) DML Statements
  C)  DDL and DML statements
  D) None of the above
  Answer: C)  DDL and DML statements

  Question:  6
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
what actions should be taken if sql server is not connecting remotely?
  A) check the firewall settings and add sql port an exception. Then add the alias in the client utility.
  B) check the firewall settings and add the sql port as an exception. Then add the alias in the server utility.
  C)  Both 1 2
  D) none
  Answer: A) check the firewall settings and add sql port an exception. Then add the alias in the client utility.

  Question:  7
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
What are the advantages of having multiple instances of SQL Server?I.Can apply different service packs for different instances II. Can increase availability of the database III. Flexibility to allocate one instance per application IV. Improves the performance of the server
  A) All the above
  B) I II only
  C)  I III only
  D) II III only
  Answer: C)  I III only

  Question:  8
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
What are the different kinds of files that can be created in a database? I. Primary data File II. Secondary data file III. Secondary Log file IV. Log File
  A) I II only
  B) I, II III only
  C)  I, II IV only
  D) All the above
  Answer: C)  I, II IV only

  Question:  9
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
what are the order of three phases in database recovery?
  A)  1- analysis 2- redo 3- undo
  B) 1- redo 2- undo 3- analysis
  C)  1- undo 2- redo 3-analysis
  D) 1- undo 2- redo 3- analysis
  Answer: A)  1- analysis 2- redo 3- undo

  Question:  10
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
What do ACID properties stand for?
  A) Atomicity, Consistency, Intermediate, Dependent
  B) Attainable, Consistency, Isolation, Dependent
  C)  Atomicity, Consistency, Isolation, Durability
  D) Attainable, Consistency, Intermediate, Durability
  Answer: C)  Atomicity, Consistency, Isolation, Durability

  Question:  11
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
What does DML stand for
  A) Data Mode Lane
  B) Data Manipulation Language
  C)  Different Mode Level
  D) Data Model Language
  Answer: B) Data Manipulation Language

  Question:  12
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
What happens to a deferred transaction if the filegroup that it is holding it up becomes defunct?
  A) The transaction is unaffected. The deferred status remains because it was not properly resolved.
  B) The transaction is moved out of the deferred state.
  C)  The database becomes suspect.
  D) The filegroup cannot be moved into the defunct state until the transaction is resolved.
  Answer: B) The transaction is moved out of the deferred state.

  Question:  13
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
What is a bookmark lookup?
  A) When the query processor uses a non-clustered index to match a SARG.
  B) When the query processor uses a non-clustered index to lookup the data in the clustered index.
  C)  When the query processor uses a clustered index to satisfy a query
  D) When the query processor updates an index based on an insert.
  Answer: B) When the query processor uses a non-clustered index to lookup the data in the clustered index.

  Question:  14
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
What is a defunct filegroup?
  A) Any filegroup that is offline from the database.
  B) Any filegroup that has been emptied of data and other objects.
  C)  A defunct filegroup is a filegroup that has been removed and can never be restored, but its meta data remains.
  D) There is no such thing as a defunct filegroup
  Answer: C)  A defunct filegroup is a filegroup that has been removed and can never be restored, but its meta data remains.

  Question:  15
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
What is basic unit of SQL Server
  A) Page
  B) row
  C)  Table
  D) Database
  Answer: A) Page

  Question:  16
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
what is meant by extents in SQL Server 2005
  A) 16 pages
  B) 24 pages
  C)  8 pages
  D) 32 pages
  Answer: C)  8 pages

  Question:  17
  UCF:  SQL 2.1
  Topic: ARCHITECTURE_17
what is the default isolation level for sql?
  A) Read uncommitted
  B) Serializable
  C)  Read committed
  D) Repeatable Read
  Answer: C)  Read committed

  Question:  18
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Which system database can be configured for logshipping
  A) Master
  B) Model
  C)  MSDB
  D)  None
  Answer: D)  None

  Question:  19
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Which system stored procedure refreshes the remote monitor tables with the latest information from a given primary or secondary server for the specified log shipping agent?
  A) sp_refresh_ls_monitor
  B) sp_refresh_log_shipping_monitor
  C)  sp_refresh_log_shipping_monitor_tables
  D) sp_update_log_shipping_monitor
  Answer: B) sp_refresh_log_shipping_monitor

  Question:  20
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Which system stored procedure removes a secondary database name from a primary database?
  A) sp_cleanup_log_shipping_primary_secondary
  B) sp_delete_log_shipping_primary_secondary
  C)  sp_remove_log_shipping_primary_secondary
  D) sp_truncate_log_shipping_primary_secondary
  Answer: B) sp_delete_log_shipping_primary_secondary

  Question:  21
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Which system stored procedure removes log shipping of primary database including backup job as well as local and remote history?
  A) sp_cleanup_log_shipping_primary_database
  B) sp_delete_log_shipping_primary_database
  C)  sp_cleanup_log_shipping_primary_db
  D) sp_delete_log_shipping_primary_db
  Answer: B) sp_delete_log_shipping_primary_database

  Question:  22
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Which system stored procedure returns information regarding a secondary database from the monitor tables?
  A) sp_get_log_shipping_monitor_secondary
  B) sp_help_log_shipping_monitor_secondaries
  C)   sp_help_log_shipping_monitor_secondary
  D) sp_help_log_shipping_secondar
  Answer: C)   sp_help_log_shipping_monitor_secondary

  Question:  23
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Which system stored procedure returns the job ID of the alert job in a logshipping setup?
  A) sp_help_log_shipping_alert_job
  B) sp_get_log_shipping_alert_job
  C)  sp_help_log_shipping_job
  D) sp_get_log_shipping_job
  Answer: A) sp_help_log_shipping_alert_job

  Question:  24
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Which table we can check for log shipping history in SQL 2005 onwards?
  A)  log_shipping_monitor_history_detail
  B) log_shipping_plan_history
  C)  loghipping_plan_history
  D) None
  Answer: A)  log_shipping_monitor_history_detail

  Question:  25
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Which table stores one record for each primary database in each log shipping configuration?
  A) log_shipping_primary_databases
  B) log_shipping_monitor_primary
  C)  log_shipping_primary_dbs
  D) log_shipping_monitor_primary_dbs
  Answer: A) log_shipping_primary_databases

  Question:  26
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Which table stores one record for each secondary database in each log shipping configuration?
  A) log_shipping_secondary_dbs
  B) log_shipping_monitor_secondary
  C)  log_shipping_monitor_secondary_dbs
  D) log_shipping_secondary_databases
  Answer: D) log_shipping_secondary_databases

  Question:  27
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Which table will provides you the status of Log Shipping in SQL Server 2005
  A) log_shipping_monitor_primary
  B) log_shipping_plans
  C)  log_shipping_monitor
  D)  log_shipping_monitor_secondary
  Answer: D)  log_shipping_monitor_secondary

  Question:  28
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
Will my SQL Server logins be transferred as a part of log shipping?
  A) Yes
  B)  No
  C)  Only in 2005 version
  D) Only in 2008 version
  Answer: B)  No

  Question:  29
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
You are a database developer for an insurance company. The company has one main office and 18 regional offices. Each office has one SQL Server 2000 database. The regional offices are connected to the main office by a high-speed network. The main office database is used to consolidate information from the regional office databases. The tables in the main office database are partitioned horizontally. The regional office location is used as part of the primary key for the main office database. You are designing the physical replication model. What should you do?
  A) Configure the main office as a publishing Subscriber.
  B) Configure the main office as a Publisher with a remote Distributor.
  C)  Configure the main office as a central Publisher and the regional offices as Subscribers.
  D) Configure the regional offices as Publishers and the main office as a central Subscriber.
  Answer: D) Configure the regional offices as Publishers and the main office as a central Subscriber.

  Question:  30
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
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. What action should you perform?
  A) A. Specify the simple recovery model for DB1.
  B)  : Specify either the full or the bulk-logged recovery model for DB1.
  C)  C. Perform a complete backup of DB1 to portable media. Restore the secondary database from that backup; specify the RECOVERY option.
  D) Before you activate transaction log shipping to the secondary database, execute the following statement on the primary server. BACKUP LOG DB1 WITH TRUNCATE_ONLY
  Answer: D) 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:  31
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
You can reduce the load on your primary server by using a secondary server for read-only query processing. This can be achieved by putting the database in __ mode?
  A) NORECOVERY
  B) RECOVERY
  C)  STANDBY
  D) You cannot use secondary database.
  Answer: C)  STANDBY

  Question:  32
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
You cannot setup Log Shipping on a database that is in _ recovery model
  A) Full
  B) Simple
  C)  Bulk Logged
  D) None of the above
  Answer: B) Simple

  Question:  33
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
You have a corporate SQL box which has the master price list for shoes which is updated constantly in the corp. office. You want all your outlets to receive the updated prices as quickly as possible from the corp. office. Which sort of replication is best here?
  A)  Transactional
  B) Snapshot
  C)  Merge
  Answer: A)  Transactional

  Question:  34
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
You must be a _____ on each server instance to configure log shipping
  A) serveradmin
  B) sysadmin
  C)  setupadmin
  D) backupadmin
  Answer: B) sysadmin

  Question:  35
  UCF:  SQL 2.1
  Topic: LOG_SHIPPING_18
You will not be able run queries on the secondary database if the secondary database is in _ mode.
  A) NORECOVERY
  B) RECOVERY
  C)  STANDBY
  D) NOSTANDBY
  Answer: A) NORECOVERY

  Question:  36
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
How can you change “Hansen” into “Nilsen” in the “LastName” column in the Persons table?
  A) UPDATE Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’
  B) UPDATE Persons SET LastName=’Hansen’ INTO LastName=’Nilsen’
  C)  MODIFY Persons SET LastName=’Hansen’ INTO LastName=’Nilsen
  D) MODIFY Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’
  Answer: A) UPDATE Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’

  Question:  37
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
How does SQL Server checks the statistics?
  A) You can configure sql to use only sample rows
  B) You can configure sql to use all rows
  C)  Both 1 2
  D) None
  Answer: C)  Both 1 2

  Question:  38
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
How to change database owner for database DBTest as ‘Albert’
  A) ALTER DATABASE DBTest CHANGE OWNER ‘Albert’
  B) ALTER DATBASE MODIFY OWNER ‘Albert’
  C)  EXEC sp_changedatabaseowner ‘Albert’
  D) EXEC sp_changedbowner ‘Albert’
  Answer: D) EXEC sp_changedbowner ‘Albert’

  Question:  39
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
How to check Database Consistency
  A) DBCC CHECKCATALOG
  B) DBCC CHECKDATABASE
  C)  DBCC CHECKTABLE
  D)  DBCC CHECKDB
  Answer: D)  DBCC CHECKDB

  Question:  40
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
How to check depended objects on a table
  A)  sp_depends
  B) sp_depend
  C)  sp_dependenc
  D) None
  Answer: A)  sp_depends

  Question:  41
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
How to check index information for a table
  A) sp_help
  B)  sp_helpindex
  C)  sp_helpdb
  D) sp_helptext
  Answer: B)  sp_helpindex

  Question:  42
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
How to check SQL Server Version using T-SQL
  A) SELECT @Version
  B)  SELECT @@Version
  C)  SELECT @@SQLVersion
  D) ALL
  Answer: B)  SELECT @@Version

  Question:  43
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
how to check the percentage of backup completed in sql 2000:
  A) sp_spaceused dbname
  B) dbcc inputbuffer(spiD)  
  C)  dbcc outputbuffer(spiD)  
  D) none
  Answer: C)  dbcc outputbuffer(spiD)

  Question:  44
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
How to connect to SQL server using DAC
  A) sqlcmd A d master
  B) Admin:serverinstance
  C)  Both
  D) None
  Answer: C)  Both

  Question:  45
  UCF:  SQL 2.1
  Topic: TOOLS_UTIL_10
How to map an existing database user to a SQL Server login
  A) sp_synclogins
  B)  sp_change_users_login
  C)  sp_change_user_login
  D) sp_change_login_users
  Answer: B)  sp_change_users_login

  Question:  46
  UCF:  SQL 2.1
  Topic: WINDOWS_OS_5
How to start the sql server in single user mode from command prompt?
  A) net start mssqlserver -m
  B) net start mssqlserver -c
  C)  net start mssqlserver -f
  D) net start mssqlserver /t
  Answer: A) net start mssqlserver -m

  Question:  47
  UCF:  SQL 2.1
  Topic: WINDOWS_OS_5
In order to secure SQL Server 2005 installation, which privileges should be granted to Service Accounts?
  A) Run SQL Server services with the lowest possible privileges.
  B) Run SQL Server services with the highest possible privileges.
  C)  Grant Full Control permission to Service Account
  D) SQL Server setup automatically grants the service account required permissions.
  Answer: A) Run SQL Server services with the lowest possible privileges.

  Question:  48
  UCF:  SQL 2.1
  Topic: WINDOWS_OS_5
On an 8 processor 32 bit sql server machine, how many MAX WORKER THREADS will be there?
  A) 128
  B) 256
  C)  288
  D) 98
  Answer: C)  288

  Question:  49
  UCF:  SQL 2.1
  Topic: WINDOWS_OS_5
Page file setting is the part of
  A)  Operating System
  B) SQL Database Engine
  C)  SQL Server
  D) SSIS
  Answer: A)  Operating System

  Question:  50
  UCF:  SQL 2.1
  Topic: WINDOWS_OS_5
Security policy can be configured using from Command Prompt
  A) secpol
  B)  secpol.msc
  C)  secpol.msi
  D) None
  Answer: B)  secpol.msc
 
 


SQL 2.2

  Question:  1
  UCF:  SQL 2.2
  Topic: DBA_17
You work as a Database Developer for Miracle Infocom. The company uses SQL server 2000 database to maintain data for the Human Resource department. You design a month salary sheet view, which joins several tables and calculates aggregate information. You want to provide a parameterized query to access the data contained in the salary sheet view. The output of this query will be used in other SELECT lists. What will you do?
  A) Create an inline user-defined function that accepts the parameter as input.
  B) B.Create a stored procedure that accepts the parameter as input and returns a rowset with the result set.
  C)  C.Create a scalar user-defined function that accepts the parameter as input.
  D) D.Use an ALTER VIEW statement to add the parameter value to the view definition.
  Answer: A) Create an inline user-defined function that accepts the parameter as input.

  Question:  2
  UCF:  SQL 2.2
  Topic: DBA_17
You work as a network administrator at Miracle Info.com. The Miracle Info.com network consists o f a single Windows 2000 domain named Miracle Info.com. All servers on the Miracle Info.com network run Windows 2000 Server. The Miracle Info.com network contains a member server named Miracle Info -SR10. The physical hardware on Miracle Info -SR10 is shown in the following table. You n eed to ensure that SQL Server 2000 can be installed on Miracle Info – SR10. What should you do?
  A) A.Install more RAM on Miracle Info – SR10.
  B) Upgrade the CPU on Miracle Info -SR10.
  C)  C.Install a second hard drive on Miracle Info -SR10.
  D) D.Do nothing; SQL Server 2000 can be installed on Miracle Info -SR10 .
  Answer: B) Upgrade the CPU on Miracle Info -SR10.

  Question:  3
  UCF:  SQL 2.2
  Topic: DBA_17
You work as the database administrator at Miracle Info.com. The Miracle Info.com network consists of a single Active Directo ry domain named Miracle Info.com. All servers on the Miracle Info.co m network run Windows 2000 Server. The Miracle Info.com network contains a SQL Server 2000 database server named Miracle Info -DB01 that runs on Windows 2000 Server. Miracle Info -DB01 contains a database named CK_Sales. Miracle Info.com intends migrating Miracle Info -DB01 to SQL Server 2005 in the near future. To prepare for the migration, you decide to install SQL Server 2005 Express on a server named Miracle Info -SR3 1. You verify that Miracle Info -SR 31 Exceeds the minimum recommended hardware requirements for SQL Server 2005. What should you do next?
  A) A.Upgrade Miracle Info -SR31 to Windows Sever 2003 and install Service Pack
  B) B.Install and run the SQL Server Upgrade Advisor on Miracle Info -SR 31.
  C)  C.Install SQL Server 2000 on Miracle Info -SR31.
  D) Download and install the .NET Framework 2.0.
  Answer: D) Download and install the .NET Framework 2.0.

  Question:  4
  UCF:  SQL 2.2
  Topic: DBA_17
You work as the database administrator at Miracle Info.com. The Miracle Info.com network consists of a single Active Directo ry domain named Miracle Info.com. All servers on the Miracle Info.com network run Windows 2000 Server. The Miracle Info.com network contains a SQL Server 7.0 database server named Miracle Info -DB01 that runs on Windows 2000 Server. Service Pack 4 for Windows 2000 Server id installed on Miracle Info -DB01. Miracle Info.com wants to migrate Miracle Info -DB01 to SQL Server 2005. You decide to perform an in-place upgrade to SQL Server 2005. You verify that Miracle Info -DB01 exceeds the recommended hardware requirements for SQL Server 2005. What should you do next?
  A) A.Up grade Miracle Info -DB01 to Windows Sever 2003.
  B) Install Service Pack 4 for SQL Server 7.0 on Miracle Info -DB01.
  C)  C.Upgrade SQL Server 7.0 on Miracle Info -DB01 to SQL Server 2000.
  D) D.Run the SQL Server 2005 Setup utility on Miracle Info -DB01 and upgrade directly to SQL Server 2005.
  Answer: B) Install Service Pack 4 for SQL Server 7.0 on Miracle Info -DB01.

  Question:  5
  UCF:  SQL 2.2
  Topic: DBA_17
You work as the database administrator at Miracle Info.com. The Miracle Info.com network consists of a single Active Directory domain named Miracle Info.com. All servers on the Miracle Info.com network run Windows 2000 Server. The Miracle Info.com network contains a SQL Server 2000 database server named Miracle Info -DB01 that runs on Windows 2000 Server Service Pack 4. Miracle Info – DB01 contains a database n amed CK_Trading.Miracle Info.com wants to migrate Miracle Info -DB01 to SQL Server 2005 but wants to retain the CK_Trading database with the same instance name. You decide to perform an in-place upgrade to SQL Server 2005. You verify that Miracle Info -DB01 exceeds the minimum recommended hardware requirements for SQL Server 2005. What should you do next?
  A) A.Upgrade Miracle Info -DB01 to Windows Sever 2003 Service Pack
  B) Run the SQL Server Upgrade Advisor on Miracle Info -DB01.
  C)  C.Detach th e CK_Trading database on Miracle Info –
  D) D.B01. D.Run the SQL Server 2005 Setup utility.
  Answer: B) Run the SQL Server Upgrade Advisor on Miracle Info -DB01.

  Question:  6
  UCF:  SQL 2.2
  Topic: DBA_17
You work as the database administrator at Miracle Info.com. The Miracle Info.com network consists of a single Windows 2000 domain named Miracle Info.com. All servers on the Miracle Info.com network run on Windows 2000 Server while the client computers run Windows 2000 Professional, Windows 98, and Novell NetWare. All computers are members of the Miracle Info.com domain. The Miracle Info.co m network contains a SQL Server 2000 database server named Miracle Info -DB01 that hosts a default instance of SQL Server 2000. Miracle Info.com wants to implement another SQL Server 2000 instance for a database named Sales_DB. Sales_DB will store sales data for the company. All Miracle Info.com users will require access to the Sales_DB database ase. You have been instructed to create the SQL Server 2000 instance for the Sales_DB database. You need to ensure that all users will be able to connect to the Sales_DB database. You cannot make configuration changes to the client computers. Your solution must optimize network traffic. What should you do?
  A) Install the instance as a named instance and configure it to support Mixed Mode Authentication. Then enable the NWLink IPX/SPX Net-Library for the instance.
  B) B.Install the instance as a default instance and configure it to support Windows Authentication. Then enable the TCP/IP Sockets Net-Library for the instance.
  C)  C.Install the instance as a Named Instance and configure it to support SQL Server Authentication. Then enable the IPX/SPX for the instance.
  D) D.Install the instance as a default instance and configure it to support Windows Authentication. Then enable the TCP/IP Sockets Net-Library for the instance.
  Answer: A) Install the instance as a named instance and configure it to support Mixed Mode Authentication. Then enable the NWLink IPX/SPX Net-Library for the instance.

  Question:  7
  UCF:  SQL 2.2
  Topic: DBA_17
You work as the database administrator at Miracle Info.com. The Miracle Info.com network consists of a single Windows 2000 domain named Miracle Info.com. All servers on the Miracle Info.com network run Windows 2000 Advanced Server. The Miracle Info.com network contains a SQL Server 2000 database server named Miracle Info -DB01. Miracle Info -DB01 is a production server and hosts a database named CK_Sales which stores sales transactions for the company. Miracle Info.com has database application developer named Rory Allen who uses a Windows 2000 Professional client computer named Miracle Info -WS228. Ro ry Allen needs to test a database application on Miracle Info -DB01. You install a separate a SQL Server 2000 instance named RA_Test on Miracle Info -DB01. You need to ensure that Rory Allen can connect to the RA_Test instance. What should you do?
  A) Configure the RA_Test instance to use the Named Pipes Net-Library.
  B) B.Configure the RA_Test instance to use the Multiprotocol Net-Library.
  C)  C.Configure the RA_Test instance to use the NWLink IPX/SPX Net-Library.
  D) D.Configu re the RA_Test instance to use the Shared Memory Net-Library.
  Answer: A) Configure the RA_Test instance to use the Named Pipes Net-Library.

  Question:  8
  UCF:  SQL 2.2
  Topic: DBA_17
You work as the database administrator at Miracle Info.com. The Miracle Info.com network consists of a single Windows 2000 domain named Miracle Info.com. The Miracle Info.com network contains a SQL Server 7.0 database server named Miracle Info -DB01 that runs on Windows NT Server 4.0. Miracle Info -DB01 is a production server and hosts several databases for the company. You install SQL Server 2000 as the default instance on a new database server named Miracle Info -DB02. Miracle Info -DB02 runs Windows 2000 Server. Miracle Info.com users in the Accounts department must have d direct access to the databases on both Miracle Info -DB01 and Miracle Info -DB02. The users in the Accounts department use client computers that run Windows 2000 Professional. You need to encrypt the network communication between the Accounts department users’ client computers and the database servers. What should you do?
  A) A.Configure the client computers to use the Named Pipes Net-Library.
  B) Configure the client computers to use the Multipro tocol Net-Library.
  C)  C.Configure the client computers to use the TCP/IP Sockets Net-Library.
  D) D.Configu re the client computer to use the Shared Memo ry Net-Library.
  Answer: B) Configure the client computers to use the Multipro tocol Net-Library.

  Question:  9
  UCF:  SQL 2.2
  Topic: DBA_17
You work as the database administrator at MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all database servers run SQL Server 2005. The MiracleInfocom .com network contains two database servers named MiracleInfocom -DB01 and MiracleInfocom -B02. MiracleInfocom -DB01 hosts a database named MI_Sales that stores sales data for the company. The MI_Sales database is also used for online transaction processing (OLTP). MiracleInfocom .com develops a new Web application that you deploy on MiracleInfocom -DB01. MiracleInfocom .com’s Web customers start complaining about excessive delays and intermittent timeout errors. You monitor the systems resources on MiracleInfocom -DB01 on Windows System Monitor and are satisfied that system resource usage is well within expected parameters. You request that the application developers test the Web application and identify the cause of the performance problems. The application developers report that the problem is related to the time required to retrieve data from MI_Sales. You need to create a SQL Server Profiler trace to identify the poorly performing queries in the Web application. What should you do?
  A) Capture events related to the Sessions and Stored Procedure event classes. Group data by EventClass.
  B)  Capture events related to the Stored Procedure and TSQL event classes. Group data by Duration.
  C)  Capture events related to the ExistingConnection event classes. Group data by EventClass.
  D) Capture events related to the ExistingConnection and Sessions event classes. Group data by ClassProcessID
  Answer: B)  Capture events related to the Stored Procedure and TSQL event classes. Group data by Duration.

  Question:  10
  UCF:  SQL 2.2
  Topic: DBA_17
You work as the database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a SQL Server 2005 database server named MiracleInfocom -DB01. MiracleInfocom -DB01 hosts a database named MI_Sales that stores sales data for the company. MiracleInfocom -DB01 also hosts two financial applications. Several MiracleInfocom .com users in the Sales department complain that queries against the MI_Sales database perform very poorly at times. You use System Monitor to monitor the performance of by using MiracleInfocom -DB01 and discover that SQL Server 2005 does not have sufficient memory to run efficiently. You need to ensure that SQL Server 2005 always has sufficient memory to run efficiently. What should you do?
  A) Decrease the min memory per query server option.
  B)  Increase the min server memory server option.
  C)  Increase the max worker threads server option.
  D) Decrease the max server memory server option.
  Answer: B)  Increase the min server memory server option.

  Question:  11
  UCF:  SQL 2.2
  Topic: DBA_17
You work as the database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. You use a client computer named MiracleInfocom -WS444. The MiracleInfocom .com network contains a SQL Server 2005 database server named MiracleInfocom -DB01. MiracleInfocom -DB01 hosts a database named MI_Products. The recent increase in database usage at the company has resulted in several MiracleInfocom .com users complaining of poor query performance and query timeouts try to retrieve data from the MI_Products database. You need to determine the cause of the poor performance. You need to minimize the effect of your monitoring strategy on system performance. What should you do?
  A) Run the SQL Server Profiler from MiracleInfocom -WS444 and create a trace that monitors MiracleInfocom -DB01. Save the trace file to a table.
  B) Run the Performance tool on MiracleInfocom -DB01 to create a log that monitors MiracleInfocom -DB01. Save the log file to a folder on MiracleInfocom -DB01.
  C)  Run the SQL Server Profiler on MiracleInfocom -DB01 and create a trace that monitors MiracleInfocom -DB01. Save the trace file to a table
  D)  Run the Performance tool on MiracleInfocom -WS444 to create a log that monitors MiracleInfocom -DB01. Save the log file to a folder on MiracleInfocom -WS444.
  Answer: D)  Run the Performance tool on MiracleInfocom -WS444 to create a log that monitors MiracleInfocom -DB01. Save the log file to a folder on MiracleInfocom -WS444.

  Question:  12
  UCF:  SQL 2.2
  Topic: DBA_17
You work as the database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a SQL Server 2005 database server named MiracleInfocom -DB01. MiracleInfocom -DB01 is running a default SQL Server instance and a named instance named MI_Sales. MiracleInfocom .com users complain that performance for both instances has deteriorated over the last six months. You want to determine the memory and processor resource requirements of the two instances as well as any resource conflicts between the two instances. What should you do?
  A) Use SQL Server Profiler to create a trace and capture typical activity for each instance with both instances running.
  B)  Use Windows Performance to create counter log that captures performance counters for each instance with both instances running.
  C)  Use Windows Performance to create counter log that captures performance counters for each instance separately with each instance running on its own.
  D) Use SQL Server Profiler to create a trace and capture typical activity for each instance separately with each instance running on its own.
  Answer: B)  Use Windows Performance to create counter log that captures performance counters for each instance with both instances running.

  Question:  13
  UCF:  SQL 2.2
  Topic: DBA_17
You work as the database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. You are in the process of collecting information that will be required for ongoing server management which includes hardware performance statistics. You are required to collect the statistics during average use and peak periods. You want to collect the hardware baseline performance statistics. What should you do?
  A)  A Windows counter log should be created.
  B) A Windows Alerts should be created.
  C)  A Windows trace log should be created.
  D) A SQL Server Profiler trace should be created.
  Answer: A)  A Windows counter log should be created.

  Question:  14
  UCF:  SQL 2.2
  Topic: DBA_17
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. Recently the MiracleInfocom .com network users started complaining that server response has degraded rapidly. You decided to monitor the following counters: 1. Processor: % Processor Time You discover that the performance counter is consistently over 90 percent. The Baseline values for this counter are typically in the 40 to 60 percent range. During the course of the day you determine that the SQL Server process accounts for less than 50% of the processor time. You are required to identify and correct the problem as quickly as possible. What should you do?
  A) The server should be scanned for missing updates.
  B) The server should be rebooted.
  C)  The SQL Server service should be stopped.
  D)  The server should be scanned using anti-virus software.
  Answer: D)  The server should be scanned using anti-virus software.

  Question:  15
  UCF:  SQL 2.2
  Topic: DBA_17
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?
  A) DBCC CHECKTABLE
  B) ALTER INDEX REORGANIZE
  C)  UPDATE STATISTICS
  D) DBCC SHOW_STATISTICS
  Answer: C)  UPDATE STATISTICS

  Question:  16
  UCF:  SQL 2.2
  Topic: DBA_17
Your SQL Server is experiencing internal memory pressure, how do you resolve this?
  A) Run DBCC Memorystatus, see how the memory is distributed and then take action
  B) B. Run DBCC inputbuffer, see how the memory is distributed and then take action
  C)  C. Run sys.dm_exec_cached_plans, see how the memory is distributed and then take action
  D) Run DBCC Outputbuffer, see how the memory is distributed and then take action
  Answer: A) Run DBCC Memorystatus, see how the memory is distributed and then take action

  Question:  17
  UCF:  SQL 2.2
  Topic: DBA_17
Your work as a network administrator at Miracle Info.com. The Miracle Info.com Net work consists of a single Windows 2000 forest. The Miracle Info.com forest has a single root domain named Miracle Info.com. All servers on the Miracle Info.com network run Windows 2000 Server. The Miracle Info.com network contains a member server named Miracle Info -SR12. You install SQL Server 2000 on Miracle Info -SR12 and configure the MS SQL Server service to run under a domain u ser account n amed SQLAdmin in the Miracle Info.com root domain. You add the SQLAdmin account to the Enterprise Admins group. You start Miracle Info -SR12 but the MSSQLServer service does not start. You need to ensure that the MS SQL Server service starts.What should you do?
  A) Configure the MS SQL Server service to run under the local System account on Miracle Info -SR12.
  B) B.Add the SQL Admin user account to the Do main Admins group in the Miracle Info.com root domain.
  C)  C.Add the Enterprise Admins group to the Domain Admins group in the Miracle Info.com root domain.
  D) D.Configure the MSSQLServer service and the MSSQLServerAgent service to run under the same user account.
  Answer: A) Configure the MS SQL Server service to run under the local System account on Miracle Info -SR12.

  Question:  18
  UCF:  SQL 2.2
  Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Microsoft Windows Server 2003 and the database server runs Microsoft SQL Server 2005. The MiracleInfocom .com network users use a database called Billventory. You need to replicate the Billventory database across all the servers and you are concerned with the distribution database growing very large. You are required to minimize the size of the distribution database and you want o increase the frequency that delivered transactions from the distribution database are deleted. You must accomplish your goal using the least amount of administrative effort. What should you do?
  A) A new job should be created that automatically delete the replicated Agent history
  B) The Replication agents checkup job should be configured to run more frequently
  C)  The Expired subscriptions cleanup job must be configured to run twice a day
  D)  The frequency that the Agent History clean up job runs should be increased
  Answer: D)  The frequency that the Agent History clean up job runs should be increased

  Question:  19
  UCF:  SQL 2.2
  Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains three Microsoft SQL Server 2005 servers which you administer. The MiracleInfocom .com network includes a mix of databases configured for Full Recovery model and Simple Recover Model. A new MiracleInfocom .com security policy specifies that database servers: 1. All database server should be secured against logon and local access 2. Database servers must be backed up on a predefined schedule 3. Any database should be recoverable to no more than six hours lost 4. The backups must be protected against unauthorized access 5. A copy of the data no more than a week old must be maintained offsite The MiracleInfocom .com network servers are locked in a room with file servers used as the backup destination for all database servers. The Full backups run monthly and the differential backups run nightly. You are required to ensure that all security policy guidelines are met. What should you do?
  A) A second file server should be deployed in the locked room and run mirror backups.
  B) Transaction log backups must be run every six hours.
  C)  Mirrored backups should be run to another file server located in a different physical location in the network.
  D)  The differential backup frequency should be changed to every six hours.
  Answer: D)  The differential backup frequency should be changed to every six hours.

  Question:  20
  UCF:  SQL 2.2
  Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains ten Microsoft SQL Server 2005 servers which you administer. MiracleInfocom .com management has recently decided to tighten network security. A new MiracleInfocom .com security policy requires that only network users with security clearance can manage security principals and roles. You additionally need to audit the users who are currently making such changes on the database servers whilst your solution does not impact performance more than required. What should you do?
  A) Enable object access event auditing in Audit Policy.
  B) Enable auditing for account management events in Audit Policy.
  C)  C2 audit mode for the SQL Server instance must be enabled.
  D)  A SQL Server Profiler trace should be created.
  Answer: D)  A SQL Server Profiler trace should be created.

  Question:  21
  UCF:  SQL 2.2
  Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. MiracleInfocom -DB01 is configured for Windows Authentication. You are busy configuring user access and permissions. Most of the MiracleInfocom .com users have the same access requirements at the server and database level but there are some exceptions. The MiracleInfocom .com network users will access the database through a custom application, stored procedures and ad hoc queries. A new MiracleInfocom .com security policy requires that access to the MiracleInfocom Data must be limited to Active Directory user accounts only. User accounts who are not members of the Domain Admins group are not configured as database administrators. You are required to configure user access to minimize the effort required to assign and audit user access permissions. What should you do?
  A)  Access permissions should be assigned to Active Directory groups.
  B) Logins and database users should be created based on Active Directory user accounts.
  C)  Access permissions should be assigned based on application roles.
  D) Access permissions should be assigned based on user accounts.
  Answer: A)  Access permissions should be assigned to Active Directory groups.

  Question:  22
  UCF:  SQL 2.2
  Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. MiracleInfocom -DB01 is used to store confidential company information. You established a domain security policy which requires every action to change permission on the database server to be logged. MiracleInfocom -DB01 can only be running when logging is enabled. You are required to enforce the security policy on the database server. What should you do?
  A)  By using the sp_configure statement c2 audit mode should be enabled.
  B) From the Security page of MiracleInfocom -DB01’s Properties windows auditing should be enabled.
  C)  Through a Group Policy Object (GPO) an audit policy must be enabled.
  D) Through the Local Security Policy auditing must be enabled.
  Answer: A)  By using the sp_configure statement c2 audit mode should be enabled.

  Question:  23
  UCF:  SQL 2.2
  Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. You have received instruction from the CIO to install and configure an additional database server named MiracleInfocom -DB02 which will be maintained using scheduled Maintenance Plans. You configure the settings so that if a Maintenance Plan fails the server will send you an e-mail. You are required to configure the surface area of MiracleInfocom -DB02 to support the required functionality whilst keeping your solution as secure as possible. What should you do?
  A) Service Broker endpoints should be enabled.
  B) The SQL Server Browser service must be configured to start automatically.
  C)  SQL Mail should be enabled.
  D)  The SQL Server Agent service must be configured to start automatically.
  Answer: D)  The SQL Server Agent service must be configured to start automatically.

  Question:  24
  UCF:  SQL 2.2
  Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. The MiracleInfocom .com network uses a database called Bill EmployeeData which is used to create monthly reports. All of the network employees have a SQL Server login to access the server. A new MiracleInfocom .com security policy requires that all the SQL logins use the same complexity and password expiration policies used by Microsoft Windows Server 2003. The networks Windows Server 2003 computers are configured to require complex passwords and periodic password changes. The security policy additionally states that all users must change their password the first time when login onto MiracleInfocom -DB01. MiracleInfocom .com has recently hired a new employee who requires access to the database server using the account named Rory A. You are required to create a SQL Server login account for the user that adheres to all the appropriate requirements. What should you do?
  A)  You should execute CREATE LOGIN RoryA WITH PASSWORD = Moles4M4 MUST_CHANGE CHECK_EXPIRATION = ON, CHECK_POLICY = ON
  B) You should execute CREATE LOGIN RoryA WITH PASSWORD = Moles4M4 MUST CHANGE CHECK_POLICY = OFF
  C)  You should execute CREATE LOGIN RoryA WITH PASSWORD = Moles4M4 MUST Change CHECK_POLICY = ON
  D) You should execute CREATE LOGIN RoryA WITH PASSWORD = Moles4M4 CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
  Answer: A)  You should execute CREATE LOGIN RoryA WITH PASSWORD = Moles4M4 MUST_CHANGE CHECK_EXPIRATION = ON, CHECK_POLICY = ON

  Question:  25
  UCF:  SQL 2.2
  Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 configured to support both Windows authentication and SQL Server authentication. You have received instruction from the CIO to audit the passwords for the SQL Server logins. You are required to determine which logins require strong passwords and which require password expiration. What should you do?
  A) Query the sys.syslogins.
  B) Query the sys.login_token.
  C)  Query the sys.server_principals.
  D)  Query the sys.sql_logins.
  Answer: D)  Query the sys.sql_logins.

  Question:  26
  UCF:  SQL 2.2
  Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 configured to support Windows authentication and SQL Server authentication. You have received instruction from the CIO to audit permissions granted to several sensitive tables. You are required to write a stored procedure to retrieve information on the permissions granted the principal to which it was granted and the principal to who granted them. The stored procedure is required to support the server even if you upgrade the SQL server to a newer version. What should you do?
  A) The sys.syspermissions should be queried.
  B) The sys.fn_builtin_permissions should be queried.
  C)  The sys.server_permissions should be queried.
  D)  The sys.database_permissions should be queried.
  Answer: D)  The sys.database_permissions should be queried.

  Question:  27
  UCF:  SQL 2.2
  Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. MiracleInfocom-DB01 includes a default instance and two named instances of SQL Server 2005. The MiracleInfocom-DB01 will only be accessed by users in the Finance department. All computers in the Finance department are located in the same subnet. The MiracleInfocom .com network financial application uses instance names to connect to MiracleInfocom -DB01. The application is not configurable. During the course of the day you are informed about a worm attack that accesses a computer using port 1434. You are required to protect MiracleInfocom -DB01 from the worm. What should you do?
  A) The HideInstance flag should be set on all instances of SQL Server.
  B) The Windows Firewall on MiracleInfocom -DB01 should be configured to block port 1434.
  C)  The SQL Server Browser service should be disabled.
  D)  An Internet Protocol Security (IPSeC)   policy should be enabled on the database server.
  Answer: D)  An Internet Protocol Security (IPSeC)   policy should be enabled on the database server.

  Question:  28
  UCF:  SQL 2.2
  Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. The MiracleInfocom .com network has its headquarters in Chicago and a branch office in Miami The MiracleInfocom .com network contains two Microsoft SQL Server servers named MiracleInfocom -SR01 in the main office running SQL Server 2005 configured as the Publisher and MiracleInfocom -SR02 at the branch office running SQL 2000 configured as the Distributor. The MiracleInfocom .com network uses transactional replication with queued updating subscribers to synchronize data between locations. You define the following publications on MiracleInfocom -SR01: 1. FinancialData 2. FinancialStatusData Since MiracleInfocom -SR02 acts as the distributor most subscriptions are implemented as push subscriptions. You monitor the replication and discover that changes are not always replicated to the stores because of connection timeouts and you need to resolve the problem. What should you do?
  A) The QueryTimeout value should be increased in the Log Reader Agent profile
  B) The PollingInterval should be decreased for each Distribution Agent
  C)  The PollingInterval value should be decreased in the Queue Reader Agent profile
  D)  The LoginTimeout value should be increased in the Distribution Agent profile
  Answer: D)  The LoginTimeout value should be increased in the Distribution Agent profile

  Question:  29
  UCF:  SQL 2.2
  Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom.com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. The MiracleInfocom .com network management has recently informed you that you should have a password policy enabled on MiracleInfocom -DB01. You enabled a password policy on MiracleInfocom -DB01. All passwords are required to meet complexity requirements. You created a SQL login using the following command: 1. CREATE LOGIN RoryA WITH PASSWORD = Rory3M4 MUST_CHANGE CHECK_EXPIRATION = ON, CHECK_POLICY = ON When Rory Allen attempts to log onto the SQL server the first time and attempts to change the password to RoryAll23$_a he receives an error message indicating that he cannot use the new password. You need to find out what is causing the problem. What should you do?
  A)  Part of the user name is in the password.
  B) At least one invalid character is in the password.
  C)  There are no uppercase letters in the password.
  D) The minimum number of characters is not used in the password.
  Answer: A)  Part of the user name is in the password.

  Question:  30
  UCF:  SQL 2.2
  Topic: MT_15
You work as the network SQL developer at MiracleInfocom .com. All servers on the MiracleInfocom .com network run Microsoft Windows Server 2003 and the database server runs Microsoft SQL Server 2005. You are currently busy configuring the MiracleInfocom.com database server named MiracleInfocom -DB01. You will be required to perform distributed queries against a database that is stored on a SQL server named MiracleInfocom -SR02 which hosts four databases each using a different collation. You have received instruction from the CIO to add MiracleInfocom -SR02 as a linked server on MiracleInfocom -SR01 and you need to make sure the distributed queries are evaluated correctly using the least amount of administrative effort. What should you do?
  A)  You should set the Use Remote Collation option to True
  B) You should set the Use Remote Collation option to False
  C)  You should use OPENROWSET to retrieve data from MiracleInfocom -SR02
  D) You should specify a collation name
  Answer: A)  You should set the Use Remote Collation option to True

  Question:  31
  UCF:  SQL 2.2
  Topic: MT_15
Your company has two SQL Server 2005 computers named SQL1 and SQL2. Transaction log shipping occurs from SQL1 to SQL2 by using default SQL Server Agent schedule settings. You need to reconfigure re transaction log shipping to provide minimum latency on SQL2. What should you do?
  A) On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, maintain default schedule settings for both the log shipping copy and the restore jobs.
  B) On SQL1, change the schedule type for the transaction log backup to Start automatically when SQL Server Agent starts. On SQL2, change the schedule types for both the log shipping copy and the restore jobs to Start automatically when SQL Server Agent starts.
  C)  On SQL1, maintain default schedule settings for the transaction log backup job. On SQL2, change the schedule types for both the log shipping copy and the restore jobs to Start automatically w+E33hen SQL Server Agent starts.
  D) On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, reschedule both the log shipping copy and the restore jobs so that they occur every minute.
  Answer: D) On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, reschedule both the log shipping copy and the restore jobs so that they occur every minute.

  Question:  32
  UCF:  SQL 2.2
  Topic: MT_15
Your Orders table is defined as follow: CREATE TABLE Orders ( OrderID Int IDENTITY (1,1) NOT NULL, SalesPersonID Int NOT NULL, RegionID Int NOT NULL, OrderDate Datetime NOT NULL, OrderAmount Int NOT NULL) The table is becoming too large to manage. You must delete all sales that are more than three years old. Which query will accomplish the desired result?
  A)  DELETE FROM Orders WHERE OrderDate DATEADD(YY,-3,GETDATE())
  B) DELETE FROM Orders WHERE OrderDate DATEADD(YY,3,GETDATE())
  C)  DELETE FROM Orders WHERE OrderDate GETDATE(), -3
  D) DELETE FROM Orders WHERE OrderDate GETDATE(),+3
  Answer: A)  DELETE FROM Orders WHERE OrderDate DATEADD(YY,-3,GETDATE())

  Question:  33
  UCF:  SQL 2.2
  Topic: NETWORKING_5
How do you check the tcp port in SQL Server 2000
  A) client network utility
  B) server network utility
  C)  sql configuration manager
  D) all of the above
  Answer: B) server network utility

  Question:  34
  UCF:  SQL 2.2
  Topic: NETWORKING_5
How many Endpoints are created in SQL Server 2005 ?
  A) 4
  B) 5
  C)   6
  D) 8
  Answer: C)   6

  Question:  35
  UCF:  SQL 2.2
  Topic: NETWORKING_5
How to check the connectivity of the remote server from the local desktop?
  A) ping local servername
  B) ping ,remote servername
  C)  Ping local, remote serevrname
  D) None of the above
  Answer: B) ping ,remote servername

  Question:  36
  UCF:  SQL 2.2
  Topic: NETWORKING_5
How to enable Remote connection in SQL Server 2005
  A) Surface Area Configuration Tool
  B) Configuration Manager
  C)  SQL Configuration
  D) None of the above
  Answer: A) Surface Area Configuration Tool

  Question:  37
  UCF:  SQL 2.2
  Topic: NETWORKING_5
Named Pipes protocol is used with Large Networks as it supports support routing and gateways
  A) TRUE
  B)  False
  Na
  NA
  Answer: B)  False

  Question:  38
  UCF:  SQL 2.2
  Topic: REPLICATION_13
You want to find the latency in transmitting data between the distribution and the subscription database in transactional replication. The time difference between the distributor_commit column in Mstracer_tokens table and the subscriber_commit column in Mstracer_history table will give you the latency for the Subscriber server. In which database can you find these two system tables?
  A) distribution database
  B) publication database
  C)  subscription database
  D) msdb database
  Answer: A) distribution database

  Question:  39
  UCF:  SQL 2.2
  Topic: REPLICATION_13
You work as a database administrator for MiracleInfocom .com. MiracleInfocom .com is a book distributor and its network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Microsoft Windows Server 2003 and the database servers run Microsoft SQL Server 2005. Your job description at MiracleInfocom .com includes the administration of the database servers named MiracleInfocom -DB01 and MiracleInfocom -DB02 respectively. MiracleInfocom -DB01 is on the internal network and MiracleInfocom -DB02 is on the perimeter network. Both these servers are configured to make use of transactional replication with immediate -updating subscribers to replicate the BookTitle table and the InStock table of the Inventory database. You have a suspicion that the replicas are non-convergent. Now you need to check which of the rows are different. What should you do?
  A) You should run sp_publication_validation utility.
  B)  You need to run tablediff utility.
  C)  You need to enable the replication: Subscriber has failed data validation alert.
  D) You need to enable the Validate Subscriptions option on the publication.
  Answer: B)  You need to run tablediff utility.

  Question:  40
  UCF:  SQL 2.2
  Topic: REPLICATION_13
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. The MiracleInfocom .com network has its headquarters in Chicago and branch offices in Miami and Dallas. The MiracleInfocom .com network uses a database called Billventory. The network branch offices are connected to the main office via a high speed Wide Area Network (WAN) connection. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 server in each branch office. The changes on each database in the branch offices should be replicated to the database in the main office and all the tables used in replication have a unique primary key. During the course of the day you implement transactional replication between the SQL servers in the branch offices and the main office. You are required to be notified when the amount of time for a transaction to be committed as the publisher and committed at the Subscriber exceeds a timeout specified in the design document that has been provided. What should you do?
  A)  A replication alert must be configured to generate an e-mail message
  B) A warning should be enabled on the Publisher an specify the appropriate subscription expiration threshold based on the design document value
  C)  The Replication: agent success predefined replication alert should be enabled on the Publisher
  D) A warning should be enabled on the Publisher and set the appropriate threshold for synchronization time based on the design document value
  Answer: A)  A replication alert must be configured to generate an e-mail message

  Question:  41
  UCF:  SQL 2.2
  Topic: REPLICATION_13
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. The MiracleInfocom .com network has its headquarters in Chicago and branch offices in Miami, Dallas and Detroit. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 server in each office. The database server in the main office is the Publisher and Distributor for merge replication. The network database server in the branch offices are configured as Subscribers and the Subscriptions are configured as pull subscriptions. During the course of the day you configure merge replication because the connection between the offices is intermittent and the data at the Subscribers rarely changes. You want to resolve conflicts manually when they occur. You are required to ensure that the SQL Server does not automatically resolve conflicts whilst you identify how you would resolve the conflicts that occur. What should you do?
  A)  The Interactive Resolver should be used to resolve replication conflicts
  B) The conflict tracking level should be configured for row level
  C)  The Conflict Viewer should be used to resolve replication conflicts
  D) All subscriptions should be configures as push subscriptions
  Answer: A)  The Interactive Resolver should be used to resolve replication conflicts

  Question:  42
  UCF:  SQL 2.2
  Topic: REPLICATION_13
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. The MiracleInfocom .com network has its headquarters in Chicago and branch offices in five cities. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 server at the main office and a SQL Server at each of the cities. The main office database server will be configured as the Publisher and Distributor whilst the remaining servers are configured as Subscribers who are rarely updated. You ensure that when updates of the subscribers conflicts with the Publisher the Publisher always takes precedence. The connections between the Publisher/Distributor and the Subscribers are periodic and are configured to occur after business hours only. You are required to design a replication configuration that meets the update and conflicts resolution requirements whist you use the least amount of administrative effort. The Publisher and Subscribers are required to remain synchronized with identical data. What should you do?
  A)  Transactional replication must be configured with queued-updating Subscribers
  B) Merge replication must be configured with a custom managed code resolver
  C)  Merge replication should be configured for manual resolution
  D) The Transactional replication should be configured with immediate-updating Subscribers
  Answer: A)  Transactional replication must be configured with queued-updating Subscribers

  Question:  43
  UCF:  SQL 2.2
  Topic: REPLICATION_13
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. The MiracleInfocom .com network has its headquarters in Chicago and four branch offices. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 server in the main office and one in each branch office. The main office SQL server is configured to act as the Publisher and Distributor for transactional replication. The network branch offices are all configured as subscribers and all subscriptions are configured as push subscriptions. The Data is treated as read-only at the remote offices. You are required to validate connections between the Distributor and the Subscribers and measure the replication latency whilst you collect statistics about latency that is accurate as possible. What should you do?
  A)  The Replication Monitor must be used to insert tracer tokens and gather token statistics from the Publisher/Distributor and all subscribers
  B) The Replication Monitor should be used to set a latency threshold and generate an alert if latency exceeds the threshold
  C)  The Replication Monitor should be used to view the Queue Reader agent history statistics
  D) The Replication Monitor should be used to view Current Average Performance and Current Worst Performance statistics
  Answer: A)  The Replication Monitor must be used to insert tracer tokens and gather token statistics from the Publisher/Distributor and all subscribers

  Question:  44
  UCF:  SQL 2.2
  Topic: REPLICATION_13
You work as the network SQL developer at MiracleInfocom .com. All servers on the MiracleInfocom .com network run Microsoft Windows Server 2003 and the database servers run Microsoft SQL Server 2005. The MiracleInfocom .com network contains two servers which hosts the FinancialData database. The second server runs Oracle Relational Database Management System (RDBMS) and hosts the MiracleInfocom Inventory database. The Inventory database is updated frequently by members of the Finance department. The MiracleInfocom .com network users in the Marketing department need to be able to create daily reports that combine information from both the FinancialData and MiracleInfocom Inventory databases to be run hourly. You have received instruction from the CIO to have your solution minimize the effort required for the database developers to run ad hoc queries no data on both SQL servers to test development requirements. The MiracleInfocom .com network users are required to create reports on the SQL server and you want to minimize the disk space used on the SQL Server whilst using the least administrative effort. What should you do?
  A) Replication must be configured between the servers and replicate the data from the Oracle to the SQL server
  B) A job must be created that performs the restore function and back up the Oracle server logged changes and restore the database changes from the Oracle to the SQL server every hour
  C)  A SQL Server Integration Services (SSIS) should be created to import the data from the Oracle server and schedule the package to be delivered every hour
  D)  The Oracle server should be configured as a linked server and join the information in the databases using a view
  Answer: D)  The Oracle server should be configured as a linked server and join the information in the databases using a view

  Question:  45
  UCF:  SQL 2.2
  Topic: REPLICATION_13
Your company has regional sales offices in Hyderabad, Noida and Chennai. You need to implement the replication scenario which will support autonomous changes the replicated data on the Publisher and on the Subscriber with the priority-based conflict resolution. Which type of SQL Server replication should you implement?
  A) Transaction replication
  B) Merge replication
  C)  Transaction replication with immediate-updating subscribers
  D) Snapshot replication
  Answer: B) Merge replication

  Question:  46
  UCF:  SQL 2.2
  Topic: REPLICATION_13
Your company has two SQL Server 2005 computers named SQL1 and SQL2. Both servers take part in replication. SQL1 is both the Publisher and its own Distributor of a publication named Pub1. Pub1 is the only publication on SQL1, and SQL2 is the only Subscriber. Your supervisor requests a status report about the replication latencies. Using Replication Monitor on SQL1, you need to find out the current latencies between the Publisher and Distributor as well as between the Distributor and Subscriber. What should you do?
  A) Select the Subscription Watch List tab for SQL1. View the Latency column for the SQL2 subscription.
  B) Select the All Subscriptions tab for the Pub1 publication. View the Latency column for the SQL2
  C)   Select the Tracer Tokens tab for the Pub1 publication. Select the Insert Tracer option and wait for the results to get the latency information.
  D) Select the Subscription Watch List tab for SQL1. Double-click the SQL2 subscription. View the duration
  Answer: C)   Select the Tracer Tokens tab for the Pub1 publication. Select the Insert Tracer option and wait for the results to get the latency information.

  Question:  47
  UCF:  SQL 2.2
  Topic: REPLICATION_13
You setup Queue Reader Agent by exection __ on the distribution database
  sp_qreader_agent_start
  sp_addqreader_agent
  sp_qreaderadd_agent
  xp_qreaderadd_agent
  Answer: sp_addqreader_agent

  Question:  48
  UCF:  SQL 2.2
  Topic: REPLICATION_13
Which flavour or TRANSACTIONAL Replication can be used in a ring topology with all the participants doing updates on different sets of data
  Peer to Peer
  Immediate Updates
  Queued Updates
  Cannot be implemented
  Answer: Peer to Peer

  Question:  49
  UCF:  SQL 2.2
  Topic: REPLICATION_13
With _, you can use the HTTPS protocol to replicate data to mobile users over the Internet or to remote databases across the corporate firewall.
  Peer to Peer Replication
  Snapshot Replication
  Transactional Replication
  Web Synchronization
  Answer: Web Synchronization

  Question:  50
  UCF:  SQL 2.2
  Topic: REPLICATION_13
SQL Server 2005 introduces a new program called __ which is used to provide information about SQL Server instances installed on the system
  Alias
  MDAC
  SQL Server Browser
  Service Broker
  Answer: SQL Server Browser
 
 


SQL 3.1

  Question:  1
  UCF:  SQL 3.1
  Topic: INST_UPG_10
SQL Server Browser does not contribute to which of the action below?
  A) Browsing a list of available servers
  B) Connecting to the correct server instance
  C)  Connecting to dedicated administrator connection (DAC)   endpoints
  D) None of the above
  Answer: D) None of the above

  Question:  2
  UCF:  SQL 3.1
  Topic: INST_UPG_10
Which of the following should be configured before installing SQL Server on a cluster?
  A) MS DTC
  B) MS TDC
  C)  MS TSC
  D) MS RTC
  Answer: A) MS DTC

  Question:  3
  UCF:  SQL 3.1
  Topic: INST_UPG_10
In SQL Server 2005, how do you perform a backup using Named Pipes?
  A) Normal backups to local disks all use named pipes
  B) In the TO part of the backup command, specify the pipe as “TO PIPE = “\sql\pipe”
  C)  SQL Server 2005 does not support backup to named pipes
  D) In the OPTIONS part of backup command, specify the pipe as OPTIONS PIPE
  Answer: C)  SQL Server 2005 does not support backup to named pipes

  Question:  4
  UCF:  SQL 3.1
  Topic: INST_UPG_10
You have created a new assembly and want to test it on SQL Server 2005. You install the developer edition with defaults on your workstation and create the assembly as a sysadmin. You then write a function to use this assembly, but it does not work. What could be the problem?
  A) .NET assemblies cannot be run on developer edition. You need to install Standard or Enterprise.
  B) The CLR environment needs to be enabled.
  C)  You do not have the correct permissions to run a .NET assembly.
  D) The assembly was not created with an explicit permission set assigned. You need to drop it and recreate it with SAFE, UNSAFE, or EXTERNAL_ACCESS explicitly set
  Answer: B) The CLR environment needs to be enabled.

  Question:  5
  UCF:  SQL 3.1
  Topic: INST_UPG_10
Which of the following component gets installed with SQL-Server 2000
  A) Internet Explorer
  B) IIS
  C)  MDAC
  D) SQL Lite Speed
  Answer: C)  MDAC

  Question:  6
  UCF:  SQL 3.1
  Topic: INST_UPG_10
You are installing a new SQL Server 2005 instance on a machine in a small peer-to-peer network. You will not be performing replication, so SQL Server will not need to communicate with other servers over the network. You need to be able to change the service accounts password every six months per company policy. Which service account type should you use?
  A) Built-in system account
  B) Local system account
  C)  Domain account
  D) DAC account
  Answer: B) Local system account

  Question:  7
  UCF:  SQL 3.1
  Topic: INST_UPG_10
Your installation of SQL Server 2005 has failed. Where is the first place you should look to find clues about the cause?
  A) The System log in the Event Viewer
  B) The Summary.txt file
  C)  The SQLSetupxxxx_ComputerName_Core.log file
  D) The Application log in the Event Viewer
  Answer: D) The Application log in the Event Viewer

  Question:  8
  UCF:  SQL 3.1
  Topic: INST_UPG_10
which version of .NET framework does SQL server 2005 need
  A) .Net framework 2.1
  B)  .Net framework 2.0
  C)  .Net framework 1.1
  D) .Net framework 1.0
  Answer: B)  .Net framework 2.0

  Question:  9
  UCF:  SQL 3.1
  Topic: INST_UPG_10
Which optional SQL Server component must you install to ensure that you can create and execute maintenance plans?
  A) SQL Writer
  B) SQL Server Browser
  C)  SQL Server Integration Services
  D) SQL Server Maintenance Pack
  Answer: C)  SQL Server Integration Services

  Question:  10
  UCF:  SQL 3.1
  Topic: INST_UPG_10
What is the recommended way to send mail from SQLAgent in SQL Server 2005?
  A) Database Mail
  B) SQL Server Agent Mail
  C)  SQLMail
  D) Extended MAPI
  Answer: A) Database Mail

  Question:  11
  UCF:  SQL 3.1
  Topic: MON_TR_15
In SQL Server2005, The purpose of making This a Shared Data Source checkbox whilh creating a report through Report Builder is
  to make it available to the future reporting apps
  o share the data across networkt
  Na
  Na
  Answer: to make it available to the future reporting apps

  Question:  12
  UCF:  SQL 3.1
  Topic: MON_TR_15
While modifying a report, what is not possible?
  You can change the available data or the sort order for the report by modifying the query on the Data tab.
  You can resize or rearrange controls on the Layout tab.
  You can use the Properties window to change properties of individual controls including their font, alignment, colors, and so on.
  You can include more tables ot join columns
  Answer: You can include more tables ot join columns

  Question:  13
  UCF:  SQL 3.1
  Topic: MON_TR_15
You are the local administrator of a windows server which hosts a SQL Server Databse Engine and SSRS. You created a report in the Report Designer and now you want to publish it to the Reporting server. You recive an error that zour login has insufficient privileges. In which security role your login need to be added in SSRS?
  Add your account to the sysadmin fixedserver role of the SQL Server
  Add the Windows user ASPNET to the Administrator group of the local server.
  Grant your login db_owner access to ReportServerTempdb database
  NA
  Answer: Add the Windows user ASPNET to the Administrator group of the local server.

  Question:  14
  UCF:  SQL 3.1
  Topic: MON_TR_15
To publish a report,In Configuration Manager, Active Soultion Configuration must be selected to ————
  Debug
  Debuglocal
  Production
  Any of theabove
  Answer: Production

  Question:  15
  UCF:  SQL 3.1
  Topic: MON_TR_15
While publishing a report, ————– should be selected from the configuration Manager
  Build only
  Deploy only
  Both Build and Deploy
  No need to select explicitily. It will automatically pick up
  Answer: Both Build and Deploy

  Question:  16
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which of the below Reporting tools is intended for Business Analysts and non-developers
  Report Builder
  Reort Manager
  Report Designer
  None of the above
  Answer: Report Builder

  Question:  17
  UCF:  SQL 3.1
  Topic: MON_TR_15
What does a Data model contai? (Choose all that apply)
  Data Sources connect the data model to actual data.
  Data Source Views draw data from data sources.
  Report Models contain entities that end users can use on reports.
  All of the above
  Answer: All of the above

  Question:  18
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which is not a rule in the Data Model generation?
  Create entities for all the tables
  Create indexed views
  Create numeric aggregates
  Create roles
  Answer: Create indexed views

  Question:  19
  UCF:  SQL 3.1
  Topic: MON_TR_15
In addition to viewing data, what are the other tasks performed bz Report Manager
  Enables view historical snapshots
  Enables Subscription of reports to the end users
  Only A)  
  Both a and b
  Answer: Both a and b

  Question:  20
  UCF:  SQL 3.1
  Topic: MON_TR_15
Installing SSRS creates two new virtual directories under the default website on IIS. They are
  Reports and ReportServer
  ReportTemp and ReportServer
  Report and ReportserverTemp
  None of the above
  Answer: Reports and ReportServer

  Question:  21
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which is true about Reports site
  hosts a web service for running and managing reports
  allows you upload reports from a browser and run reports over the web.
  Both A)  and B)  
  None of the above
  Answer: allows you upload reports from a browser and run reports over the web.

  Question:  22
  UCF:  SQL 3.1
  Topic: MON_TR_15
It is possible to add new parameters, maz or maz not be in the query in the Lazout of Report Designer
  Yes
  No
  Cant Say
  na
  Answer: Yes

  Question:  23
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which is not a SSIS object
  Event handler
  Data and Control flows
  Tasks
  Connections
  Answer: Tasks

  Question:  24
  UCF:  SQL 3.1
  Topic: MON_TR_15
What is true about connection manager in SSIS?
  Controls the data flow
  Integrate different data sources into packages.
  Handles event
  None of the above
  Answer: Integrate different data sources into packages.

  Question:  25
  UCF:  SQL 3.1
  Topic: MON_TR_15
Which is not a SSIS maintenance plan task_
  Notify operator
  History Cleanup
  Execute T-SQL statement
  Shrink File
  Answer: Shrink File

  Question:  26
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Eliminate Columns not dependent on a key is
  1NF
  3NF
  2NF
  BCNF
  Answer: 3NF

  Question:  27
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Eliminate Redundant Data is
  1NF
  3NF
  2NF
  4NF
  Answer: 2NF

  Question:  28
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Large datawarehouses should have more indexes
  True
  False
  maybe
  maybe not
  Answer: True

  Question:  29
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
A primary key attribute due to itsrelationwith another key attribute creates multiple tuples withinan entity cappled
  multipled dependency
  combined dependency
  Multivlued Dependency
  associated dependency
  Answer: Multivlued Dependency

  Question:  30
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
SQL Server Lock information can be obtained using
  sys.dm_os_sys_info
  sys.dm_os_performance_counters
  both
  neither
  Answer: sys.dm_os_performance_counters

  Question:  31
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
/3GB switch improves performance in a 64 bit server
  True
  False
  maybe
  maybe not
  Answer: False

  Question:  32
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Size of Pagefile is inversely proportional to the RAM
  True
  False
  maybe
  maybe not
  Answer: True

  Question:  33
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Buffer Cache hit ratio should always be above
  0.3
  0.4
  0.5
  0.9
  Answer: 0.9

  Question:  34
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
AWE option should be set explicitly on 64 bit
  False
  True
  maybe
  maybe not
  Answer: False

  Question:  35
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Windows 2003 server Datacenter edition supports
  32 GB
  64 GB
  128GB
  256 GB
  Answer: 64 GB

  Question:  36
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
Windows 2003 server SP1 Datacenter edition supports
  16 GB
  32 GB
  64 GB
  128 GB
  Answer: 128 GB

  Question:  37
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
A constant decrease in available memory is indicative of
  i/o bottleneck
  Memory Leak
  wrong max memory setting
  wrong cpu affinity setting
  Answer: Memory Leak

  Question:  38
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
How would you supress direct trigger recursion
  Set RECURSIVE_TRIGGERS database option to OFF
  set RECURSIVE_TRIGGER server option to 0
  set NESTED TRIGGER server option to 0
  Set NESTED_TRIGGERS database option to OFF
  Answer: Set RECURSIVE_TRIGGERS database option to OFF

  Question:  39
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
On performance standpoint what is the best way to store rows of data temporarily during an execution of SP?
  #tables
  Table variables
  Permanent Tables
  None of the above
  Answer: Table variables

  Question:  40
  UCF:  SQL 3.1
  Topic: PERF_TUN_15
How would you improve the performance of a cursor
  try to define READONLY Cursor
  Try to limit the number of rows defined in the cursor
  Select only the required columns in the Cursor
  All of the above
  Answer: All of the above

  Question:  41
  UCF:  SQL 3.1
  Topic: STORAGE_10
All successful SQL Server backup entries can be prevented from writing to the SQL Server Error Log by a single trace flag
  3226
  3116
  3410
  none
  Answer: 3226

  Question:  42
  UCF:  SQL 3.1
  Topic: STORAGE_10
How do you backup Analysis Services databases?
  Backup Database command
  Backup service command
  Create the XML statement to backup the Analysis Services databases
  none
  Answer: Create the XML statement to backup the Analysis Services databases

  Question:  43
  UCF:  SQL 3.1
  Topic: STORAGE_10
Which RAID level is known as Disk Striping?
  A) RAID 0
  B) RAID 1
  C)  RAID 2
  D) RAID 10
  Answer: A) RAID 0

  Question:  44
  UCF:  SQL 3.1
  Topic: STORAGE_10
Which RAID level provides striping with parity?
  A) RAID 0
  B) RAID 1
  C)  RAID 5
  D) RAID 10
  Answer: C)  RAID 5

  Question:  45
  UCF:  SQL 3.1
  Topic: STORAGE_10
Combine all separate disks into a single storage volume called as.
  A) striping
  B) mirroring
  C)  intended
  none
  Answer: A) striping

  Question:  46
  UCF:  SQL 3.1
  Topic: STORAGE_10
Which leve RAID level has stripes data at byte level across multiple disks
  A) RAID 0
  B) RAID 1
  C)  RAID 2
  D) RAID 3
  Answer: D) RAID 3

  Question:  47
  UCF:  SQL 3.1
  Topic: STORAGE_10
Which leve RAID level has stripes data at the block level across multiple disks
  A) RAID 2
  B) RAID 3
  C)  RAID 4
  D) RAID 5
  Answer: C)  RAID 4

  Question:  48
  UCF:  SQL 3.1
  Topic: STORAGE_10
Fault tolerance supported in
  RAID 0
  RAID 1
  RAID 2
  RAID 5
  Answer: RAID 5

  Question:  49
  UCF:  SQL 3.1
  Topic: STORAGE_10
parity and Hot spare option supported in
  RAID 0
  RAID 1
  RAID 2
  RAID 5
  Answer: RAID 5

  Question:  50
  UCF:  SQL 3.1
  Topic: STORAGE_10
Because of parity, information all data are available in case one of the disks fails in
  RAID 0
  RAID 1
  RAID 2
  RAID 5
  Answer: RAID 5
 
 


SQL 3.2

  Question:  1
  UCF:  SQL 3.2
  Topic: CONCEPT_12
The _ system DMV can be used to check the source of memory pressure (external/internal)
  sys.dm_os_memory_cache_clock_hands
  sys.dm_os_latch_stats
  sys.dm_os_loaded_modules
  sys.dm_os_memory_cache_counters
  Answer: sys.dm_os_memory_cache_clock_hands

  Question:  2
  UCF:  SQL 3.2
  Topic: CONCEPT_12
A perfectly configured SQL Server starts experiencing OOM Errors. This could be due to
  SQL Server Buffer Pool is unable to commit the Virtual Address Space reserved
  Memory leak in the System
  Both of the above
  None of the above
  Answer: Both of the above

  Question:  3
  UCF:  SQL 3.2
  Topic: CONCEPT_12
In order to use locked pages on 64 bit systems, it is __ to turn ON AWE.
  required
  not required
  NA
  NA
  Answer: not required

  Question:  4
  UCF:  SQL 3.2
  Topic: CONCEPT_12
A SQL Server installation is found to be using only 4 GB of memory inspite of configuring the Max Server memory to 8GB. What could be the reason?
  SQL Server cannot ustilze more than 4 GB
  Maximum amount of allowed memory is only 2 GB
  The AWE option needs to be enabled on 32 bit systems
  None of the above
  Answer: The AWE option needs to be enabled on 32 bit systems

  Question:  5
  UCF:  SQL 3.2
  Topic: CONCEPT_12
A memory node component in SQL Server consists of
  page allocators
  virtual allocator leveraging Windows VirtualAlloc APIs
  shared memory allocator
  All of the above
  Answer: All of the above

  Question:  6
  UCF:  SQL 3.2
  Topic: CONCEPT_12
SQL OS’s memory manager consists of several components such as memory objects and _
  memory nodes
  memory caches
  memory clerks
  All of the above
  Answer: All of the above

  Question:  7
  UCF:  SQL 3.2
  Topic: CONCEPT_12
The following output is from the DBCC MEMORYSTATUS Command Buffer Distribution Buffers —————————— ———– Stolen 241 Free 95 Procedures 89 Inram 0 Dirty 16 Kept 0 I/O 0 Latched 18 Other 880 (9 row(s) affecteD)  Pages for pinned tables that cannot be removed from memory are indicated by
  Dirty
  Inram
  Kept
  Other
  Answer: Inram

  Question:  8
  UCF:  SQL 3.2
  Topic: CONCEPT_12
The output of the DBCC MEMORYSTATUS command contains information on relative distribution of stolen buffers among major component groups in the server in addition to
  distribution of 8 KB buffers in the buffer pool
  overall size and the makeup of the buffer pool
  makeup of the procedure cache
  All of the above
  Answer: All of the above

  Question:  9
  UCF:  SQL 3.2
  Topic: CONCEPT_12
The __ DMV can be used to find out how much memory SQL Server has allocated through AWE mechanism
  sys.dm_os_awe_memory_used
  sys.dm_os_workers
  sys.dm_os_memory_clerks
  None of the above
  Answer: sys.dm_os_memory_clerks

  Question:  10
  UCF:  SQL 3.2
  Topic: CONCEPT_12
The following SQL Component cannot utilize the AWE mapped memory
  SQL Server Analysis Services
  SQL Server Reporting Services
  SQL Server Integration Services
  SQL Server Database Engine
  Answer: SQL Server Analysis Services

  Question:  11
  UCF:  SQL 3.2
  Topic: CONCEPT_12
On systems having more than 16 GB of RAM, the amount of user mode address space available to applications like SQL Server is
  1 GB
  2 GB
  4 GB
  As configured by Max Server Memory
  Answer: 2 GB

  Question:  12
  UCF:  SQL 3.2
  Topic: CONCEPT_12
With MARS enabled, the amount of memory utilized by a User Connection is
  Approximately (3 * network_packet_size + 94 KB)  
  Approximately (2 * network_packet_size + 94 KB)  
  approximately (3 + 3 * num_logical_connections) * network_packet_size + 94 KB
  approximately (3 + 2 * num_logical_connections) * network_packet_size + 94 KB
  Answer: approximately (3 + 3 * num_logical_connections) * network_packet_size + 94 KB

  Question:  13
  UCF:  SQL 3.2
  Topic: MIRROR_10
In the following statements which is the right command to Specify the partner from the principal server
  Modify DATABASE [DB name] SET PARTNER = N’TCP://B.corp.mycompany.com:5022′
  ALTER DATABASE [DB name] SET PARTNER = N’TCP://B.corp.mycompany.com:5022′
  Set DATABASE [DB name] SET PARTNER = N’TCP://B.corp.mycompany.com:5022′
  All of the above
  Answer: ALTER DATABASE [DB name] SET PARTNER = N’TCP://B.corp.mycompany.com:5022′

  Question:  14
  UCF:  SQL 3.2
  Topic: MIRROR_10
cannot serve the database – When a principal server does not allow any user connections to the database and any transactions to be processed
  True
  False
  NA
  NA
  Answer: True

  Question:  15
  UCF:  SQL 3.2
  Topic: MIRROR_10
Database mirroring does not use the partner servers as resource managers in a distributed transaction
  True
  False
  NA
  NA
  Answer: True

  Question:  16
  UCF:  SQL 3.2
  Topic: MIRROR_10
Can I have more than one mirror in SQL 2005 ?
  Yes
  No In SQL Server 2005, each principal database can have only one mirrored database.
  Partial
  Unknown
  Answer: No In SQL Server 2005, each principal database can have only one mirrored database.

  Question:  17
  UCF:  SQL 3.2
  Topic: MIRROR_10
Can I use Database Mirroring and Clustering?
  Unknown
  No
  Yes
  Manager needs to approve
  Answer: Yes

  Question:  18
  UCF:  SQL 3.2
  Topic: MIRROR_10
Database Mirroring Went out of Sync due to existing Endpints.due to the error message : the endpoint is not started? how do you start the End points?
  ALTER ENDPOINT [Mirroring] STATE = Enabled
  ALTER ENDPOINT [Mirroring] STATE = STARTED
  Create ENDPOINT [Mirroring] STATE = STARTED
  NA
  Answer: ALTER ENDPOINT [Mirroring] STATE = STARTED

  Question:  19
  UCF:  SQL 3.2
  Topic: MIRROR_10
Mirroring failed due to endpoints, how, you ensure thatport currently associated with database mirroring endpoint of a server instance
  Using update statement against sys.tcp_endpoints table
  Using SELECT statement against sys.tcp_endpoints table
  Using recreate statement against sys.tcp_endpoints table
  None of the above
  Answer: Using SELECT statement against sys.tcp_endpoints table

  Question:  20
  UCF:  SQL 3.2
  Topic: MIRROR_10
To verify the results of the failover on the new mirror, the following query will give you the current value of mirroring_role_desc is now Mirror SELECT name, mirroring_role_desc GO FROM sys.databases WHERE name = N’AdventureWorks’;
  False
  True
  NA
  NA
  Answer: True

  Question:  21
  UCF:  SQL 3.2
  Topic: MIRROR_10
To learn the role and state of the database mirroring endpoint of a server instance, on that instance, use the following Transact-SQL statement: SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
  True
  False
  Na
  Na
  Answer: True

  Question:  22
  UCF:  SQL 3.2
  Topic: MIRROR_10
choose the following statement is true or false? A SET PARTNER or SET WITNESS command can complete successfully when entered, but fail later
  False
  True
  Na
  NA
  Answer: True

  Question:  23
  UCF:  SQL 3.2
  Topic: REPORT_13
To enable the incoming request which port is opened in Microsoft Windows Firewall.
  Port 1443
  Port 80
  Port 443
  Port 1433
  Answer: Port 80

  Question:  24
  UCF:  SQL 3.2
  Topic: REPORT_13
Which command is used to find the status of the Remote administration
  netsh.exe
  RemoteStat.exe
  inetinfo.exe
  Adminstat.exe
  Answer: netsh.exe

  Question:  25
  UCF:  SQL 3.2
  Topic: REPORT_13
On the instance of SQL Server that is hosting the report server database.SQL Server Agent must be running before users define subscriptions and other scheduled operations.What is used to enable the SQL Server Agent.
  SQL Server Surface Area Configuration tool
  SQL Server Service configuration tool
  Report Builder
  Report Designer tool
  Answer: SQL Server Surface Area Configuration tool

  Question:  26
  UCF:  SQL 3.2
  Topic: REPORT_13
If you are performing an upgrade from an earlier version of SQL Server Reporting Services to SQL Server 2005 Reporting Services, what value you will provide to SKUUPGRADE argument if the following conditions are true: a.You use an instance of SQL Server Express Edition to host the report server database. b.You are upgrading from the previously installed edition of Reporting Services and from the previously installed instance of SQL Server Express Edition.
  0
  1
  2
  3
  Answer: 1

  Question:  27
  UCF:  SQL 3.2
  Topic: REPORT_13
Reporting server makes a request to whom to initially obtain report metadata.
  report catalog
  Reporting Data
  Both
  None
  Answer: Both

  Question:  28
  UCF:  SQL 3.2
  Topic: REPORT_13
If the report server database is on a remote computer and you do not have permission to update that database, Setup prompts you to provide credentials to update to a remote report server database. what permissions we should provide.
  SecurityAdmin
  Setupadmin
  Serveradmin
  sysadmin
  Answer: sysadmin

  Question:  29
  UCF:  SQL 3.2
  Topic: REPORT_13
If IIS is not running, you may receive which error during the SSRS setup.
  29556
  1603
  1306
  29956
  Answer: 1603

  Question:  30
  UCF:  SQL 3.2
  Topic: REPORT_13
Which file Stores the code access security policies for the server extensions.
  RSSrvpolicy.config
  RSMgrPolicy.config
  RSPreviewPolicy.config
  RSReportServer.config
  Answer: RSSrvpolicy.config

  Question:  31
  UCF:  SQL 3.2
  Topic: REPORT_13
which file is located at \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies folder
  RSReportServerServices.exe.config
  RSReportDesigner.config
  RSReportServer.config
  RSPrivateAssemblies.config
  Answer: RSReportDesigner.config

  Question:  32
  UCF:  SQL 3.2
  Topic: REPORT_13
While creating a new report, you are prompted to create a data source definition. To connect to data source you required three information.Data source type, connect information and _
  Port number
  Permission
  Version
  Database name
  Answer: Permission

  Question:  33
  UCF:  SQL 3.2
  Topic: REPORT_13
Which table in reportserver database that stores the execution results of each report
  ExecutionLog
  ExecutionResult
  ExecutionTrace
  Executionstat
  Answer: ExecutionLog

  Question:  34
  UCF:  SQL 3.2
  Topic: REPORT_13
Where does the .RDL stored
  ReportServer Database
  Report Folder
  Windows Directory
  All
  Answer: ReportServer Database

  Question:  35
  UCF:  SQL 3.2
  Topic: REPORT_13
Which report is used in Visual Studio for client side reporting
  RDL
  RDLC
  Both
  None
  Answer: RDLC

  Question:  36
  UCF:  SQL 3.2
  Topic: TU_15
What does the following command indicates when you are restoring your database using sql server Litespeed tool. exec master.dbo.xp_restore_database @database = ‘dbname’, @filename = ‘path to backup file’ , @file number =2
  Restoring single backup from a Backup set (Multiple backups written to same file)
  Restoring backup but only the second file
  Restoring backup but only the second filegroup
  Restoring backup but only the log file
  Answer: Restoring single backup from a Backup set (Multiple backups written to same file)

  Question:  37
  UCF:  SQL 3.2
  Topic: TU_15
when you try to restore a database you got an error something like this: Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure ‘master.dbo.xp_restore_database’. What does this indicate and how to correct this issue?
  This indicates that there is a problem with the backup file, verify your backup and if corrupted get a new backup and restore the db
  This indicates that you lost some of your system stored procedures, you need to restore Resource database with a backup you have you get all the updated system objects
  This indicates that litespeed is not installed and hence you cannot use this backup command, instead use sql server native backup command, backup database to restore your database
  This could be that you are executing this command in master database instead of MSDB, execute the same statement in Msdb database and you would be able to restore you database
  Answer: This indicates that litespeed is not installed and hence you cannot use this backup command, instead use sql server native backup command, backup database to restore your database

  Question:  38
  UCF:  SQL 3.2
  Topic: TU_15
how do you take a databases backup other then SQL native Backup?
  Lite Speed
  Veritas
  Tivoli
  All of the above
  Answer: All of the above

  Question:  39
  UCF:  SQL 3.2
  Topic: TU_15
LiteSpeed improves the performance of log shipping by reducing the size of the logs, thereby increasing the speed of the log transfer
  True
  False
  NA
  NA
  Answer: True

  Question:  40
  UCF:  SQL 3.2
  Topic: TU_15
LiteSpeed allows you to restore only the individual objects (i.e., tables, stored procedures and views) you need, preventing disruption while you access the database.
  True
  False
  NA
  NA
  Answer: True

  Question:  41
  UCF:  SQL 3.2
  Topic: TU_15
The technology of LiteSpeed is specifically designed to identify additional compression points within the database
  False
  True
  NA
  NA
  Answer: True

  Question:  42
  UCF:  SQL 3.2
  Topic: TU_15
LiteSpeed allows you to write a single backup to multiple locations simultaneously, eliminating the need to make copies individually.
  False
  True
  NA
  NA
  Answer: True

  Question:  43
  UCF:  SQL 3.2
  Topic: TU_15
LiteSpeed allows you to restore data down to the row level by incorporating SELECT statements into the recovery process, returning only the data you need from your backup file.
  True
  False
  NA
  NA
  Answer: True

  Question:  44
  UCF:  SQL 3.2
  Topic: TU_15
The Backup Analyzer lets you test different backup parameters such as compression level, ncryption, backup locations and tuning capabilities(SQL Server 5.0.2 includes)
  False
  True
  NA
  NA
  Answer: True

  Question:  45
  UCF:  SQL 3.2
  Topic: TU_15
LiteSpeed Maintenance Plans provide the ability to create maintenance plans with all the options of the native tool as well LiteSpeed’s power, regardless of plan complexity(SQL Server 5.0.2 includes)
  True
  False
  NA
  NA
  Answer: True

  Question:  46
  UCF:  SQL 3.2
  Topic: TU_15
Log Reader makes online transaction logs and online backup logs (native and LiteSpeeD)  visible. It also features the power to redo, undo, search and view transaction details(SQL Server 5.0.2 includes)
  False
  True
  NA
  NA
  Answer: True

  Question:  47
  UCF:  SQL 3.2
  Topic: TU_15
WE can not manage all backup and recovery operations from a central user interface.
  True
  False
  NA
  NA
  Answer: False

  Question:  48
  UCF:  SQL 3.2
  Topic: TU_15
LiteSpeed must be installed under a Local Administrator account with SA rights on SQL Server to allow installation of the registry keys, files and extended stored procedures necessary
  True
  False
  NA
  NA
  Answer: True

  Question:  49
  UCF:  SQL 3.2
  Topic: TU_15
user account that will run backups/restores may be a Power User account.
  False
  True
  NA
  NA
  Answer: True

  Question:  50
  UCF:  SQL 3.2
  Topic: TU_15
while using lightspeed backup you scheduled a backup job. Later some time you noticed that the backup file is not compressing expected. Where do you verify compress level?
  At SQL job Level
  At lightspeed parameters
  At SQL default values
  None of the above
  Answer: At lightspeed parameters
 
 


SQL 4.1

  Question:  1
  UCF:  SQL 4.1
  Topic: MT_15
The public and private networks that we setup in cluster should have
  dynamic IP addresses
  static IP addresses
  either static or dynamic IP addresses
  virtual IP addresses
  Answer: static IP addresses

  Question:  2
  UCF:  SQL 4.1
  Topic: MT_15
which of the following is not true in case of Qurom disks in sql server failover clusters
  quorum disk contains a master copy of the server cluster configuration
  used as a tie-breaker if all network communication fails between cluster nodes
  the quorum resource share the same disk with other resources
  quorum disk should be a physical disk on the shared cluster disk array
  Answer: quorum disk should be a physical disk on the shared cluster disk array

  Question:  3
  UCF:  SQL 4.1
  Topic: MT_15
In a Clustering environment, this signal, is very lightweight and does not perform anything more than a simple check to see if the SQL Server 2005 instance is up and running, not if the instance can execute any operations, what is this signal called
  IsAlive
  IsWorking
  LooksAlive
  heartbeat
  Answer: LooksAlive

  Question:  4
  UCF:  SQL 4.1
  Topic: MT_15
This is a private network set up between the nodes of the cluster that checks to see whether a server is up and running. This occurs at regular intervals known as time slices. If this is not functioning, a failover is initiated, and another node in the cluster will take over the services, what is this called?
  IsAlive
  LooksAlive
  Heartbeat
  IsWorking
  Answer: Heartbeat

  Question:  5
  UCF:  SQL 4.1
  Topic: MT_15
This type of disk array is a setup in which only one node can own a given resource at any given moment. All other nodes are denied access until they own the resource. This protects the data from being overwritten data when two computers have access to the same drives concurrently, what is this disk array called?
  Shared Nothing
  Shared one
  Shared array
  FibreChannel
  Answer: Shared Nothing

  Question:  6
  UCF:  SQL 4.1
  Topic: MT_15
Does SQL Server 2000 clustering support load balancing?
  SQL Server 2000 clustering does not provide load balancing; it provides failover support. To achieve load balancing, you need software that balances the load between clusters, not between servers within a cluster
  SQL Server 2000 clustering does not support load balancing
  ALL the above
  NA
  Answer: ALL the above

  Question:  7
  UCF:  SQL 4.1
  Topic: MT_15
Does SQL Server 2000 Full-Text Search support clustering?
  Yes
  No
  Required additional Hot fix
  None
  Answer: Yes

  Question:  8
  UCF:  SQL 4.1
  Topic: MT_15
Is it necessary to configure MSDTC as a clustered resource for SQL Server clustering to work?
  No. MSDTC is required only if you need the ability to perform distributed transactions on your cluster
  We can configure for Data Load
  Yes
  None
  Answer: No. MSDTC is required only if you need the ability to perform distributed transactions on your cluster

  Question:  9
  UCF:  SQL 4.1
  Topic: MT_15
Can SQL Server 6.5 or 7.0 be installed on one node of a cluster and SQL Server 2000 be installed on the other node?
  We can install ony with SQL 200 and SQL 2005
  Possible only in Multinode Cluster
  Using third-party Software
  No. For two SQL Server servers to participate in a cluster, they must be the same version.
  Answer: No. For two SQL Server servers to participate in a cluster, they must be the same version.

  Question:  10
  UCF:  SQL 4.1
  Topic: MT_15
How do I shut down SQL Server from the command line without the Cluster Service interpreting the shutdown as a failure?
  It is not possible from Command Prompt
  Cluster /offline
  cluster [cluster name] RESOURCE [resource name] /option
  Cluster /SQL resourse Online
  Answer: cluster [cluster name] RESOURCE [resource name] /option

  Question:  11
  UCF:  SQL 4.1
  Topic: MT_15
Can I make the VMM2008 server highly available by configuring it as a clustered application on a failover cluster of physical servers?
  We can not make
  It is possible only when Single Node Cluster
  No. Instead, you can run VMM 2008 on a highly available virtual machine.
  None
  Answer: No. Instead, you can run VMM 2008 on a highly available virtual machine.

  Question:  12
  UCF:  SQL 4.1
  Topic: MT_15
Is the use of software fault-tolerant disk sets for cluster storage supported?
  No
  Yes
  None
  using SAN manager Utility
  Answer: No

  Question:  13
  UCF:  SQL 4.1
  Topic: MT_15
Where is the ” Cluster.Log” Location?
  C:|winnt
  C:\Program files\SQL Server
  C:\Cluster
  None
  Answer: C:|winnt

  Question:  14
  UCF:  SQL 4.1
  Topic: MT_15
How does SQL Cluster Setup process detect that it is going to operate in a cluster environment?
  It checks Cluster Service running
  it Checks the cluster DLL files
  it checks the MSCS installed
  None
  Answer: It checks Cluster Service running

  Question:  15
  UCF:  SQL 4.1
  Topic: MT_15
How many IP addres would require to build a Single Node Cluster?
  9
  8
  6
  4
  Answer: 6

  Question:  16
  UCF:  SQL 4.1
  Topic: PT_15
You are mastering the company database, Users complaints that the SQL Server 2008 application is running slowly. You should check the information of the CPU utilization, disk utilization, and memory utilization. The network should be considered. the detailed information should be checked for the proper example of SQL Server2008.Which is the correct answer.
  You should check the proper dynamic management views (DMVs).
  In the databases, you should check the distribution of the statistics which are hosted
  You should check a statistics update command’s the output
  You should check the recovery model of the master database
  Answer: You should check the proper dynamic management views (DMVs).

  Question:  17
  UCF:  SQL 4.1
  Topic: PT_15
You are managing a SQL Server 2008 instance which includes databases for a shopping application and a manufacturing application for the Company.com. You set the Resource Governor to cut the maximum CPU search time permitted through the applications. The administrator of the manufacturing application tells you that that some reports could not run successfully. You should make sure that you change the Resource Governor settings to permit the manufacturing application to cost more CPU time. Which is the correct answer?
  The workload group that clarifies the shopping application
  The classifier function that is used by default
  The workload group that is utilized by default
  The workload group which is utilized through the manufacturing application
  Answer: The workload group which is utilized through the manufacturing application

  Question:  18
  UCF:  SQL 4.1
  Topic: PT_15
You are managing a SQL Server 2008 instance for Company.com. There are databases in the server for some applications. You decide to utilize the Resource Governor to cut the result of searches which are run through Microsoft SQL Server Management Studio. The requirements listed below are met: By SQL Server Management Studio, Searches initiated do not excel 40 percent of CPU utilization.By the applications, Searches initiated are permitted to cost 200 percent of CPU utilization when needed. Which is the correct answer?
  You should develop a default resource pool and configure the MAX_CPU_PERCENT selection to 60.and give this resource pool to the workload group used.
  You should develop a default resource pool and configure the MAX_CPU_PERCENT selection to 40.and give this resource pool to the workload group used.
  You should develop a new resource pool and configure the MAX_CPU_PERCENT selection to 40.and give his resource pool to the workload group used through.
  You should develop a new resource pool and configure the MAX_CPU_PERCENT selection to 60.and give this resource pool to the workload group used through
  Answer: You should develop a new resource pool and configure the MAX_CPU_PERCENT selection to 40.and give his resource pool to the workload group used through.

  Question:  19
  UCF:  SQL 4.1
  Topic: PT_15
You are a database administrator for Company.com. You are responsible for monitoring the performance of your SQL Server 2005 computers. You have a SQL Server Integration Services (SSIS) package that usually takes 10 minutes to run. Today, the package is taking more than one hour to run. You suspect memory is too low. You need to identify the problem. What should you do?
  Redesign the SQL Server Integration Services (SSIS) package so that it runs only when no exclusive table locks are in use
  Check the time-out values in the SQL Server Integration Services (SSIS) package
  Use the System Monitor to monitor the SQL Server Integration Services (SSIS) pipeline counters
  Use SQL Server Profiler to monitor the Scans event category counters
  Answer: Use the System Monitor to monitor the SQL Server Integration Services (SSIS) pipeline counters

  Question:  20
  UCF:  SQL 4.1
  Topic: PT_15
You are a database administrator for Company.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?
  Implement the Read Uncommitted isolation level
  Execute the sp_locks stored procedure
  Increase the maximum number of worker threads.
  Configure the SQL Server max Server memory setting to be a lower value
  Answer: Implement the Read Uncommitted isolation level

  Question:  21
  UCF:  SQL 4.1
  Topic: PT_15
You work as DBA at Company.com. You administer a SQL Server 2005 computer named Company A. Users report that all client applications that connect to Company A are responding slowly. You discover that Company As CPU and memory utilization is low and that disk activity is low. You need to identify the cause of the performance problem. What should you do?
  Use System Monitor to examine Company As cache hit ratio
  Examine the sys.dm_db_index_physical_stats dynamic management function (DMF).
  Use Task Manager to examine utilization of Company As network adapter
  Examine the sys.dm_db_partition_stats dynamic management view (DMV).
  Answer: Use Task Manager to examine utilization of Company As network adapter

  Question:  22
  UCF:  SQL 4.1
  Topic: PT_15
You are a database administrator for Company.com. Users report that the performance of a SQL Server 2005 application seems slow. You verify that the SQL Server computers CPU utilization,disk utilization, and memory utilization are normal. You also verify that the network is operating normally. You need to examine more detailed performance information for the appropriate instance of SQL Server 2005. What should you do?
  Examine the distribution of the statistics in the databases that are hosted by the instance
  Examine the recovery model of the temp db database
  Examine the appropriate dynamic management views (DMVs).
  Examine the output of a statistics update command
  Answer: Examine the appropriate dynamic management views (DMVs).

  Question:  23
  UCF:  SQL 4.1
  Topic: PT_15
You are a database administrator for Company.com. Users report that a SQL Server 2005 application stops responding for several seconds at a time during the day. This problem occurs randomly throughout the day and affects many users at once. When the problem occurs, you notice that CPU utilization and disk tilization on the SQL Server 2005 computer are low, but that the network is operating normally. You need to examine additional information relevant to the problem.What should you do?
  Examine the sys.dm_tran_locks dynamic management view (DMV).
  Examine the total number of lock requests on the SQL Server 2005 computer
  Examine the output of the DBCC SHOWCONTIG command
  Examine the output of the DBCC SQLPERF(IOSTATS) command.
  Answer: Examine the sys.dm_tran_locks dynamic management view (DMV).

  Question:  24
  UCF:  SQL 4.1
  Topic: PT_15
You are a database developer for TestKing’s SQL Server 2000 database. The database is installed on a Microsoft Windows 2003 Server computer. The database is in the default configuration. All tables in the database have at least one index. SQL Server is the only application running on the server.The database activity peaks during the day, whensalesrepresentatives enter and update salestransactions. Batch reporting is performed after business hours. hesalesrepresentatives report slow updates and inserts.What should you do
  Run system monitor on the SQL Server:Access Methods counter during the day. Use the output from System Monitor to identify which tables need indexes
  Use the sp_configure system stored procedure to increase the number of locks that can be used by SQL
  Increase the value of the min server memory option
  Run SQL Profiler during the day. Select the SQL:BatchCompleted and RPC:Completed events and the EventClass and TextData data columns. Use the output from SQL profiler as input to the Index Tuning Wizard
  Answer: Run SQL Profiler during the day. Select the SQL:BatchCompleted and RPC:Completed events and the EventClass and TextData data columns. Use the output from SQL profiler as input to the Index Tuning Wizard

  Question:  25
  UCF:  SQL 4.1
  Topic: PT_15
You are the administrator of a SQL Server 2005 computer. You create a job that performs several maintenance tasks on the server’s databases. You want the job to run whenever the server’s processor utilization falls below 5 percent. You create a new schedule for the job and specify the Start whenever the CPU(s) become idle option. After several days, you notice that the job has never executed although the server’s processor utilization has fallen below 5 percent several times. What should you do
  Modify SQL Server Agent properties and specify a smaller idle time
  Modify SQL Server Agent properties and specify a larger idle time
  Write a stored procedure that executes the job whenever the @@IDLE system variable is less than 5
  Write a stored procedure that executes the job whenever the @@IDLE system variable is grater than 5
  Answer: Modify SQL Server Agent properties and specify a smaller idle time

  Question:  26
  UCF:  SQL 4.1
  Topic: PT_15
You are the administrator of a SQL Server 2000 computer that contains a database. Users report that the database responds slowly when they insert data. You need to modify the server to improve its performance. What should you do?
  Install a larger hard disk
  Increase the amount of RAM
  Increase the size of the virtual memory paging file
  Add an additional processor
  Answer: Increase the amount of RAM

  Question:  27
  UCF:  SQL 4.1
  Topic: PT_15
You are the administrator of a SQL Server 2000 computer. The server contains your company’s investment-tracking database. Each day, 100 operators make approximately 5,000 changes to customer investments. In addition, daily and monthly reports are created from the investment data.Developers at your company need to optimize the database application. They need a sample of database query activity to discover if they can speed up the transactions. The developers also want to replay the sample on another SQL Server computer You need to capture the sample, but you want to minimize any increase to the workload of the server. What should you do
  Run SQL Profiler on a client computer Configure SQL Profiler to monitor database activity, and log data to a .trc file
  Run SQL Profiler on the server Configure SQL Profiler to monitor database activity, and log data to a database table
  Run SQL Profiler on the server Configure SQL Profiler to monitor database activity, and log data to a text file
  Run SQL Profiler on the server Configure SQL Profiler to monitor database activity, and log data to a database table
  Answer: Run SQL Profiler on a client computer Configure SQL Profiler to monitor database activity, and log data to a .trc file

  Question:  28
  UCF:  SQL 4.1
  Topic: PT_15
Your company has a server named SQL1 that runs SQL Server 2005 Enterprise Edition. SQL1 has 2 GB of RAM, 1.6GB of which are used by the default SQL Server database engine instance. The average data growth of all databases combined is 100 MB a month.Users state that report execution times are increasing. You want to assess whether more RAM is needed.You need to use System Monitor to create a counter log that will help you decide whether to add RAM.Which performance object should you add to the counter log
  MSAS 2005:Memory
  MSAS 2005:Proactive Caching
  SQLServer:Buffer Manager
  SQLServer:SQL Statistics
  Answer: SQLServer:Buffer Manager

  Question:  29
  UCF:  SQL 4.1
  Topic: PT_15
You use an order application named App1, which connects to a SQL Server 2005 database named DB1. Because some actions take more than three seconds, users report slow performance when they use App1. You want to identify those actions by using the Tuning template in SQL Server Profiler to trace the application. You need to capture only those App1 actions in DB1 that have a response time of two seconds or more. What should you do
  Add the ApplicationName column and create the following column filters.ColumnOperatorValueDurationGreater than or equal2000ApplicationNameLikeApp1DatabaseNameLikeDB1
  Add the ApplicationName column and create the following column filters.ColumnOperatorValueDurationGreater than or equal to ApplicationName Like App1DatabaseName Like DB1
  Add the CPU column and create the following column filters.ColumnOperatorValueCPUGreater than or equal2000TextDataLikeApp1DatabaseNameLikeDB1
  Add the ApplicationName and CPU columns and create the following column filters.ColumnOperatorValueCPUGreater than or
  Answer: Add the ApplicationName column and create the following column filters.ColumnOperatorValueDurationGreater than or equal2000ApplicationNameLikeApp1DatabaseNameLikeDB1

  Question:  30
  UCF:  SQL 4.1
  Topic: PT_15
You are managing a SQL Server 2008 instance which includes databases for a shopping application and a manufacturing application for the Company.com. You set the Resource Governor to cut the maximum CPU search time permitted through the applications. The administrator of the manufacturing application tells you that that some reports could not run successfully. You should make sure that you change the Resource Governor settings to permit the manufacturing pplication to cost more CPU time. Which is the correct answer?
  The workload group that clarifies the shopping application
  The classifier function that is used by default
  The workload group that is utilized by default
  The workload group which is utilized through the manufacturing application
  Answer: The workload group which is utilized through the manufacturing application

  Question:  31
  UCF:  SQL 4.1
  Topic: SOL_5
Can we restore a SQL Server 2000 database backup to a SQL Server 7.0 server?
  yes, DBRestore is possible if the SQL Server DB have no Consistency errors
  No. The only way to move a SQL Server 2000 database to a SQL Server 7.0 server is by transferring the data using a method such as DTS, bcp, or use of a query between linked servers.
  yes ..Restore DB is possible if .ndf and .ldf files are present though .mdf files are missing
  Yes ..Database is in Full Recovery mode can be Restored
  Answer: No. The only way to move a SQL Server 2000 database to a SQL Server 7.0 server is by transferring the data using a method such as DTS, bcp, or use of a query between linked servers.

  Question:  32
  UCF:  SQL 4.1
  Topic: SOL_5
Running DBCC Commands with Allow Dataloss are best suited when run
  DBOnline
  DB Single User mode
  DB Restoring
  DB MultiUser mode
  Answer: DB Single User mode

  Question:  33
  UCF:  SQL 4.1
  Topic: SOL_5
MasterDB supports which type of Back-up
  Full DB Back-up
  Differential Back-up
  Transaction Log
  File and Filegroup Back-up
  Answer: Full DB Back-up

  Question:  34
  UCF:  SQL 4.1
  Topic: SOL_5
ONLINE RESTORE in SQL Server 2005
  allows to restore the DB Online and access the data being restored
  allows to restore the DB Online and only the data being restored is unavailable
  allows to access the filegroup data and shrink DB
  Allows slowness in DB Transactions
  Answer: allows to restore the DB Online and only the data being restored is unavailable

  Question:  35
  UCF:  SQL 4.1
  Topic: SOL_5
How can I grant to user the RESTORE DATABASE permission using TSQL ?
  sp_changesrvrolemember
  sp_addrolemember
  sp_addsrvrolemember
  sp_add_dbadmin
  Answer: sp_addsrvrolemember

  Question:  36
  UCF:  SQL 4.1
  Topic: SSIS_5
You can install DTS and SSIS on the same computer and run them?
  You can install but cant run
  cant install both
  Can install and run
  cant install both
  Answer: Can install and run

  Question:  37
  UCF:  SQL 4.1
  Topic: SSIS_5
If you are a previous or current user of Microsoft SQL Server 2000 Data Transformation Services (DTS) you have the following options when you install Microsoft SQL Server 2005 Integration Services (SSIS)?
  Installing Support for SQL Server 2000 Data Transformation Services Packages
  Upgrading an earlier version of Data Transformation Services to SSIS
  Migrating existing Data Transformation Services packages to SSIS
  All of the above
  Answer: All of the above

  Question:  38
  UCF:  SQL 4.1
  Topic: SSIS_5
SQL Server 2005 provides what for migrating SQL Server 2000 DTS packages to the Integration Services package format?
  DTS migration wizard
  Package Migration wizard
  ssis migration wizard
  None of the above
  Answer: Package Migration wizard

  Question:  39
  UCF:  SQL 4.1
  Topic: SSIS_5
IIS 1.0 or later is required for Microsoft SQL Server 2005 Reporting Services (SSRS) installations?
  5.0 or later
  2.0 or later
  3.0 or later
  4.0 or later
  Answer: 5.0 or later

  Question:  40
  UCF:  SQL 4.1
  Topic: SSIS_5
To ensure a complete installation of Integration Services, you must select what on the Components to Install page?
  Install all components
  Database Engine
  The Business Intelligence Development Studio
  Integration Services
  Answer: Integration Services

  Question:  41
  UCF:  SQL 4.1
  Topic: STO_10
Which of the following is not a best practice on TEMPDB
  keeping the recovery model to simple
  keeping the database owner to SA
  drop and recreate tempdb by restarting sql server service atleast once a day
  Do not change collation from the SQL Server instance collation
  Answer: drop and recreate tempdb by restarting sql server service atleast once a day

  Question:  42
  UCF:  SQL 4.1
  Topic: STO_10
What are version stores
  is a collection of log pages of tempdb
  is a collection of data pages that hold the data rows that are required to support the features that use row versioning
  is a collection of all data pages in tempdb
  is a collection of all data and log pages in tempdb
  Answer: is a collection of data pages that hold the data rows that are required to support the features that use row versioning

  Question:  43
  UCF:  SQL 4.1
  Topic: STO_10
Which DMV is used to monitor disk space used by tempdb files
  sys.dm_db_file_space_usage
  sys.dm_io_virtual_file_stats
  sys.dm_tran_version_store
  sys.dm_os_latch_stats
  Answer: sys.dm_db_file_space_usage

  Question:  44
  UCF:  SQL 4.1
  Topic: STO_10
what’s the difference in the results of dbcc sqlperf(logspace) and dbcc showfilestats
  both give information about data files and there is no difference
  both give information about data files, the first one gives in the form of MBs while the latter gives the results in Extents
  both give information about data files, the first one gives in Extents while the latter gives information in MBs
  The first one gives information about data files while the latter gives information about column statistics
  Answer: both give information about data files, the first one gives in the form of MBs while the latter gives the results in Extents

  Question:  45
  UCF:  SQL 4.1
  Topic: STO_10
which of the following is not one of the requirements that you would look at before doing capacity planning for your databases
  expected number of users
  usage profile of the system
  max CPU usage
  max memory usage
  Answer: max memory usage

  Question:  46
  UCF:  SQL 4.1
  Topic: STO_10
What should be the value of Priority Boost in case of sql server 2005 failover clusters
  0
  1
  2
  you should not use this setting
  Answer: you should not use this setting

  Question:  47
  UCF:  SQL 4.1
  Topic: STO_10
What is a LUN
  This is the identification number assigned to a volume when created on a SAN device
  are collections of storage devices and fibre switches connected together along with the servers that access the storage on the device
  another way to refer to the physical disk drives that make up the RAID array
  A general term that refers to the disks on the server
  Answer: This is the identification number assigned to a volume when created on a SAN device

  Question:  48
  UCF:  SQL 4.1
  Topic: STO_10
there is a performance impact while writing data to the array because a parity bit must be calculated for each write operation performed, which RAID level are we talking about here?
  RAID 0
  RAID 1
  RAID 5
  RAID 10
  Answer: RAID 5

  Question:  49
  UCF:  SQL 4.1
  Topic: STO_10
It is recommended that for larger systems, each file group of the database should
  have between .25 to one physical file per physical CPU core in the server
  have between .5 to one physical file per physical CPU core in the server
  have between .75 to one physical file per physical CPU core in the server
  have between 1 to 2 physical files per physical CPU core in the server
  Answer: have between .25 to one physical file per physical CPU core in the server

  Question:  50
  UCF:  SQL 4.1
  Topic: STO_10
What is the best RAID level suited for storing Transaction log files
  RAID 0
  RAID 1
  RAID 5
  RAID 0+1
  Answer: RAID 0+1
 
 



Leave a Reply

Your email address will not be published. Required fields are marked *