1.What cause DB mark suspects during recovery?

1. transaction log is empty
2. Server shutdown during active transaction
3. Error severity between 17 and 18 occur
4. Process bring db to consistant transaction state during setup fails.

2. Payroll DB is call MoneyBag, you change the computer name to Finance, after that, replication failed. How to fix? Choose 2

1. execute sp_rename
2. change publication server name in SEM
3. edit server name in working directory data entry under replication subkey
4. exeute sp_dumpserver, sp_addserver, sp_serveroption.

3. Store procedure that determine the number of pages allocate for the table and used by indexes:

1. Sp_monitor
2. Sp_depends
3. Sp_spaceused
4. Sp_statistics

4. To minimize the inpact of OLTP, which replication scenario should choose?

1. subscribtion
2. publishing subscriber
3. publication
4. central publisher with remote distributor

5. which of following will auto execute a user defined store procedure, execute dump transaction log then execute dump database whenever 1105 error occurred?

1. Alert
2. Task scheduler
3. Performance monitor

6. DBO is Joe, Transaction log and database is in separate device. Dump transaction payroll to payroll dump. What will happened?

1. get error message that only SA can dump transaction log
2. Transaction log backed up, all inact transaction log are truncated.
3. Transaction log backed up, all inact transaction log are remained in the log
4. Not backup.

7. How to reduce physical I/O, choose 3:

1. user larger cache
2. install faster disk subsystem
3. change database design
4. verify appropriate index created

8. 5G DB, what can be benefit from RA

1. create index, update statistics, DBCC Checkdb
2. create table, create db, create index
3. dbcc changedb, drop db, disk resize
4. update statistics, drop table, create table

Free test

Network Libraries

1) From Windows for groups you want to provide for integrated security and data encryption to SQL Server?

Multi-proticol

2) If you have a VMS client on your network, which network protocol would you need on the server side to connect to the client?

DecNet Sockets

3) Network libraries enable SQL Server to communicate with client applications over a network. Which network library should NOT be disabled on the server?

Named Pipes

4) Use ADSP for Appletalk networks, NOT running TCPIP

5) Bad Token from SQL Data stream out of Sync. - Data sent is corrupt,

The network generate an error MSG that unknown to DB Library.

Defaults

6) What is Default setting LE Threshold Maximum?

200

7) What is Default setting LE Threshold Minimum?

20

8) What is Default setting LE Threshold Percent?

0

9) What is Default setting Locks?

5000

10) What is Default setting Procedure Cache?

30

11) What is Default setting RA Cache Hit Limit?

4

12) What is the minimum amount of space required for a new master device?

25 MB

13) How much RAM will be allocated to SQL server on a computer with 128 MB of RAM by default?

16 mb

Replication

14) Your Publishing server goes down, what do you do? (Select two)

" Drop Subscribers & Disconnect, Cleanup, & reconnect server

15) Heavily used server, What replication scenario?

- use Central Publisher with Remote Distributor

16) Your company has several branch offices that need to rollup copies of their databases to the Corporate Office. What replication scenario best fits this sitution?

Central Subscriber

17) Using replication for 10 servers, how do you set up the administrative user?

The Windows NT user account which the SQL Executive service is using to log on at startup. In a single domain environment, it's normally easiest that this account be a domain-level account which is a member of the 'Domain Admins' global group. This will normally automatically make it a member of the local Administrators group on each of the SQL Server systems.

This assumes that the Administrators group has been left as the default group having SA access to SQL Server. If the Administrators group has been revoked SA access (using the SQL Security Manager utility), then the SQL Executive user account will have to be a member of whatever group does have SA access, as well as administrators (or Domain Admins).

Finally, you just have to make sure that all the SQL Server systems participating in replication are using the same account for the SQL Executive service.

18) Which replication scenario requires a "location specific" column to be added to the primary key....Central Subscriber

19) Manual synchronization is particularly useful 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

20) Which system table on the destination database will have the ID of the last transaction replicated from 7 columns of publisher database?

Mslast_job_info

21) On a distribution server, which system file holds replication tasks?

" MS_JOB "

Backup/Restore

22) You execute the command DUMP TRANSACTION LOG with TRUNCATE ONLY, will the Transaction Log be backed-up?

