How to find the DB Growth in sql server(simple way to find out the db growth in sql server)
------------------------------------------------------------------------------------------

select 

 BackupDate = convert(varchar(10),backup_start_date, 111) 

 ,SizeInGigs= convert(  varchar(20),backup_size/1000000)

 ,database_name

from msdb..backupset 

where 

 database_name = 'PGAccruals'

 and type = 'd'

order by 

 backup_start_date desc

How to find the Tape,disk and Virtual device(device types) backups  in Sql server:
--------------------------------------------------------------------------------------
All device types of backups  information stored on  Msdb database in  Backupmediafamily table.

To find disk backups:

Use Msdb
Select * from  backupmediafamily where device_type =2

Tape:

Use Msdb
Select * from backupmediafamily where device_type=5

Virtual device:

Use Msdb
Select * from backupmediafamily where device_type=7

 permanent backup device:
Use Msdb
Select * from Backupmediafamily where device_type=105

Types of  recovery models  in sql server:

3 types of recovery models in sql server those are:
1.Full recovery model
2.Bulk-logged recovery model
3.Simple recovery model.

1.Simple  recovery model:
In simple recovery model no log backups and automatically reclaims log space to keep space requirements small essentially eliminating the need to manage log space.

changes since the most recent backups are unprotected. In the event of a disaster,those changes must be redone. Can recover only to the end of a backup.

2.Bulk- logged recovery model:
Requires log backups An adjust of the full recover model that permits high-performance bulk copy operations.Reduces log space usage by bulk logging most bulk operations.

If the log is damaged or bulk -logged operations occurred since the most recent log backup,changes since that last backup must be redone.Other wise,no work is lost.

Can recover to the end of any backup.point-in-time recovery is not possible.

3.Full recovery model:
Requires log backups.No work is lost due to a lost or damaged data file can recover to an arbitrary point in time.

If the tail of the log is damaged,changes since most recent log backup must be redone.
Can recovery to a specific point in time assuming that your backups are complete up to that point.

How to find the recovery model in sql server :

select databasepropertyex('dbname','recovery')

or

select  name,recovery_model_desc,recovery_model from sys.databases


Backup under the simple recovery Model:

The simple recovery model provides the simplest form of the backups and restore.Backup is easy to manage because transaction log in never backed up.However,if the there are no log backups, a database can be restored only to the end of the most recent backups of the data.If a failure were to occur,updates that are made after the most recent backups of the data are lost.

For example:

Five full database backups exit,but only the most recent backup(t1,t2,t3,t4),taken at the time t5 has to be restored.restoring this backup returns the database to the t5 point in time.All later updates respresented by the t6 box are lost.we can restored database up to t5.


Backup under the Full recovery model:

The full recovery model uses log backups to prevent data loss in the broadest range of failures scenarios,and backing and restoring the transaction log(Log backups) is required.
By using log backups we can recover the database up to the point -in-time recovery.Assuming we can backup the active log after a disaster occurs,you can restore the database up to the point of failure without data loss.

For ex:
A database backup,Db_1 and two routine log backups,log_1 and log_2 have been taken.some time after the log_2 log backup,data loss occurs in the database.Before three backups are restored ,the admin must back up the active log(the tail of the log).The database admin then restores db_1,log_1,and log_2 without recovering the database.then the database admin restores and recovers the tail-log backups(Tail).This recovers the databases to the point of failure,recovering all the data.


Backup under the bulk-Logged recovery Model:

The bulk logged recovery model is special-purpose recovery model that should be used only intermittently to improve the performance of certain large scale bulk operations,such as bulk imports of large amount of data.Much of the description of backup under the full recovery model also applies to the bulk-logged recovery model.

The best practice is to switch to the bulk -logged recovery model right before a set of bulk operations,perform the operations, and then immediately switch back to the full recovery model.

the bulk -logged recovery model minimally logs bulk operations,although fully logging other transactions.The bulk-logged recover model protects against media failure and, fo bulk operations,provide the best performance and least log space usage.

However,the bulk-logged recovery model increases the risk of data loss for these bulk-copy operations,because bulk logging operations prevents recapturing changes on a transaction -by-transaction basis.If a log backup contains any bulk-logged operations,you cannot restore to a point-in-time within that log backup;you can restore only the whole log backup.

under the bulk-logged recover model,if a log backup covers any bulk operations,the log backup contains both log records and the data pages that were changed by bulk operations.This is necessary to capture the results of the bulk-logged operations.The incorporated data extents can make a log backup very large.Additionally,backing up the log requires access to the data files contain the bulk-logged transactions.If any affected databases file is in accessible,the transaction log cannot be backed up and all operations committed in that log are lost.

To track the data pages, A log backups operations relies on bulk-changes bitmap pages that contains a bit for every extent.For extent updated by bulk-logged operations,since the log backup,the bit is set to 1 in the bitmap.The data extent are copied into the log followed by the log data.


How to change the recovery model in sql server :

Alter database <database name>  Set recovery  Full/Bulk-logged/Simple.

To  find the database files, file groups and file  growth  in sql server:

--select * from sys.sysaltfiles

SELECT DB_NAME(database_id) AS DatabaseName,

CAST([Name] AS varchar(20)) AS NameofFile,

CAST(physical_name AS varchar(100)) AS PhysicalFile,

type_desc AS FileType,

((size * 8)/1024) AS FileSize,

