FILE STRUCTURE OF MSSQLSERVER

What is File Structure into SQL Server?

1) Primary File

2) Secondary File

3) Transaction Log File.

1) Primary File :- In a Database We have just .mdf extension primary file. It can store two objects.

  • System Define Objects.
  • User Define Objects.

2) Secondary File :- This file is Optional and User Define. It doesn’t Store System Define Objects. It’s Extension is .ndf. These can be multiple. There is no limitation of size. It depends on Hard disk.

Transaction Log File :- It’s essential, Every Inserting table record entry first in log file in sequence and then into permanent table or database. SQL Server uses a write-ahead modification process that requires all changes to be

written to transaction logs before they are written to the database. Means wright ahead log. It’s basically used for backup and recovery purpose.

Data Storage Architecture of SQL SERVER.

It store data into pages. This is a smallest unit of Datasotrage into SQL Server. The size of one page 8192 bytes.  We never used it fully. 96 bytes for page header. And Actual size of page is 8096 byte is used by Us.

1MB=128 pages.

We have different Type of Pages for data storage.

1) Data pages.

2) Global Allocation Map

3) Index Allocation Map

4) Index Pages.

5) Page Free space Pages.

6) Text/ Image Pages.

How Many Types of Database into SQLSERVER 2008 BY Default?

There are Five Types

  • Master Database
  • Model Database
  • MSDB Database
  • Tempdb Database
  • Resource Database
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s