Log Truncation

Demo on Log Truncation

-- Log Truncation
-- First, grow the log, after backing it up

Hi Today Iam Explaining on Log Truncation.

I have taken Adventureworks Database.

Change the recovery model of Adventureworks database to Full.

ALTER DATABASE AdventureWorksLT SET RECOVERY FULL;

Backup Database Adventureworks to disk = 'c\adv.bak'

Use Adventureworks

Check the log  properties by using DBCC loginfo

Take a screenshot and save it for verification.

Perform transactions in the db until most VLFs have status = 2

SELECT * INTO Orders FROM AdventureWorks.Sales.SalesOrderDetail;
DROP TABLE Orders;
GO 5
DBCC LOGINFO;

-- Notice that backing up the database does not clear the log
BACKUP DATABASE AdventureWorksLT
    TO DISK = 'C:\BACKUPS\AdventureWorksLT.bak' WITH INIT;
GO
DBCC LOGINFO;

-- However, backing up the transaction log does clear the log
BACKUP LOG AdventureWorksLT
    TO DISK = 'C:\BACKUPS\AdventureWorksLT_log.bak' WITH INIT;
GO
DBCC LOGINFO;

-- To truncate without backing up,
--  in SQL Server 2005, don't specify a file and use TRUNCATE_ONLY

SELECT * INTO Orders FROM AdventureWorks.Sales.SalesOrderDetail;
DROP TABLE Orders;
GO 5
DBCC LOGINFO;

-- This doesn't work in SQL Server 2008
BACKUP LOG AdventureWorksLT
    WITH TRUNCATE_ONLY;
GO