sql admin

The answers to these questions are best guesses of the contributor and may not be correct

1. How to show number of pages allocated for a table and used by indexes? A. sp_monitor; B. sp_depends; C. sp_spaceused; D. sp_statistics; Answer: C

2. What does following do?

disk resize name='payroll_log', size=15560

A. Size increased by 15MB; B. Increased to 15MB; C. Increased to 30MB; D. Error message; Answer: D

3. Want to inform users of the correct syntax if they don't enter all the necessary parameters. How to do it? A. Batch; B. Rule; C. Stored procedure; D. Trigger; Answer: C

4. You have inventory db with log on separate device. Want to increase log by 20MB. You have created device invlogdev2 of 40MB. Is the following correct?

alter database inventory on invlogdev2=20

exec sp_logdevice inventory, invlogdev2

A. Yes; B. No, only increased by 10MB; C. No, trans. log moved instead of increased; D. No, need DBCC CHECKALLOC to determine errors; E. Cannot determine based on the info; Answer: A

5. Which permission allows you to copy to db using bcp? A. Select into/bulk copy; B. Insert; C. Select; D. Update; Answer: B

6. Want users to update several columns in two tables w/ a single command. How? A. Stored procedure; B. Temporary db; C. Rule; D. View; Answer: D

7. Which statement by SA used to impersonate another user in a db to create objects or to test user permissions? A. grant setuser; B. setuser; C. set procid; D. set identity_insert; Answer: B

8. Monica owns sales table. DBO of customer table creates delete trigger

create trigger customer_delete on customer for delete as

delete monica.sales from monica.sales, deleted

where sales.cust_id=deleted.cust_id

No other permissions on both tables for DBO & Monica. DBO executes

grant delete on customer to clericalusers

A user member of clericalusers tries to delete a row from customer table. What happens? A. Successfully deleted in both tables; B. only the row in customer table is deleted; C. user prompted for the sales table password; D. receives "delete permission denied"

9. sp_addumpdevice 'disk', 'payrolldump', 'c:\sqldumps\payroll.dmp', 2

A. sysdevices updated, physical file created; B. sysdevices & sysdatabases updated, physical file created; C. sysdevices updated, physical file created first time dumped; D. sysdevices & sysdatabases updated, physical file created first time dumped; Answer: C

10. Sales offices work autonomously. Must report monthly revenue to headquarter. A. central publisher; B. central subscriber; C. publishing subscriber; D. multiple publishers of one table; Answer: B

11. sales db and transaction log on same device

dump database sales to payrolldump

dump transaction sales with truncate_only

A. the log backed up to DISKDUMP; B. the log backed up with the db and all completed transactions are truncated; C. the log backed up with the db and all completed transactions remain; D. the log is not backed up; Answer: B

12. What does dynamic backup enables? A. backup multiple dbs at the same time; B. read and write to db as it's being backed up; C. query, but not modify the db being backed up; D. access all dbs except the db being backed up; Answer: B

13. What should do enable incremental backup? A. Place the transaction log on a separate device; B. Set incremental option to true using no chkpt on recovery; C. set trunc. log on chkpt. option to true using sp_dboption; D. Nothing; Answer: A

14. dump transaction payroll to payroll_dump

A. error: only SA has permissions; B. OK and log truncated; C. OK and log remains; D. Not backed up; Answer: B

15. What Server Group is installed by default? A. Admin; B. Master; C. Main; D. SQL 6.5

16. 10 SQL Servers in an enterprise. All will replicate to each other. How to setup SQL Executive account? A. LocalSystem on each. B. Create one account for SQL Executive and have each server share that account. C. Create an account for SQL Executive on each server that belongs to the Administrators. D. Create one account for SQL Executive on the PDC and set up the remaining servers to use the LocalSystem account; Answer: B

17. Want to automatically backup transaction log when it's 70% full. Want to record the event in the event log and send an e-mail to dbo. What to use? A. bcp.exe; B. SQLALRTR.EXE; C. Performance Monitor; D. xp_logevent; Answer: B

18. Scenario: User of MARKETING server received error w/ severity level 19, pressed OK before reading it. How to get more info? Solution: 1. Connect to MARKETING server by SQL Enterprise Manager. 2. Select Server->Error Log; Answer: Meets requirement and is an outstanding;

19. Scenario: see 18. Solution: 1. Connect and create share on the C:\SQL65\LOG directory if one has not already been created. 2. Open ERRORLOG w/ text editor; Answer: Meets requirement but not desirable

20. T-Derek Graham from MARKETING domain is a temporary user. What is his SQL login ID? Answer: _Marketing_T#Derek$Graham

21. Want to e-mail query in comma-delimited text. A. SQL Transfer Manager to ASCII file and xp_sendmail; B. select into, bcp, send; C. xp_sendmail @query @atach_results; D. xp_sendmail @query @atach_results @separator; Answer: D

22. Which option on Server service automatically selected by setup? A. Balance; B. Min memory used; C. Max throughput for File Sharing; D. Max throughput for Network Applications; Answer: D

23. Which sybkey in HKEY_LOCAL_MACHINE stores entries? A. HARDWARE; B. SAM; C. SOFTWARE; D. SYSTEM; Answer: C

24. What must provide when adding user? A. Default db; B. Password; C. Login ID; D. Valid NT login ID; Answer: C

25. Admin is a group in sales db. Michael is a user in sales. How to make Mike member of Admin? A. sp_adduser; B. sp_addgroup; C. sp_depends; D. sp_changegroup; Answer: A

26. Want all user dbs to be created to allow guest access. Where to add group? A. master; B. model; C. pubs; D. tempdb; Answer: B

27. Integrated security. User is deleted from NT accounts. What happens? A. All login and db user info is auto deleted; B. Only NT logon; C. NT logon, SQL logon ID delete but user name remains in the db; D. NT logon, SQL logon ID delete and user name is deleted only from default db; Answer: B

28. Media failure. Distribution server restored from backup. Log reader failure. Suspect that dist. server doesn't contain a valid pointer to the last distributed transaction for the publishing server. What to do? A. Unsubscribe then resubscribe subscribed server to the publications that caused log reader failure then clean up. B. resync the subscription manually; C. drop the distribution db, and sync manually; D. drop the subscribed server distribution db, recreate it and then reconfig the subscriptions; Answer: A

29. What does sync process perform? A. Sets admin focus on the publishing server; B. Set admin focus on the subscribed server; C. ensure that the table schema and the table data in the publishing and destination dbs are made identical; D. receives all changes to published data, store the changes in the distribution db, and transfer them to subscribed server; Answer: C

30. Which system table contains entry for each transaction stored in the distribution db? A, MSjobs; B. Mslast_job_info; C. sysarticles; D. syssubscriptions; Answer: A

31. Which replication tasks are scheduled automatically when setting up replication through SQM Enterprise Manager? A. Distribution; B. Log reader; C. Publishing; D. Sync; Answer: A, C, D

32. Publishing by OLTP Server. Want to separate the process. A. Central publisher; B. Central publisher using remote distribution; C. publishing subscriber; D. Central subscriber; Answer: B

33. What is not SQL Service? A. Alert manager; B. Event manager; C. Replication manager; D. Backup manager; Answer: D