Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
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')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.