/*============================================================================
  File:     1_Examining_VLFs.sql

  Summary:  This script shows how to look at VLFs for a database.

  Date:     April 2017

  SQL Server Version: 2005/2008/2008R2/2012/2014/2016
------------------------------------------------------------------------------
  Written by Erin Stellato, SQLskills.com

  For more scripts and other useful content, go to:
	http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Erin Stellato.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

/*
	Start with fresh copy of AW
*/

/*
	Verify the recovery model
*/ 
USE [master];
GO

SELECT DATABASEPROPERTYEX('AdventureWorks2016', 'Recovery');
GO

SELECT [name], [recovery_model_desc]
FROM [sys].[databases]
WHERE [name] = 'AdventureWorks2016';
GO

/*
	check log file configuration
*/
USE [AdventureWorks2016];
GO

SELECT 
	[f].[name], 
	[f].[file_id], 
	[f].[type_desc], 
	[f].[physical_name], 
	[f].[size]*8/1024 [SizeMB],
	[f].[growth]*8/1024 [GrowthMB]
FROM [sys].[database_files] [f]
LEFT OUTER JOIN [sys].[filegroups] [fg] 
	ON [f].[data_space_id] = [fg].[data_space_id]
WHERE [f].[type_desc] = 'LOG';
GO

/*
	set file growth low
*/
USE [master];
GO

ALTER DATABASE [AdventureWorks2016] 
	MODIFY FILE ( NAME = N'AdventureWorks2016_Log', FILEGROWTH = 10%);
GO

/*
	Check VLFs
	*Use sys.dm_db_log_info in SQL Server 2017
*/
DBCC LOGINFO('AdventureWorks2016');
GO
--note number of VLFs:

/*
	Modify some data
*/
USE [AdventureWorks2016];
GO

UPDATE [Person].[Person] SET [FirstName] = 'Tony';
GO


/*
	Check VLFs again
*/
DBCC LOGINFO('AdventureWorks2016');
GO
--note number of VLFs:


/*
	Issue a checkpoint
*/
CHECKPOINT;
GO

/*
	Check VLFs again
*/
DBCC LOGINFO('AdventureWorks2016');
GO


/*
	Modify more data and then check VLFs
*/
USE [AdventureWorks2016];
GO

UPDATE [Person].[Person]
SET [LastName] = 'Stark'
WHERE [FirstName] = 'Tony';
GO

DBCC LOGINFO('AdventureWorks2016');
GO

UPDATE [Person].[Person]
SET [LastName] = 'Stark'
WHERE [FirstName] like 'T%';
GO

DBCC LOGINFO('AdventureWorks2016');
GO

UPDATE [Person].[Person]
SET [Title] = 'Mr.'
WHERE [LastName] IN ('Stark');
GO

DBCC LOGINFO('AdventureWorks2016');
GO


/*
	checkpoint one more time
*/
CHECKPOINT;
GO

DBCC LOGINFO('AdventureWorks2016');
GO

/*
	* reset AW database for next demo *
*/