1) With a Windows for Workgroups Workstation on a Netware network with
integrated security and data encryption, what network library is used to
connect to SQL?
a) Multi Protocol (correct answer)
b) Named Pipes
c) Novell IPX/SPX
d) NWLink
2) You have a Netware workstation and you want one login, one password,
and data encryption. What network library should you install?
a) Named Pipes
b) Multi Protocol (correct answer)
c) NWLink/IPX/SPX
3) You have IPC and need integrated security. What network library
should you install?
a) Named Pipes
b) Multi Protocol (correct answer)
c) IPX/SPX
4) You want to communicate something over an IPC mechanism that supports
integrated security with Microsoft Windows NT. Which network protocol to
choose in setup?
a) NWLink/IPX/SPX
b) Named Pipes (correct answer)
c) Multi Protocol
d) TCP/IP
5) You install SQL Sever on a Microsoft Windows NT client with TCP/IP
protocol. You want to allow communication with SQL server. Does the
following solution work? From the SQL Client Configuration Utility,
select the Net library tab. In Default Network, select TCP/IP sockets.
Specify the name of the Windows NT Primary Domain Controller for the IP
address.
a) The above solution meets the requirements and is an outstanding
solution.
b) The above solution meets the requirements and is an adequate solution.
c) The above solution meets the requirements but is an inadequate
solution.
d) The above solution does not meet the requirements although it appears
to work.
e) The above solution does not meet the requirements and does not work.
(correct answer)
6) You change the name of the SQL Server "finance" to MONEYBAGS and
computer name from "X" to "Y." Remote stored procedures used to query
branch offices do not work anymore. How will you correct this?
a) drop and add using sp_dropserver and sp_addserver, and then,
sp_serveroption (correct answer)
b) Change the name of the publisher sever in Enterprise Manager (correct
answer)
c) rename the server name using sp_rename
7) What DBCC command is used to check a specified object to see that
index and data pages are correctly linked?
a) DBCC CHECKTABLE (correct answer)
b) DBCC CHECKCATALOG
8) What DBCC command checks for consistency in and between system tables
in a database for integrity?
a) DBCC CHECKCATALOG (correct answer)
9) Who has permission to use update statistics?
a) DBOO or owner of the object. (correct answer)
b) Anyone
10) You increase the number of locks. What effect does this have on
system resources?
a) Less memory available. (correct answer)
b) More memory available.
11) Trigger question:
a) Scenario:
i) You have a DBO that is also the DBOO of the Customer table. He
creates a trigger that deletes a row on the Sales table.
ii) You have Monica who is a user that is the DBOO of the Sales table.
iii) You have the Clerical Users Group that deletes the Cust_ID row in
the Customer table that calls the trigger into effect on the Sales table.
The trigger is supposed to delete the same data in the Cust_ID row on
the Sales table. The command is entered as follows: delete monica.table1
where sales.s_id=table1.s_id.
iv) The Clerical Users Group are given delete permission on the Customer
table.
b) What is the result?
i) The two rows will be deleted.
ii) One row will be deleted from the Customer table. (correct answer)
iii) Password will be asked for Monica's table.
iv) An error message will result.
12) You have a customer database and the log is getting filled up. You
want to know which users are logged on and what commands they are using.
You do not want to schedule a task to dump the log. You do want a script
that will automatically execute a user defined stored procedure, run DUMP
TRANSACTION WITH NO LOG, and run DUMP DATABASE when error number 1105
occurs. Error number 1105 indicates that you have run out of space in
the transaction log. What tool should you use?
a) Alert (correct answer)
b) Replication
c) Task Scheduler
d) SQL Performance Monitor
13) What can cause a database to be marked suspect during recovery?
a) Transaction log empty.
b) Server shutdown during an active transaction.
c) Error of severity 17 and 18 is ____.
d) The process that brings a database to a consistent transactional state
during startup fails. (correct answer)
14) After running SQL Performance Monitor for a few days you note that
page I/O reads per second are high. How do you reduce them? Choose
three.
a) Use a larger data cache. (correct answer)
b) Install a faster disk sub system.
c) Change the database design. (Put transaction log and database on
separate devices?) (correct answer)
d) Verify appropriate indexes have been created. (correct answer)
15) You want to enable incremental backups for you database. What should
you do?
a) Place the transaction log on a separate device from the database.
(correct answer)
b) Set Incremental Backup option to True by using no checkpoint on
recovery.
c) Set Truncate Log On Checkpoint to True by using sp_dpoption
d) Nothing. Incremental backups are always enabled.
16) Each of your branch sales offices function autonomously, but must
report monthly revenue to the corporate headquarters for roll-up
reporting. What replication model should you use?
a) Central publisher
b) Central subscriber (correct answer)
c) Publisher subscriber
d) Multiple publishers of one table
17) You want to separate the functions that have to do with the online
transaction process (OLTP) from functions that have to do with decision
support analysis on your sales database. You want to minimize the impact
on your OLTP server. What replication scenario should you use?
a) Central publisher
b) Central publisher using remote distributor (correct answer)
c) Publisher subscriber
d) Central subscriber
18) What system process can you use to determine the number of pages
allocated for a table and used by the indexes?
a) Sp_monitor
b) Sp_depends
c) Sp_spaceused
d) Sp_statistics (correct answer)
19) What is not considered a SQL Executive service?
a) Alert Manager
b) Event Manager
c) Replication Manager
d) Backup Manager (correct answer)
20) The System Administrator creates a disk dump for backing up the
payroll database by executing the following batch: sp_addumpdev
'disk','payrolldump','c:\sqldumps\payroll.dmp,2. What is the result?
a) Sysdevice table is updated and physical file is created.
b) Sysdevice and sysdatabase tables are updated and physical file is
created.
c) Sysdevice table is updated and physical file is not created. (correct
answer)
d) Sysdevice and sysdatabase tables are updated and physical file is not
created.
21) The database owner of the payroll database is named Joe. The payroll
transaction log is located on a different device than the database. Joe
executes the following command: dump transaction payroll to
payroll_trandump. What happens as a result?
a) An error message is generated that indicates that only the system
administrator can execute dump transaction.
b) The transaction log is backed up and all inactive transactions are
truncated.
c) The transaction log is backed up and all inactive transactions remain
in the log.
d) The transaction log is not backed up.
22) What task does the synchronization process perform in replication?
a) Sets administrative focus on the publication server.
b) Sets administrative focus on the subscription server.
c) Ensures that table schema and the table data in the publication and
destination databases are made identical. (correct answer)
d) Receives all changes to published data, stores changes in distribution
database, and transmits to the subscription servers.
23) You have 10 SQL computers in your enterprise. All the servers will
be replicating data to each other. How should you setup the SQL Executive
account?
a) Use Local System on each server.
b) Create one account for SQL Executive and have each server share that
account. (correct answer)
c) Create an account for SQL Executive on each server that belongs to the
administrative local group.
d) Create one account for SQL Executive on the Primary Domain Controller
and set up the remaining servers to use the Local System account.
24) You increase the locks configuration option value. What does this
change have on other SQL resources?
a) Decreases amount of memory for Data Cache. (correct answer)
b) Increases amount of memory for Procedure Cache.
c) Decreases LE Threshold percent.
d) Increases LE Threshold percent.
25) You want users to be able to quickly determine the quarterly sales of
each branch office by providing the name of the branch and quarter. You
want to inform users of the correct syntax if they do not enter all
necessary parameters, as shown: Please enter: quarterlysales
<branchname>, <quarter> For example: quarterlysales Dallas, Q495. Which
object should you create?
a) Batch (correct answer)
b) Rule
c) Stored Procedure
d) Trigger
26) You are working on a database during a recovery operation and want to
prevent outside use of the database. How would you accomplish this?
a) For load (Use CREATE DATABASE with the FOR LOAD option.) (correct
answer)
b) Offline
c) DBO use
d) Single user
27) Sp_addumpdevice 'disk','mydiskdump', 'c:\MSSQL\BACKUP\dump1.dat.'
You use Sp_adddumpdevice to create logical and physical names added to
sysdevice. When was the actual file created on the dump device?
a) Dump file not on device until first backup. (correct answer)
28) You want to send a Weekly Financial report automatically from SQL
Server via e-mail to Corporate. What Extended Procedure would you use?
a) xp_sendmail @recipients = ______ @query = ______ @attach_results,
@separator (correct answer)
29) What is the output from this statement? DUMP DATABASE mydb TO
backup1.
a) Database 'mydb' (708 pages) dumped to file <1> on device 'backup1'
(correct answer)
30) What is used to enforce referential integrity? Choose two:
a) Triggers (correct answer)
b) Foreign Keys (correct answer)
c) Views
d) Rules
e) Constraints
31) What do you use to drop a user from a database?
a) Sp_dropuser
32) What allows communication between SQL, NT, and applications?
a) ODBC (Open Database Connectivity client drivers) (correct answer)
b) OLE
33) What is the maximum number of page locks that are held prior to
releasing a [table]?
a) 200 (correct answer)
b) 256
c) 40%
d) 50%
34) What is the default database for new users?
a) Master Database (correct answer)
35) What is used in an upgrade from SQL 4.2 on an Alpha platform, to SQL
6.5?
a) Set up SQL server on the Alpha machine and then use Transfer Manager.
(correct answer)
b) BCP
36) Which option when enabled for a subscription database disables it's
ability to receive data?)
a) Read Only (correct answer)
b) DBO only
c) Single user
d) Truncate only
37) What permissions are required to create foreign keys?
a) Reference (correct answer)
b) Select (correct answer)
c) ALTER DATABASE
d) EXECUTE
38) User has to make a Foreign Key reference to a table where no select
permission has been given, what permission has to be assigned to the
user?
a) Reference (correct answer)
b) ALTER DATABASE
c) Foreign
39) What DBCC command displays the cache settings?
a) DBCC SQLPERF(LRUSTATS)
40) What replication scenario is impacted by a particular primary key
arrangement called "location specific?"
a) Central Subscriber (correct answer)
b) Remote publisher
41) How much RAM will be allocated to SQL server on a computer with 128
MB of RAM by default?
a) 16 (correct answer)
b) 32
c) 48
d) 64
42) How do you disable thread pooling?
a) 0
b) 1
c) User connections less than maximum number of worker threads. (correct
answer)
d) User connections more than workers.
43) When do you use UPDATE STATISTICS? Choose three.
a) When a significant number of records have been inserted to the table.
(correct answer)
b) When a significant number of changes have been made to indexed
columns. (correct answer)
c) When a table has been truncated and then repopulated. (correct answer)
44) You want to do a backup and setup alerts. Which tools should you
use? Choose two.
a) Sqlaltr.exe (correct answer)
b) Performance Monitor (correct answer)
c) BCP
45) What are the two ways to shrink a database (shrinking a 500MB
database by 200 MB)? Choose two.
a) Enterprise Manager (correct answer)
b) DBCCSHRINKDB (correct answer)
c) Sp_procedure
46) A scenario question:
a) Situation: A co-worker saw error message number 19 on his server and
failed to write down exactly what the error message was.
b) Required results: Where can you look to check on the error?
c) Solution:
i) Go to Enterprise Manager
ii) View the SQL error log
d) Answers:
i) The above solution meets the requirements and is an outstanding
solution. (correct answer)
ii) The above solution meets the requirements and is an adequate
solution.
iii) The above solution meets the requirements but is an inadequate
solution.
iv) The above solution does not meet the requirements although it appears
to work.
v) The above solution does not meet the requirements and does not work.
47) A scenario question:
a) Situation: A co-worker saw error message number 19 on his server and
failed to write down exactly what the error message was.
b) Required results: Where can you look to check on the error?
c) Solution:
i) Create a share on C:\sql65\log directory.
ii) Map a drive to the share
iii) Go to the file named errorlog and open the file in notepad.
d) Answers:
i) The above solution meets the requirements and is an outstanding
solution.
ii) The above solution meets the requirements and is an adequate
solution.
iii) The above solution meets the requirements but is an inadequate
solution.
iv) The above solution does not meet the requirements although it appears
to work.
v) The above solution does not meet the requirements and does not work.
(correct answer)
48) How do you create a database? Choose two.
a) Enterprise Manager - choose <new> in the list box. (correct answer)
b) CREATE DATABASE statement (correct answer)
c) Use Sp_proc
49) Your database is on drive C and your transaction log is on drive D.
Your last database backup was 6:00 PM. Your last transaction log backup
was at 10:00 am. Your media [drive c] crashed at 2:00 PM. Can you
restore the database?
a) Yes, but only up to 6:00 PM.
b) Yes, but only up to 10:00 am. (correct answer)
c) Yes, up to 2:00 PM. (Only if the database device is undamaged)
d) No it will not restore the database.
50) How do you drop a user from a database?
a) Sp_dropuser (correct answer)
51) You are DBO, and you'll be away for 2 weeks, you want to grant
Stephanie to be DBO while you are gone. She is a user in the database but
does not own any objects, so which approach should you use? [I am not
clear on the exact wording of this question so depending on how the
question is worded the answer could be either "a" or "b."]
a) sp_changedbowner stephanie true (only after first dropping user from
database) (correct answer)
b) sp_dropuser stephanie (must first drop user from database) (correct
answer)
c) sp_addalias
d) sp_changegroup dbo
52) SQL server is installed in Sales domain and uses integrated security.
T- Derek Graham is from a trusted domain called Marketing. His SQL
account name on the remote domain is T-Derek Graham. . How will the
Character mappings show his account on your server?
a) _marketing_T#Derek$Graham (correct answer)
b) \\marketing\T-Derek-Grahm
53) You created a database. What is the first step to granting user John
permission to access the database?
a) Add user to database. (correct answer)
b) Add user to the public group.
54) You own the table called Payroll and Tony, Laura and Katie are the
users of this table. You gave Tony the create view right and Laura the
stored procedure right. Tony created a view called Emp_Mgr and assigned
execute permission to Laura. Laura created a stored procedure called
sp_management_history and assigned the execute permission to Katie. Katie
executes the stored procedure as follows: "EXEC
Laura.sp_management_history." An error message is returned. Why did
Katie get the error message?
a) Katie did not have permission for the view.
b) Katie entered the command incorrectly. (correct answer)
55) What table on the subscriber server tracks the last successful
transaction replicated?
a) MSlast_job_info
56) Which subkey in HKEY Local Machine is SQL located?
a) Software (correct answer)
b) SAM
c) Hardware
d) System
57) What type of sync do you use in a slow WAN link?
a) Manual (correct answer)
b) Automatic
c) No Sync
58) What is the default throughput setting that is used by SQL in NT?
a) Maximize throughput for network applications. (correct answer)
b) Maximize throughput for file sharing.
59) User-defined stored procedures are normally owned by the user who
creates them. Who can register a stored procedure?
a) SA Account (correct answer)
b) NT Administrator
60) What statement can only the system administrator grant permission to
execute?
a) CREATE DATABASE
61) When can Performance Monitor kick off a backup of the transaction
log?
a) At 75% full and send an alert to the operator.
62) Which tool would you use to kick off a backup of the transaction log
at 75% full and fire an alert to the operator?
a) Performance Monitor and sqlalrtr.exe (correct answer)
63) The Publication server is located in Boston and Subscription server
is Stockholm, and they are connected over an expensive and fast
connection link, What type of synchronization mode is most efficient?
a) Manual (correct answer)
b) Auto
c) Snapshot
64) Where is the pager number located for the sysoperator in an alert?
a) Enterprise Manager - Alerts -Edit Alert
65) Which table in MSDB does SQL Executive service use to determine pager
number and schedule for an administrator assigned to an alert?
a) Sysoperators table in the MSDB database.
66) What file does DISK REFIT write to in the master database?
a) Sysdevices
67) In a dump transaction, database name to database device, what happens
to the transaction log?
a) Nothing Happens (Should be DUMP DEVICE) (correct answer)
b) Truncated
c) Backed Up
d) Backed Up and Truncated
68) Which API library must you use to create extended stored procedures?
a) ODS (Open Data Services) (correct answer)
b) OBDC
69) What two databases must always have truncate log on checkpoint
enabled? Select two.
a) Master Database
b) MSDB
70) You have 128 megabytes of memory on your server. How should you
configure the memory for optimal usage? How many pages do you have to
insert after the command: "sp_configure memory." You have 128 MB RAM of
total physical RAM in your computer. You want to configure SQL Server as
dedicated database server. Which value should you supply after
sp_configure memory?
a) 4000
b) 50,000
c) 51,200 (correct answer)
d) 44,000
71) You want to schedule tasks to backup the payroll database every
Monday, Wed., and Fri. at 11AM. You also want to schedule tasks to backup
the payroll transaction log 4 times a day, every 3 hours starting from 7
AM. How many tasks do you need to create?
a) One
b) Two (correct answer)
c) Three
d) Four
72) In the Client Configuration Utility, where do you enter your SQL
Server name?
a) SQL Client Configuration Utility Advanced tab
73) You have a DBO that owns a table. He created a trigger that deletes
two rows, one on one table that he owns and one on another table that is
owned by another DBO. Mary is given permission to use that trigger on
that one database and no other permissions are given on either database.
What is the result?
a) Works
b) Appears to work
c) Doesn't work (correct answer)
74) You have two devices named device one and device two. You want to
increase the transaction log by 20 megabytes. The device is 40 megabytes
now. You use DISK RESIZE device to 10,000. What is the result?
a) The transaction log was increased.
b) The transaction log was not increased.
c) The statement does not work. (correct answer)
d) Not enough information.
75) When you delete a Windows NT user, what happens to the SQL-Server
user?
a) SQL Login is still active (correct answer)
b) Drop SQL log-on
c) Drop users log-on
76) What replication scenario is used with a Heavily used server?
a) Use Central Publisher with Remote Distributor
77) What is the advantage of Dynamic Backups?
a) Users can read & write to the Database while you are backing-up.
78) After SQL memory ALLOC, which two items take remaining memory from
SQL server?
a) Procedure Cache and Data Cache
79) Which Stored Procedures would benefit most by the prior use of RA
Manager?
a) CHECKTABLE, CHECKDB, CHECKALLOC, CREATE INDEX, and UPDATE STATISTICS
80) What must you provide when adding a user to a SQL Server database?
a) User name
b) NT Account
c) Valid SQL log-on (correct answer)
81) You don't want to give direct permissions on tables and you want the
users to be able to easily update columns. What will you do?
a) Create a view
82) Will using the Dump Transaction with the Truncate_Only option backup
the Transaction log?
a) No
83) Which is the Server side library that allows Macintosh clients to
connect to SQL Server?
a) AppleTalk (adsp)
84) While trying to retrieve data from the database, the front end
displays the following error message: "Bad token from SQL Server:
Datastream processing out of synchronization. What caused the error?
Choose two.
a) This is caused when data during transmit from server has gotten
corrupt.
b) When an invalid error number is generated to the database-library.
85) After renaming a SQL Server machine, your remote stored procedures
cannot be executed on the renamed server. What must you do to correct
the problem?
a) Re-register the server with its new name in Enterprise Manager
86) Which tool will you use to automatically dump the transaction log
when transaction log of Inventory Database increases to more than 75%?
Choose two.
a) Performance Monitor
b) Sqlalrtr.exe
87) You use new database dialog to create Database, but there is not an
available Device. What is your quickest way to create a new device?
a) By selecting <new> in the Data Device list box.
88) What is the trade-off if tempdb is placed in RAM?
a) Less memory for SQL Server.
89) You encounter a corrupted device in the Master Database. What do you
use to repair it?
a) Disk Reinit and Disk Refit
90) What should you execute before or after a backup to check the
database?
a) DBCC CHECKDB, DBCC NEWALLOC, DBCC CHECKCATALOG
91) If you have a VMS client on your network, which network protocol
would you need on the server side to connect to the client?
a) DecNet Sockets
92) Network libraries enable SQL Server to communicate with client
applications over a network. Which network library should NOT be
disabled on the server?
a) Named Pipes
93) What is default setting LE Threshold Maximum?
a) 200
94) What is default setting LE Threshold Minimum?
a) 20
95) What is default setting LE Threshold Percent?
a) 0
96) What is default setting Locks?
a) 5000
97) What is default setting Procedure Cache?
a) 30
98) What is default setting RA Cache Hit Limit?
a) 4
99) What is the minimum amount of space required for a new master device?
a) 25 Mb
100) Your Publishing server goes down, what do you do. (Select two)
a) Drop Subscribers & Disconnect
b) Cleanup, & reconnect server
101) Your company has several branch offices that need to rollup copies
of their databases to the Corporate Office. What replication scenario
best fits this situation?
a) Central Subscriber
102) What type of synchronization is used when the publication and
subscription servers are connected by a slow, expensive, or low-quality
network connection, and when a table being published is very large?
a) Manual
103) Which system table on the destination database will have the ID of
the last transaction replicated from seven columns of the publisher
database?
a) MSlast_job_info
104) On a distribution server, which system file holds replication tasks?
a) MSjobs
105) What is the advantage of Dynamic Backups?
a) Users can read & write to the Database while you are backing-up.
106) How do you make sure users are not able to update a Database that is
being restored?
a) Use the CREATE DATABASE with the FOR LOAD option.
107) What happens when you use the Dump Database with multiple dump
devices specified?
a) Creates a striped backup.
108) When to backup MSDB?
a) After Sync of replication, after schedule any task
b) After create or edit any alert
c) After sending Replication task on Distribution
109) A Database gets corrupt, how do you save the transaction log?
a) Use the Dump Transaction with the NO_Truncate option.
110) If your database and transaction logs reside on the same device,
which of the following is true of managing the size of the transaction
log?
a) You can use the DUMP TRANSACTION WITH TRUNCATE_ONLY statement before
dumping the database.
111) Sp_addumpdevice 'disk','mydiskdump', 'c:\MSSQL\BACKUP\dump1.dat.'
You use Sp_adddumpdevice to create logical and physical names added to
sysdevice. When was the actual file created on the dump device?
a) Dump file not on device until first backup.
112) What happens if you disable NT error Event logging?
a) The alerts engine will not be able to pick up events from the NT
application log and No alerts will be sent.
113) What system processes cannot be terminated?
a) MIRROR HANDLER, LAZY WRITER, CHECKPOINT SLEEP, and RA MANAGER and
processes running an extended stored procedure cannot be terminated.
114) After SQL memory ALLOC, which two items take remaining memory from
SQL server?
a) Procedure Cache and Data Cache
115) What type of lock does SQL use during update data modification?
a) Exclusive lock
116) From which directory on the SQL6.5 CD-ROM would you run Setup for a
Pentium based computer?
a) /I386
117) In which system database would you add the guest login so all the
user databases would have a default login?
a) Model
118) Which Stored Procedure would you use to move a user from one group
to another?
a) sp_changegroup
119) You want to make a change to someone else's table, but do not have
the rights and they are on vacation. What command would you use to fool
the system?
a) Setuser
120) Which Stored Procedure do you use to add an Extended Procedure to
the MASTER database?
a) Sp_addextendedproc
121) What do you use to obtain the next available VDEVNO?
a) Sp_helpdevice
122) When should you use UPDATE STATISTICS?
a) When there is significant change in the key values in your index
b) A great deal of data in an indexed column has been added, changed, or
removed.
c) The table has been truncated using the TRUNCATE TABLE statement and
then repopulated
123) What allows multiple SQL Servers to be managed from a single user
application?
a) SQLOLE
124) SQL Executive can't logon using the recently created domain account
because the Domain Administrator changed the account password. What
other account could you use until you get the new password?
a) Local System (correct answer)
b) Administrator
c) SQL Executive
125) What statement permissions default to the database owner and are not
transferable?
a) LOAD DATABASE and LOAD TRANSACTION
126) Which login account is always available for use by SQL Executive if
its primary login is NOT available?
a) Local System account
127) You want to use BCP to copy Data from an ASCII text file to a Table.
What permission must you have?
a) Insert and Select when copying from a table to an ASCII text file.
128) Using BCP to add ASCII data to a table, what permission must you
have?
a) INSERT
129) What is the fastest file format to use BCP?
a) Binary
130) You want ASCII dumps for seven columns of a table, what tool should
you use.
a) BCP
131) What tools can you use to make the MSSQLServer service start
automatically?
a) Setup
132) What utilities do you use to test the integrity of a pipe
connection? Choose two.
a) Makepipe
b) Readpipe
133) Which utility might fix a corrupted sysusages table?
a) DISK REFIT
134) When can a database be marked suspect?
a) A database device is off-line.
b) A database device has been removed or renamed.
c) A database device is inaccessible for another reason (such as
insufficient permissions).
d) SQL Server detects an internal error caused by possible
inconsistencies in a database or as a result of a system layer or
configuration problem
135) Which process is not controlled automatically by the SQL Executive
service?
a) Publication
136) What does the RA Manager do?
a) Pre-fetches data into Cache.
137) One of the hard disks containing your production databases data
fails. How can you recover data modifications that had occurred since
the last backup?
a) Backup the transaction log using the 'NO_TRUNCATE' option.
138) You have accidentally set the memory allocation of SQL Server too
high, and SQL Server will not start. What is the best way to correct the
problem?
a) Restart SQL Server from the command prompt using the 'f' option and
change the configuration
139) The transaction log for your product database is normally dumped to
the device prod_tran1. Many table updates have been made since the last
database dump. You issue the following command: DUMP TRANSACTION product
TO prod_tran1 WITH TRUNCATE_ONLY. Which of the following is true?
a) The log will be cleared and will not be backed up to prod_tran.
140) What is the output from this statement? CREATE DATABASE mydb ON
mydata = 5 LOG ON mydblog = 3
a) CREATE DATABASE: allocating 2560 pages on disk 'mydbdata'
b) CREATE DATABASE: allocating 1536 pages on disk 'mydblog'
141) Which of the following is not allowed when the "Truncate Log on
Checkpoint." Database option is set?
a) Dumping the transaction log.
142) Which command will cause SQL Server to output the exact number of
table scans required to process a join?
a) SET STATISTICS IO ON
143) Which DBCC command can be used to find out how many data pages are
used by a particular table?
a) DBCC SHOWCONTIG
144) You want to quickly determine how many page reads SQL Server is
doing between two points in time. What commands could you use?
a) sp_monitor and DBCC SQLPERF (IOSTATS)
145) You are concerned that SQL Server may not have enough memory
allocated to it. What DBCC command can you use to monitor the number of
times that a page needed to be flushed from cache to make room for
another page?
a) DBCC SQLPERF (LRUSTATS)
146) What DBCC command checks pointers in data page headers to ensure
their validity?
a) DBCC CHECKTABLE
147) What DBCC SQLPERF command reports on worker thread statistics?
a) DBCC SQLPERF (NETSTATS)
148) The DBCC CHECKTABLE statement reports on the cross-integrity of data
and index pages. What DBCC CHECKTABLE statement will report on clustered
indexes ONLY?
a) DBCC CHECKTABLE (products, NOINDEX)
149) What is reported on by the DBCC MEMUSAGE command?
a) The total amount of memory configured for use by SQL Server when it
was started.
___________________________________________________________________
1) With a Windows for Workgroups Workstation on a Netware network with
integrated security and data encryption, what network library is used to
connect to SQL?
a) Multi Protocol (correct answer)
b) Named Pipes
c) Novell IPX/SPX
d) NWLink
2) You have a Netware workstation and you want one login, one password,
and data encryption. What network library should you install?
a) Named Pipes
b) Multi Protocol (correct answer)
c) NWLink/IPX/SPX
3) You have IPC and need integrated security. What network library
should you install?
a) Named Pipes
b) Multi Protocol (correct answer)
c) IPX/SPX
4) You want to communicate something over an IPC mechanism that supports
integrated security with Microsoft Windows NT. Which network protocol to
choose in setup?
a) NWLink/IPX/SPX
b) Named Pipes (correct answer)
c) Multi Protocol
d) TCP/IP