人人都是 DBA（VI）SQL Server 事务日志

CREATE TABLE [dbo].[Customer](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    [Address] [nvarchar](max) NULL,
    [Phone] [nvarchar](256) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Customer]
           ([Name]
           ,[Address]
           ,[Phone])
     VALUES
           ('Dennis Gao'
           ,'Beijing Haidian'
           ,'88888888')

DBCC LOG([TEST])

SELECT [Current LSN]
    ,[Operation]
    ,[Context]
    ,[Transaction ID]
    ,[Log Record Length]
    ,[Previous LSN]
    ,[AllocUnitId]
    ,[AllocUnitName]
    ,[Page ID]
    ,[Slot ID]
    ,[Xact ID]
FROM sys.fn_dblog(NULL, NULL)

dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

DBCC TRACEON(3604, -1)
GO

DBCC PAGE([TEST], 1, 120, 3)  
GO

PAGE: (1:120)


BUFFER:


BUF @0x000000027D15AC80

bpage = 0x000000026B6BA000          bhash = 0x0000000000000000          bpageno = (1:120)
bdbid = 7                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 8595                        bstat = 0x10b
blog = 0x1215accc                   bnext = 0x0000000000000000          

PAGE HEADER:


Page @0x000000026B6BA000

m_pageId = (1:120)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 87    m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594043629568                                
Metadata: PartitionId = 72057594039107584                                Metadata: IndexId = 0
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 12                        m_slotCnt = 1                       m_freeCnt = 8005
m_freeData = 185                    m_reservedCnt = 0                   m_lsn = (33:460:24)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                        DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 89

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 89                    
Memory Dump @0x000000000BE5A060

0000000000000000:   30000c00 01000000 00000000 04000003 002b0049  0................+.I
0000000000000014:   00590044 0065006e 006e0069 00730020 00470061  .Y.D.e.n.n.i.s. .G.a
0000000000000028:   006f0042 00650069 006a0069 006e0067 00200048  .o.B.e.i.j.i.n.g. .H
000000000000003C:   00610069 00640069 0061006e 00380038 00380038  .a.i.d.i.a.n.8.8.8.8
0000000000000050:   00380038 00380038 00                          .8.8.8.8.

Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8

Id = 1                              

Slot 0 Column 2 Offset 0x17 Length 20 Length (physical) 20

Name = Dennis Gao                   

Address = [BLOB Inline Data] Slot 0 Column 3 Offset 0x2b Length 30 Length (physical) 30

000000000BE4FC70:   42006500 69006a00 69006e00 67002000 48006100  B.e.i.j.i.n.g. .H.a.
000000000BE4FC84:   69006400 69006100 6e00                        i.d.i.a.n.

Slot 0 Column 4 Offset 0x49 Length 16 Length (physical) 16

Phone = 88888888   

CREATE TABLE #VLFInfo (
    RecoveryUnitID INT
    ,FileID INT
    ,FileSize BIGINT
    ,StartOffset BIGINT
    ,FSeqNo BIGINT
    ,[Status] BIGINT
    ,Parity BIGINT
    ,CreateLSN NUMERIC(38)
    );

CREATE TABLE #VLFCountResults (
    DatabaseName SYSNAME
    ,VLFCount INT
    );

EXEC sp_MSforeachdb N'Use [?]; 

                INSERT INTO #VLFInfo 
                EXEC sp_executesql N''DBCC LOGINFO([?])''; 
     
                INSERT INTO #VLFCountResults 
                SELECT DB_NAME(), COUNT(*) 
                FROM #VLFInfo; 

                TRUNCATE TABLE #VLFInfo;'

SELECT DatabaseName
    ,VLFCount
FROM #VLFCountResults
ORDER BY VLFCount DESC;

DROP TABLE #VLFInfo;

DROP TABLE #VLFCountResults;

DBCC LOGINFO

SELECT * FROM sys.database_recovery_status;

DBCC SQLPERF('logspace')

SELECT instance_name AS [Database]
    ,cntr_value AS [LogFull(%)]
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Percent Log Used%'
    AND instance_name NOT IN (
        '_Total'
        ,'mssqlsystemresource'
        )
    AND cntr_value > 0
ORDER BY [LogFull(%)] DESC;

DBCC SHRINKDATABASE 
( database_name | database_id | 0 
     [ , target_percent ] 
     [ , { NOTRUNCATE | TRUNCATEONLY } ] 
)
[ WITH NO_INFOMSGS ]

DBCC SHRINKFILE 
(
    { file_name | file_id } 
    { [ , EMPTYFILE ] 
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
[ WITH NO_INFOMSGS ]