" NO transaction log will be Backed-up "

23) What is the advantage of Dynamic Backups?

" Users can read & write to the Database while you are backing-up "

24) How do you make sure users are not able to update a DB that is being restored?

Use the CREATE DATABASE with the FOR LOAD option.

25) Using the Dump Database with multiple dump devices specified will create a striped backup.

26) When to backup MSDB.?

After Sync of replication, after schedule any task, After create or edit any alert, After sending Replication task on Distribution

27) A DB gets corrupt, how do you save the transaction log? Use the Dump

Transaction with the NO_Truncate option

28) Will using the Dump Transaction with the Truncate_Only option backup the Transaction log? No

29) If your Database and Transaction Log are each on separate devices, when you execute DUMP TRANSACTION LOG will this clear the LOG? " YES "

30) LOAD DATABASE and LOAD TRANSACTION permission default to the database owner and are not transferable

31) Which databases by default have the ‘Truncate log on check point set on by default? Master & MSDB

32) What are the two ways to shrink a database (shrinking a 500MB database by 200 mb)?

33) Your database and transaction log reside on the same device. Which of the following is true of managing the size of the transaction log?

You can use the DUMP TRANSACTION WITH TRUNCATE_ONLY statement before dumping the database

34) sp_addumpdevice 'disk', 'mydiskdump', 'c:\MSSQL\BACKUP\dump1.dat' You use Sp_adddumpdevice to create logical and physical names added to sysdevice. When did the actual file get created on the Dumpdevice ?

" Dump file not on device until first backup "

35) Your database and transaction log reside on the same device. Which of the following is true of managing the size of the transaction log?

You can use the DUMP TRANSACTION WITH TRUNCATE_ONLY statement before dumping the database

Alerts/Tasks

36) If you disable NT error Event logging - The alerts engine will not be able to pick up events from the NT application log and No alerts will be sent.

37) How many backup tasks are needed for backing-up your database once on Monday, Wednsday, and Friday AND backing-up your transaction log Four times each day starting at 7:00am.

Answer: Two

SQL MAIL

38) You want to send a Weekly Financial report automatically from SQLserver via e-mail to Corporate. What Extended Procedure would you use?

XP_sendmail @Query, @Attach_reults, @seperator
Performance & Optimizing

39)System processes (MIRROR HANDLER, LAZY WRITER, CHECKPOINT SLEEP, and RA MANAGER) and processes running an extended stored procedure cannot be terminated.

40) After SQL memory ALLOC, which two items take remaining memory from SQL server?

Procedure Cache and Data Cache

41) Which tool would you use to kick off a backup of the transaction log at 75% full and fire an alert to the operator?

Performance Monitor.

42) How to avoid thread pooling ?

Increase the number of worker threads. (Number of users connection is less then Max of worker thread)

43) What can help to determine if you have large enough cache?

DBCC SQLPREF(lrustat)

44) For alerts, Performance Monitor & SQLALRTR.EXE go hand in hand.

45) DBCC Checkcatalog checks for consistency in and between system tables

46) what type of lock SQL use during update data modification?

Exclusive lock

47) table object are stored using .....?

Extent

Trouble Shooting

48) Users can control locking by adding an optimizer hint to the FROM clause of a SELECT statement or by setting the TRANSACTION ISOLATION LEVEL with the SET statement. For syntax and restrictions, see the SELECT and SET statements.

Installation

49) When you run the SQL Server setup program, your Windows NT system is automatically configured to maximize throughput for network applications

50) Something came out about the registry so make sure to know it.
HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \MSSQLServer \MSSQLServer

51) From which directory on the SQL6.5 CD-ROM would you run Setup for a Pentium based computer?

" /I386 "

52) What Option should always be set on your SQL Master & MSDB databases?

" Truncate Logs on Chk/Pt "

53) How will you upgrade from SQL Server 4.2 on an Intel machine to SQL server 6.5 on an alpha machine?

The setup program cannot upgrade from one processor version to another. If you want to do upgrade you would have to do a fresh install on the Alpha machine and then use SQL Transfer Manager to simply transfer data from one architecture to another.
**Cross-platform dumping and loading is not supported. For example, a database dumped on a MIPS-based computer cannot be loaded into an Intel-based computer. If you need to move data from one platform to another, use the Microsoft SQL Transfer Manager