MaxFileSize = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'

WHEN max_size = 0 THEN 'NO_GROWTH'

WHEN max_size <> -1 OR max_size <> 0 THEN CAST(((max_size * 8) / 1024) AS varchar(15))

ELSE 'Unknown'

END,

SpaceRemainingMB = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'

WHEN max_size <> -1 OR max_size = 268435456 THEN CAST((((max_size - size) * 8) / 1024) AS varchar(10))

ELSE 'Unknown'

END,

Growth = CASE WHEN growth = 0 THEN 'FIXED_SIZE'

WHEN growth > 0 THEN ((growth * 8)/1024)

ELSE 'Unknown'

END,

GrowthType = CASE WHEN is_percent_growth = 1 THEN 'PERCENTAGE'

WHEN is_percent_growth = 0 THEN 'MBs'

ELSE 'Unknown'

END

FROM master.sys.master_files

WHERE state = 0

AND type_desc IN ('ROWS','LOG')

ORDER BY database_id, file_id.

To find the databases  log files  space usage:

Dbcc sqlperf(logspace)

To find the data files space usage:

create table #dbsize
(Dbname sysname,
--dbstatus varchar(50),
--Recovery_Model varchar(40) default ('NA'),
file_Size_MB VArchar(3000),Space_Used_MB Varchar(2000),used_percentage Varchar(2000),Free_Space_MB Varchar(2000))

insert into #dbsize(Dbname,
--dbstatus,Recovery_Model,
file_Size_MB,Space_Used_MB,Free_Space_MB,used_percentage)
exec sp_msforeachdb
'use [?];
  select DB_NAME() AS DbName,
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
--sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 *100 as used,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB ,
(sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 )*100/((sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0)+(SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0))AS used_percentage from sys.database_files  where type=0 group by type'

select * from #dbsize

drop table #dbsize

Types of Physical database files and file groups of SQL Server databases

Database Files:

SQL Server  databases have three types of files:

· Primary data files:

The primary data file is the starting point of the database and points to the Page1 other files in the database.

Every database has one primary data file.

The recommended file name extension for primary data files is .mdf.

· Secondary data files:

Secondary data files are the data files.

A database can have one or more data files.

Secondary data files are other than primary data file.

The recommended file name extension for secondary data files is .ndf.

· Log files

Log files hold all the log information that is used to recover the database.

There must be at least one log file for each database, although there can be

more than one.

 The locations of all the files in a database are recorded in the primary file of the database and in the master database.

The Database Engine uses the file location information from the master database most of the time.

Logical and Physical File Names

They are:

1.logical_file_name2.Physical file name (os_file_name)

Logical_file_name:

The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements.

Physical file name (os_file_name)

The os_file_name is the name of the physical file including the directory path.

SQL Server data and log files can be put on either FAT or NTFS file systems. NTFS is recommended for the security aspects of NTFS.

When multiple instances of SQL Server are run on a single computer, each instance receives a different default directory to hold the files for the databases created in the instance.

EX:

How SQL Server writes data to disk

The transaction log is a serial record of all modifications that have occurred in the database. SQL Server writes changes to the log before it writes changes to the actual data file.

SQL Server Arch Diagram

SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification must be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.

 At the time a modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.

When users change data, SQL Server doesn't write that change directly to the data. Rather, SQL Server locates the appropriate data and then loads it into a special area of RAM called the data cache. Changes are made in RAM. Then, SQL Server copies changes waiting in RAM to the transaction log. Only then does SQL Server write changes to the actual data file.

This is called a write-ahead log because SQL Server writes changes to the log before it writes changes to the actual data file.

SQL Server Architecture:

1.Physical database architecture:

2.Relational Database Engine Architecture

1.Physical database architecture Contains:

•Page & Extents

•Physical Database Files & File Groups

•Space Allocation & Reuse

•Table & Index Architecture

•Transaction Log Architecture

Physical Database Architecture
Page & Extents

•Page fundamental unit of storage.

•Page size is 8 KB (128 Pages per MB).

•96 byes for Page Header,8060 for Actual Data & Offset &  rest 36 for SLOT ARRAY.

•For VARCHAR, NVARCHAR, VARBINARY or SQL_VARIANT data types when total row size of all fixed & variable columns in a table exceeds 8060 bytes (Insert or Update) , SQL server dynamically move one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit. 

•When a column is moved to ROW_OVERFLOW_DATA allocation unit, a 24 byte pointer on the Original page in the IN_ROW_DATA allocation unit is maintained. If a subsequent operations reduce the row size, SQL Server dynamically move the column back to original data page.

Types of Pages:

EXtents:

•Extents, basic unit in which space is managed.

•Extent is of 8 Physical pages (8 KB page x 8 pages=64 KB). Total 16 Extents per MB.

•To efficiently utilize space, SQL Server does not allocate whole extent to a table. SQL has 2 type of extents:

•Uniform Extents, owned by single object, all 8 pages in the extent can only be used by owning object.

•Mixed Extents, are shared by up to 8 objects. Each of 8 pages in the extent can be owned by different objects.

An new table or index is generally allocated pages from Mixed Extents. When the table or index grows to the point that it has 8 pages, it then switches to use Uniform Extents for subsequent allocations.If we create an index on existing table that has enough rows to generate 8 pages in the index,all allocations to the index are in Uniform Extents.