1.What is the default server group?
A. SQL6.5
2. If you want to connect to a VMS client, which NETLibrary do you use?
A. DECNet Sockets
3. What are the Publications default tables?
A. syspublications and syssubscriptions
4. What is the Subscriptions default table?
A. MSJob_last_info
5. What is the Distribution default table?
A. MSJob
6. When using a MAC workstation with SQL what kind of security do you use?
A. mixed
7. To connect to SQL using SPX as default on a windows client, what do you use?
A. DBMSSPX.EXE (tsr)
8. I can't remember the whole question, but it included syntax regarding a dump. The key was in the line that looked like: DUMP xxx to device1, device2, device3, ...etc.
A. the answer is: Striped Backup Set
9. This one will be two questions in a row with a different answer. The question goes something like this: A SQL user gets error 19 and before reading it clicks ok. Later the user tells you about it but can't tell you what it said. Where can you look to check on the error.
A. The answers for these are the infamous: This works great, this kind
of works, this almost works, this doesn't work but looks like it does, this
doesn't work at all.
The first question has a proposed solution that looked something
like this: blah, blah, and blah, ... look at the SQL error log. this answer
works. Pick A
The second question has a proposed solution that looked something like this: blah, blah, blah, ... connect to the local machine, create a share to the C:\XXX\LOG directory, and use an text editor to view the file.
This one always threw me because you can edit the SQL log file with a text editor, but when a bunch of us who took the test mulled it over we decided that it doesn't work. We picked E
10. How do you determine your cache size?
A. DBCC SQLPERF (RLUSTAT)
11. Can't remember the whole question. Something like this: Pub has a communication server, it goes into an infinite loop...
A. Unsubscribe, re-subscribe, send out a clean up task
12. You are a dbo of the sales database. You want to allow your users to do a query each quarter, but if they don't enter the correct information you want them to get a message telling them what to do?
A. trigger
view
batch
STORED PROCEDURE This is the best answer we could come up with after speaking with one of the instructors and beating our heads against the wall. If you know this answer not to be the best one let me know.
13. Who can register and grant permission to a extended procedure?
A. SA
14. What is the only option that should always be on the MASTER DATABASE?
A. DBO_USE_ONLY
15. Who has the rights to UPDATE STATISTICS?
A. Anyone
16. When do you use UPDATE STATISTICS?
A. when there are a-lot of changes
17. We are all fuzzy on this question. This is the best we could come
up with.
Your database and log are on the same device.
Syntax: DUMP DATABASE
DUMP TRANSACTION to dump device
A. log is backed up and all inactive transactions are truncated
18. The device is filled. How can you restore?
A. Use DUMP TRANSACTION WITH NO TRUNCATE
19. You made changes, increased size, lost the master, can you recover?
A. No
I think for the test, this is the correct answer. For real life you could use your documentation of the database (to increase the size of the master again) and the Transaction Log (if it hasn't been truncated - unlikely) to recover from this - which is another advantage. However, this is not one of the options on the test. (as usual!#@*)
20. Your server has 128mb of RAM. How much memory does SQL Setup allocate for SQL?
A. 16 (the key is Setup)
21. Which command can SA assign
A. Create Database (it's the only answer that works - know all the correct answers)
22. Which process can't you kill?
A. Following are processes that you cannot kill. Pick any of these that are in the answer on the test. You will probably see 3 out of these 4 plus a bogus one.
23. This question tested your knowledge of tasks. It went something like
this:
You want to schedule a backup for once a week (or) 4 times a day AND you
want to schedule another backup for every Mon, Wed, Fri. How many tasks
do you need to set up?
A. 2
24 What does a 3rd party vendor use to develop extended procedures?
A. ODS.DLL (we are pretty sure about this, but not positive)
25. Which of these will benefit while using the RA Manager?
26. What do you do if the NetCommand Queue Length is high?
A. Increase worker threads
27. Anytime you increase worker threads, what happens?
A. Decreases memory for SQL
28. What is the default pages for LE Threshold?
A. 200
29. What is the percentage of procedure cache?
A. 40%
30. How do you decrease (or) disable pooling?
A. Increase amount of threads to cover all users
31. There were a couple of questions, which hit on the different replication
models. They were something like:
- Which would you use to configure a location specific...
A. Central Subscriber
- Which would you use to configure a localized variable...
A. Central Subscriber
- one question refers to rollup accounting
A. Central Subscriber
32. Server is being used extensively for publishing. Company wants to ease server load. *(In the question you will see the word "Separate" - this is the key)
A. Central with Remote
33. You are using an expensive link. What is the most efficient way to replicate?
A. Manual Replication
34. There is a long question that tells you who made what tables from what, and then asks you something like: If Cindy tries to delete XXX from table YYY, what is deleted (related to "broken owner chains")?
A. Deletes one entry
35. There is a question that asks you something like: You delete users from NT. What happens to the users in SQL?
36. There is a question that asks something like: A user named John who is part of the Marketing Domain logs on in the Sales Domain. What would his login look like?
A.Marketing_John (NT equivalent = MARKETING\John)
(Although I do remember it having the other characters ($ and #) I don't remember how the question would have been formulated - mainly memorize the default mappings:
_ = (\) domain separator "\"
# = (-) hyphen
$ = ( ) space
37. How do you disable locking on a table?
A. select read only (it's the best choice)
38. You are using SQL in a Domain. Users complain of slow performance. What can you do to improve performance? Select all that apply.
39. You just published a database. A table is not showing up as an option that you can publish. Why?
A. Didn't create a primary key
40. What does an extended procedure use to make an external call to something outside of the SQL environment?
A. SQLOLE
41. In which subkey is SQL in in the registry?
A. HKEY_LOCAL_MACHINE\SOFTWARE
42. What is the default size of the Master database?
A. 25mb
43. You add a new user but you don't specify a database. What database does the user get added to by default?
A. Master
44. What 2 things do you need to set up an alert?
45. If you turn logging off in NT what happens?
A. You can't set alerts.
46. By default, how is SQL configured?
A. Maximize throughput for Network Applications
47. What is the default directory that SQL installs to?
A. MSSQL
48. The server goes down and reboots itself, but the Executive Service does not start. How do you make it start automatically?
A. in Setup (the only good answer)
49. What services does the executive services oversee?
A. For this answer the only answer that doesn't fit is: Event Managing (do not select this)
50. What permissions do you need to bcp into a database?
A. INSERT
51. What permissions do you need to bcp out of a database?
A. SELECT, SELECT
52. This question asks something like: You want to be able to e-mail a text-delimited file to your boss. You want him to be able to put it into a spreadsheet. ?? (keywords here are "text-delimited file" and "put it into a spreadsheet")
A. bcp and then send the file
53. How do you add a user to a group?
A. sp_adduser