To check Step by step Working of Transaction Log

CREATE DATABASE EmployeeDB

ON

(

  NAME = EmployeeDB_dat,

  FILENAME = 'C:\Named Instance\MSSQL10_50.NAMED\MSSQL\DATA\EmployeeDb.mdf'

)

LOG ON

(

  NAME = EmployeeDB_log,

  FILENAME = 'C:\Named Instance\MSSQL10_50.NAMED\MSSQL\DATA\EmployeeDb.ldf'

);

……………………………………………

USE EmployeeDB;

IF OBJECT_ID ('Employees', 'U') IS NOT NULL

DROP TABLE dbo.Employees;

SELECT BusinessEntityID,

  FirstName,

  LastName,

  JobTitle,

  PhoneNumber,

  EmailAddress,

  AddressLine1,

  AddressLine2,

  City,

  StateProvinceName,

  PostalCode,

  CountryRegionName

INTO dbo.Employees

FROM AdventureWorks2008.HumanResources.vEmployee;

…………………………………….

USE master;

ALTER DATABASE EmployeeDB

SET RECOVERY FULL;

……………………………………….

USE EmployeeDB;

SELECT name,

  size, -- in 8-KB pages

  max_size, -- in 8-KB pages

  growth,

  is_percent_growth

FROM sys.database_files

WHERE type_desc = 'LOG'

……………………………………..

DBCC SQLPERF(LOGSPACE);

………………………………………….

BACKUP DATABASE EmployeeDB

TO DISK = 'C:\Backup\EmployeeDB_dat.bak';

…………………………………………………..

USE EmployeeDB;

UPDATE Employees

SET JobTitle = 'To be determined';

UPDATE Employees

SET CountryRegionName = 'US'

WHERE CountryRegionName = 'United States';

DELETE Employees

WHERE BusinessEntityID > 5;

……………………………………………………..

BACKUP LOG EmployeeDB

TO DISK = 'C:\Backup\EmployeeDB_log.bak';

…………………………………………………………

ALTER DATABASE EmployeeDB

MODIFY FILE

(

    NAME = EmployeeDB_log,

    SIZE = 2MB,

    MAXSIZE = 200MB,

    FILEGROWTH = 10MB

);

…………………………………………

DBCC SQLPERF(LOGSPACE);

………………………………………………….

DBCC SHRINKFILE (EmployeeDB_log, 1);

……………………………………….

ALTER DATABASE EmployeeDB

ADD LOG FILE

(

    NAME = EmployeeDB_log2,

    FILENAME = 'C:\Named Instance\MSSQL10_50.NAMED\MSSQL\DATA\EmployeeDB2.ldf',

    SIZE = 2MB,

    MAXSIZE = 50MB,

    FILEGROWTH = 10%

);

…………………………………………

DBCC SHRINKFILE (EmployeeDB_log, 1);

……………………………………….

ALTER DATABASE EmployeeDB

REMOVE FILE EmployeeDB_log2;

……………………………………………………………

DBCC SQLPERF(LOGSPACE);