Commands

54) Password used by SQL Executive has expired or something to this effect.What user ID can you use?

Local System

55) In which system database would you add guest so all user database would have a default login?

Model

56) Which Stored Procedure would you use to move a user from one group to another?

sp_changegroup

57) You want to make a change to someone elses table, but don't have the rights and they are on vacation. What command would you use to fool the system?

Setuser

58) Only SA can grant permission to CREATE DATABASE

59) Which Stored Procedures would benefit most by the prior use of RA Manager?

CHECKTABLE, CHECKDB, CHECKALLOC, CREATE INDEX, UPDATE STATISTICS

60) Which Stored Procedure do you use to add an Extended Procedure to the MASTER database?

SP_ADDEXTENDEDPROC

61) You wish to enforce an Input Rule for data by using ASCII text on the users screen like "Enter Corp. Location, then state ". What command line utility would you use?

" Batch "

62) What will SP_Statistics do?

Returns a list of all Indexes on a specified table

63) Use sp_helpdevice ro obtain the next available VDEVNO.

64) Which API library must you use to create extended stock procedures ODS (Open Data Services)

65) You drop a remote server with the sp_dropserver system procedure, which deletes the entry for that server from the sysservers table. When you drop a remote server, a user can no longer access its information through the local server.

66) What are the two ways to shrink a database (shrinking a 500MB database by 200 mb)?

>From the Enterprise Manager, select a database, and then choose the Edit Database button.
Choose the Shrink button. And DBCC SHRINKDB

67) Which two can be used for maintaining cross table referential integrity?

Triggers & FOREIGN KEY

68)When should you use UPDATE STATISTICS?

* there is significant change in the key values in your index great deal of data in an indexed column has been added, changed, or removed the table has been truncated using the TRUNCATE TABLE statement and then repopulated

69) 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?

Create a View

70) Who has rights to run UPDATE STATISTICS on a table?

DBOO or Owner of the Object

71) SQLOLE allows multiple SQL Servers to be managed from a single user application

ACCOUNT MGT

72) Delete Windows NT user, what happens with SQL-Server user?

SQL Login is still active

73) You have a user from a trusted Domain named Marketing using integrated security and wanting to connect to your SQL server. His account name on the remote domain is T-Derek Graham. How will the Character mappings show his account on your server?

"_Marketing_T#Derek$Graham". Just remember that:

\	turns into _
-	turns into #
<SPACE>	turns into $

74) SqlExecutive can't logon using the recently made Domain account because the Domain Admin changed the Account Password. What other account could you use until you get the new password?

Local System

75) Who can insert and grant permission to extended stored procedure (SA)

Preference allows user to create Foreign Key constraint without having select permission on that table. Reference permission needs to be granted to the person that create the foreign key.

76) What must you provide to add a user to SQL server?

The answer is a valid SQL server login

77) When adding a user to the database, what information must you provide?

SQL Login ID

78) LOAD DATABASE and LOAD TRANSACTION permission default to the database owner and are not transferable.

79) Which login account is always available for use by SQL Executive if its primary login is NOT available?

Local systems account

80) User-defined stored procedures are normally owned by the user who creates them. Who can register an extended stored procedure?

SA Account

Transfering Data

81) You want to use BCP to copy Data from an ASCII text file to a Table. What permission must you have?

" Insert " " Select " when coping from a table to an ASCII text file.

82) Using BCP to add ASCII data to a table, what permission must you have?

" INSERT "

83) what is the fastest file format to use bcp?

Binary

84) The bcp utility can be used to copy data, from a table or view into an operating system file, or from an operating system file into a table. To copy data from an operating system file into a table, a user must have INSERT permission on the table. To copy a table or view to an operating-system file, the user must have SELECT permission

· sysobjects

· syscolumns

· sysindexes

85) You want ASCII dumps for 7 columns of a table, what tool will you use?

BCP

Trouble Shooting

86) Your NT server for SQL has 128 mb of total memory. How much would you configure for SQL if this machine were to be dedicated solely to SQL server?

