Saturday, 24 August 2013

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);