--Create demo database
use master
if exists (select 1 from sys.databases where name = 'Mr_T_log')
begin
	alter database Mr_T_log set offline with rollback immediate;
	alter database Mr_t_log set online;
	drop database Mr_T_log;
end

CREATE DATABASE Mr_T_log
ON PRIMARY
(name=Mr_T_log_data01,
 filename='C:\DBFiles\MSSQL11.SQL2012\MSSQL\DATA\mr_t_log_data01.mdf',
 size=500MB,
 filegrowth=100MB,
 maxsize=UNLIMITED)
log on
(name=Mr_T_log_log01,
 filename='C:\DBFiles\MSSQL11.SQL2012\MSSQL\log\mr_t_log_log01.ldf',
 size=15MB,
 filegrowth=0MB,
 maxsize=15MB)

--Get initial full backup
BACKUP DATABASE Mr_T_log
to DISK='C:\DBFiles\Mr_T_log_FULL.bak'
WITH INIT;

use Mr_T_log
--Let's look at the log
dbcc loginfo;
select * from fn_dblog(null,null);

--Create a table to play with
CREATE TABLE da
(id int not null identity (1,1)
,foo char(8000));
go

create clustered index ci_da
on da(id);
go

--Create data insert stored procedure
CREATE PROCEDURE foo_insertdata @rowcount int
as
begin
	WITH n(rownum) AS
	(
		SELECT
			ROW_NUMBER() OVER (ORDER BY s1.[object_id]) rownum
		FROM       sys.objects AS s1
		CROSS JOIN sys.objects AS s2
		CROSS JOIN sys.objects AS s3
	)
	INSERT INTO da(foo)
	SELECT top(@rowcount) 'pity'
	FROM n
end
go

--Get a log backup
BACKUP LOG Mr_T_log
to DISK='C:\DBFiles\Mr_T_log_LOG01.bak'
WITH INIT;

--Insert 1000 rows
exec foo_insertdata 1000

--What does the log look like now?
dbcc loginfo

--One last look at fn_dblog, it's overkill for what we're doing
select 
	[Current LSN]
	,Operation
	,[Transaction Name]
	,[Begin Time]
	,[End Time]
	,[Checkpoint Begin]
	,[Checkpoint End]
	,[Log Record Length]
from fn_dblog(DEFAULT,DEFAULT)
order by [Current LSN];

--Insert 1000 rows again
exec foo_insertdata 1000

--Uh oh (sev 17), what are we waiting on?
select name,log_reuse_wait_desc,recovery_model_desc
from sys.databases
where name = 'Mr_T_log'

--Backup the log again
BACKUP LOG Mr_T_log
to DISK='C:\DBFiles\Mr_T_log_LOG01.bak'
WITH INIT;

checkpoint;

select name,log_reuse_wait_desc,recovery_model_desc
from sys.databases
where name = 'Mr_T_log'

--And log info again
dbcc loginfo

--What if we insert more data?
exec foo_insertdata 1000

dbcc loginfo

--All of our stuff got emptied out 
--and the log file is round robin-ing VLFs.

--Let's go ahead and allow our log file to grow out
BACKUP LOG Mr_T_log
to DISK='C:\DBFiles\Mr_T_log_LOG01.bak'
checkpoint;

ALTER DATABASE Mr_T_log
MODIFY FILE (name=Mr_T_log_log01
			,filegrowth=1MB
			,maxsize=UNLIMITED);

--Nowwhat if someone else inserts data before our log backup
--(Run queries 2 and 3, separate windows)

dbcc loginfo

--Back that log up!
BACKUP LOG Mr_T_log
to DISK='C:\DBFiles\Mr_T_log_LOG01.bak'
WITH INIT;
checkpoint;

dbcc loginfo

--Uh oh, still a lot of active VLFs (status 2)
dbcc opentran

SELECT
       tat.transaction_id
       ,tat.name
       ,st.session_id
       ,tat.transaction_begin_time
       ,case transaction_type
              when 1 then 'Read/Write TXN'
              when 2 then 'Read TXN'
              when 3 then 'System TXN'
              when 4 then 'Distributed TXN'
       end txn_type
       ,transaction_state
FROM sys.dm_tran_active_transactions tat
       INNER JOIN sys.dm_tran_session_transactions st ON tat.transaction_id = st.transaction_id
order by
       transaction_begin_time

--And let's look at what the database is waiting on
select name,log_reuse_wait_desc,recovery_model_desc
from sys.databases
where name = 'Mr_T_log'

--We have to wait for the transactions to finish
--before SQL Server will truncate their VLFs
--Go to other windows and rollback/commit
checkpoint;
--Let's look at the log status first
dbcc loginfo
select name,log_reuse_wait_desc,recovery_model_desc
from sys.databases
where name = 'Mr_T_log'

--They're still active, even though committed/rolled back
--Run a log backup
BACKUP LOG Mr_T_log
to DISK='C:\DBFiles\Mr_T_log_LOG01.bak'
WITH INIT;

checkpoint;

dbcc loginfo

select name,log_reuse_wait_desc,recovery_model_desc
from sys.databases
where name = 'Mr_T_log'

--What happens when we try to shrink?
dbcc shrinkfile(Mr_T_log_log01,15)

select name,type_desc,size/128.0 FileSizeMB
from sys.database_files

--We can only shrink down to our last active VLF
--Backup log to force log truncation (might have to do this twice)
BACKUP LOG Mr_T_log
to DISK='C:\DBFiles\Mr_T_log_LOG01.bak'
WITH INIT;

dbcc loginfo

dbcc shrinkfile(Mr_T_log_log01,15)

select name,type_desc,size/128.0 FileSizeMB
from sys.database_files