Using sp_config,set memory size to 51200

87) You then realize your database had some changes to the schema and tables after the last backup. Which command could you run that might fix your database?

refit

88) Only 2 scenario questions: A user calls to tell you about an error on his server , but he cannot remember the full details of the error. How do you find out what the error was?

a. The first solution said to go into the server error log, which is an "outstanding" solution. Microsoft makes you choose between outstanding and adequate.

b. The second solution recommends to create a share, map to the other server and use a text editor to view the contents of the log in c:\sql65\log. This does NOT work because the error log is in C:\MSSQL\Log -eerror_log_ path

Indicates the fully qualified path for the error log file (typically, C:\SQL60\LOG\ERRORLOG). If you do not provide this option, the existing Registry parameters are used.

89) What tools can you use to make the MSSQLServer service start automatically?

Setup

90) what utilities do you use to test the inegrity of a pipe connection?

makepipe and readpipe

91) Which utility might fix a corrupted Sysusages table?

Refit

92) why a database can be marked suspect?

• A database device is off-line.
• A database device has been removed or renamed.
• A database device is inaccessible for another reason (such as insufficient permissions).
• SQL Server detects an internal error caused by possible inconsistencies in a database or as a result of a system layer or configuration problem

SQL EXEC

93) Under replication the publication process is not controlled automatically by the SQL Executive service

94) Name which Managers that run under SQL Exec. (Select three) " Task manager, Alert manager, Event manager " (NOT Backup)

95) When using the cmd Create Database = 200 (megs) the system response will be " Database created = 101204 on DataDevice ".

96) What table contains the operator's pager number?

Sysoperators

97) After renaming a SQL Server machine, your remote stored procedures cannot execute on the renamed server. What must you do to correct the problem?

Re-register the server with its new name in Enterprise Manager

98) Which database option should always be set in the Master database?

Truncate Log On Checkpoint

99) What does the RA Manager do?

Pre-fetches data into Cache

100) What is the output from this statement?

DUMP DATABASE mydb TO backup1

Database ‘mydb’ (708 pages) dumped to file <1> on device 'backup1'

101) One of the hard disks containing your production database’s data fails. How can you recover data modifications that had occurred since the last backup?

Backup the transaction log using the ‘NO_TRUNCATE’ option.

102) Joe creates a table called Table1 and grants all permissions on it to Mary. Mary creates view View1 based on Table1, and then creates a stored procedure Proc1 to update her view. Mary grants execute permission on Proc1 to Ann. When Ann tries to execute Proc1, her access is denied. What is the most likely cause of this?

Mary is not the owner of Table1

103) 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?

Restart SQL Server from the command prompt using the ‘f’ option and change the configuration

104) 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?

The log will be cleared and will not be backed up to prod_tran1

105) What is the output from this statement?

CREATE DATABASE mydb ON mydata = 5 LOG ON mydblog = 3

create DATABASE;allocating 2560 pages on disk ydbdata

create database;allocating 1536 pages on disk ydblog

106) Which of the following is not allowed when the "trunc. Log on chkpt." Database option is set?

Dumping the transaction log

107) Which command will cause SQL Server to output the exact number of tables scans required to process a join?

SET STATISTICS IO ON

108) Which DBCC command can be used to find out how many data pages are used by a particular table?

DBCC SHOWCONTIG

109) You want to quickly determine how many page reads SQL Server is doing between two points in time. What commands could you use?

sp_monitor and DBCC SQLPERF[IOSTATS]

110) 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?

DBCC SQLPERF[LRUSTATS]

111) What DBCC command checks pointers in data page headers to ensure their validity?

DBCC CHECKTABLE

112) What DBCC SQLPERF command reports on worker thread statistics?

DBCC SQLPERF(NETSTATS)

113) The DBCC CHECKTABLE statement reorts on the cross-integrity of data and index pages. What DBCC CHECKTABLE statements will report on clustered indexes ONLY?

DBCC CHECKTABLE(products, NOINDEX)

114) What is reported on by the DBCC MEMUSAGE command?

The total amount of memory configured for use by SQL Server when it was started.

115) What command checks for consistency in and between system tables?

DBCC CHECKCATALOG