03 July, 2019

Internal Structure of Data File

Data files contain data and objects such as tables, indexes, stored procedures, and views.
Storage space allocated to a database is divided into pages that are contiguously numbered from 0 to N. That means data file is a large array of pages.
When Microsoft SQL Server starts expanding the database file from its default size, the newly created pages are numbered starting from the (last highest page number in the file)+1.
Similarly, when SQL server shrinks the file it removes pages descendingly (starting with the highest page number) from the database file.
Disk I/O operations (read/write) are performed at the page level.

Basically, a SQL Server data file has the following basic structure.
SQL server refer the first page in the data file as page number 0.In all the data file first 9  pages (till page number 8) are in same order as shown below.In the primary data file, the 10th page (Page number 9) will be the boot page which store the metadata about the database.






02 July, 2019

Extents Architecture

An extent is a group of eight physically consecutive pages in a database data file. One page is 8 KB, therefore one extent is 64 KB. This means SQL Server databases have 16 extents per megabyte.

There are two types of extents in SQL Sever:

Uniform Extents:
All eight physically contiguous pages belong and can be used only by a single object.(table, index, ...) This is a uniform extent.








Page Architecture

SQL server organizes and stores records in smaller units of data, known as pages.
The size of the page in SQL server is  8 KB. This means SQL Server databases have 128 pages per megabyte.
Page mainly divided as  Page Header, Data Row, and Row Offset.
A page can have maximum storage of 8060 bytes (ie., (Total Page Capacity 8192)-Page Header-Row Offset)=8192-96-36).




Database Architecture

Database is a systematic collection of data. Databases support storage and  manipulation of data. Databases make data management easy.
Microsoft SQL Server databases contain two primary file types required for proper functioning of a OS. They are: Data file and a Log file.
















08 February, 2019

Prerequisite to Config Database Mail

Step 1: Verify SQL Server Agent Service is Running:

Messages in Database Mail are sent by the SQL Server Agent. If the Agent is not running, the messages will be accumulated in the msdb database and sent when the Agent service gets started again.
Use the master..xp_servicecontrol system stored procedure to check the status of the SQL Server Agent process. If the Agent is stopped, you will want to start it in order to send messages through Database Mail.
USE master
Go
EXEC xp_servicecontrol N'QueryState', N'SQLServerAGENT';
Go