if2rgdvj

1SET NOCOUNT ON;
2USE master;
3GO
4IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'TestVLFSeq')
5BEGIN
6    ALTER DATABASE TestVLFSeq SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
7    DROP DATABASE TestVLFSeq;
8END
9GO
10CREATE DATABASE TestVLFSeq
11ON (NAME = 'system', FILENAME = 'C:tempTestVLFSeq.mdf', SIZE = 10MB, FILEGROWTH = 10MB, MAXSIZE = 100MB)
12LOG ON (NAME = 'log', FILENAME = 'C:tempTestVLFSeq.ldf', SIZE = 1MB, FILEGROWTH = 1MB, MAXSIZE = 10MB);
13GO
14
15ALTER DATABASE TestVLFSeq SET RECOVERY FULL;
16BACKUP DATABASE TestVLFSeq TO DISK = 'NUL:';
17GO
18
19SELECT d.name
20    , d.recovery_model_desc
21FROM sys.databases d
22WHERE d.name = 'TestVLFSeq';
23GO
24	
25USE TestVLFSeq;
26GO
27
28CREATE TABLE dbo.TestData
29(
30    someVal varchar(8000) NOT NULL
31        CONSTRAINT DF_TestData
32        DEFAULT ((CRYPT_GEN_RANDOM(8000)))
33);
34GO
35
36INSERT INTO dbo.TestData DEFAULT VALUES
37 /* insert 64 rows - ~512 KB of log usage */
38GO 64
39
40DBCC LOGINFO
41GO
42	
43USE TestVLFSeq;
44GO
45
46CREATE TABLE dbo.TestData
47(
48    someVal varchar(8000) NOT NULL
49        CONSTRAINT DF_TestData
50        DEFAULT ((CRYPT_GEN_RANDOM(8000)))
51);
52GO
53
54INSERT INTO dbo.TestData DEFAULT VALUES
55 /* insert 64 rows - ~512 KB of log usage */
56GO 64
57
58DBCC LOGINFO
59GO
60	
61USE TestVLFSeq;
62BEGIN TRANSACTION
63INSERT INTO dbo.TestData DEFAULT VALUES
64GO
65	
66USE TestVLFSeq;
67BEGIN TRANSACTION
68INSERT INTO dbo.TestData DEFAULT VALUES
69GO
70	
71BACKUP DATABASE TestVLFSeq TO DISK = 'NUL:' WITH INIT;
72BACKUP LOG TestVLFSeq TO DISK = 'NUL:' WITH INIT;
73GO
74	
75DBCC LOGINFO
76	
77INSERT INTO dbo.TestData DEFAULT VALUES
78 /* insert 64 rows - ~512 KB of log usage */
79GO 64
80
81DBCC LOGINFO
82GO
83	
84FSeqNo
85	
86DECLARE @cmd nvarchar(max);
87IF OBJECT_ID(N'tempdb..#loginfo', N'U') IS NOT NULL
88DROP TABLE #loginfo;
89CREATE TABLE #loginfo
90(
91    RecoveryUnitID int
92    , FileID int
93    , FileSize int
94    , StartOffset bigint
95    , FSeqNo int
96    , [Status] tinyint
97    , Parity tinyint
98    , CreateLSN bigint
99);
100SET @cmd = 'DBCC LOGINFO;';
101INSERT INTO #loginfo
102EXEC sys.sp_executesql @cmd;
103SELECT *
104FROM #loginfo li
105ORDER BY /* put "0" FSeqNo rows at the end */
106    CASE 
107        WHEN li.FSeqNo = 0 THEN CONVERT(int, POWER(CONVERT(bigint,2),31) - 1) 
108        ELSE li.FSeqNo 
109    END;