ADR Demo.sql

USE master
GO
ALTER DATABASE gocowboys SET ACCELERATED_DATABASE_RECOVERY = ON
BACKUP DATABASE gocowboys TO DISK=N'NUL'
BACKUP LOG gocowboys TO DISK=N'NUL'
GO

USE gocowboys
GO

DECLARE @now datetime
DROP TABLE IF EXISTS #vf
DROP TABLE IF EXISTS #tvf
SELECT * INTO #vf FROM sys.fn_virtualfilestats(DB_ID('gocowboys'), NULL)
SELECT * INTO #tvf FROM sys.fn_virtualfilestats(DB_ID('tempdb'), NULL)


BEGIN TRAN

SET @now = GETDATE()
DELETE FROM howboutthemcowboys
SELECT FORMAT(DATEDIFF(ms, @now, GETDATE()), '#,###0') AS delete_proc_time_ms

SELECT Operation, Context,COUNT(*) AS cnt 
FROM sys.fn_dblog(NULL, NULL) 
GROUP BY Operation, Context 
ORDER BY cnt DESC


-- What is the log space usage
SELECT 1 AS No, * FROM sys.dm_db_log_space_usage
SELECT FORMAT(COUNT(*), '#,###0') AS LogCount_01 FROM sys.fn_dblog(NULL, NULL)


CHECKPOINT

RAISERROR('Wait....', 10, 1) WITH NOWAIT
WAITFOR DELAY'00:00:15'
SELECT 2 AS No, * FROM sys.dm_db_log_space_usage
SELECT FORMAT(COUNT(*), '#,###0') LogCount_02 FROM sys.fn_dblog(NULL, NULL)

SET @now = GETDATE()
ROLLBACK TRAN
SELECT FORMAT(DATEDIFF(ms, @now, GETDATE()), '#,###0') AS rollback_proc_time_ms

SELECT 
	vf.NumberReads - #vf.NumberReads AS NumberReads,
	vf.BytesRead - #vf.BytesRead AS BytesRead,
	vf.NumberWrites - #vf.NumberWrites AS NumberWrites,
	vf.BytesWritten - #vf.BytesWritten AS BytesWritten
FROM sys.fn_virtualfilestats(DB_ID('gocowboys'), NULL) AS vf
LEFT JOIN #vf ON vf.DbId = #vf.DbId AND vf.FileId = #vf.FileId



SELECT 
	vf.NumberReads - #tvf.NumberReads AS NumberReads,
	vf.BytesRead - #tvf.BytesRead AS BytesRead,
	vf.NumberWrites - #tvf.NumberWrites AS NumberWrites,
	vf.BytesWritten - #tvf.BytesWritten AS BytesWritten
FROM sys.fn_virtualfilestats(DB_ID('tempdb'), NULL) AS vf
LEFT JOIN #tvf ON vf.DbId = #tvf.DbId AND vf.FileId = #tvf.FileId

GO

USE master
GO

Log Truncate.sql

BACKUP LOG gocowboys TO DISK=N'NUL'

SELECT name,log_reuse_wait, log_reuse_wait_desc FROM sys.databases WHERE database_id = DB_ID('gocowboys')