Data Architecture

Question 1

Extension for primary data file is

Answer 1

.mdf

Question 2

Extension of secondary data file

Answer 2

.ndf

Question 3

Extension for log file

Answer 3

.ldf

Question 4

Every database has 2 files

Answer 4

Primary file and log file

Question 5

A data base will have 2 forms of databases

Answer 5

System database ( with 4 Master, Model, Msdb, Temp file) and User database

Question 6

Instance 2 will have a different storage than the default T/F

Answer 6

True

One instant is independent on each other even if it run on the same server

Question 7

How do you name the second or third instant

Answer 7

The file name back slash and the instance name
CR1WINPSQL01\Instance2

Question 8

Data base is made up for 2 file

Answer 8

Primary and log file

Question 9

Which database is where all the data will be stored in permanently

Answer 9

Primary database

Question 10

Data is durable or permanent or committed means

Answer 10

It is stored in primary data file

Question 11

Data file is made up of _______ and the size of each _____ is ______

Answer 11

Pages
The size of each page is 8KB =8192 Bytes
Bytes stands for one character on a keyboard a,b….

Question 12

Data file is made up of

Answer 12

Pages

Question 13

Data base objects are

Answer 13

Tables, view, function, stored procedure, triggers

Question 14

When we different set of table it will be on

Answer 14

Table 2 and so forth

Question 15

When 8 consecutive tables are occupied with the same content it is called

Answer 15

Uniform Extents

Question 16

When 8 consecutive tables are filled with different contents they are called

Answer 16

Mixed extent

Question 17

What is the size of an extent

Answer 17

An extent is 8 pages so it will be 8 KB times 8 kb which will be 64 KB

Question 18

A page- 8 KB -there is 8192 However there is a cost - area that is used for other things. Those things are

Answer 18

Page header-96 Byte
Row offset- where the raw starts -36 Bytes
The actual body of the page - 8060 Bytes

Question 19

Is it mandatory to have a log file ?

Answer 19

Yes

Question 20

Log file is where

Answer 20

Every action made on the data is stored

Question 21

Every file regardless of data file or log file it should have 5 things

Answer 21

Logical name
Physical name
Initial growth
File growth
Max size

Question 22

The name that is used to refer to the file

Answer 22

Logical name

Question 23

Check point

Answer 23

The point where SQL server check if there is a successful transaction and transfer it to the Data file.

Question 24

Should we always delete data from log file after every check point

Answer 24

It depends - on how critical the data is

Question 25

Should we always delete data from log file after every check point

Answer 25

It depends - on how critical the data is

Question 26

The three recovery models

Answer 26

Full Bulk load Simple

Question 27

Which recovery is good for a critical data base

Answer 27

Full recovery

Question 28

If a data is not too sensitive you can use which recovery

Answer 28

Simple

Question 29

Who decides if the data will be on full recovery or simple recovery

Answer 29

The application owner ( AP owner)

Question 30

What kind of recovery model do we use for Test or Developmental data base

Answer 30

Simple

Question 31

When the log file is empty due to all the file being moved to the Data File

Answer 31

Truncated

Question 32

Data gets backed up when frequently depends on

Answer 32

How critical the data is

Question 33

Three types of Back ups in SQL

Answer 33

Full Differential Transactional

Question 34

Complete back up of a data base is called

Answer 34

Full back up

Question 35

Which back up takes the different from one full back up until the next scheduled full back up

Answer 35

Differential back up - this is optional

Question 36

If you don’t have full back up, you cannot take differential back up or transactional back up

Answer 36

True

Question 37

Which back up is not applicable if the recovery is simple

Answer 37

Transactional log back up

Question 38

What are the file extensions for Full back up, differential back up and transactional back up

Answer 38

Full back up ( *.BAK) Differential back up- (*.DIF) transactional back up (* .TRN)

Question 39

Which back up is the mandatory back up

Answer 39

Full backup

Question 40

Size of transaction log file recommendations if you are using a full recovery model

Answer 40

25% - if the whole size of the data file

Question 41

Recommend size of transaction log file for simple recovery model is

Answer 41

10% of the database

Question 42

Which recovery model capture everything except bulk operation

Answer 42

Bulk Logged recovery Model- it does not load them as individual so you can’t open it

Question 43

Bulk logged recovery model is exactly like full but it ignores anything in bulk

Answer 43

True

Question 44

File group is not applicable for the log file

Answer 44

True

Question 45

When we create additional data file we cal if

Answer 45

Secondary data file You can store it under system file group( default) or you can create additional file group