http://www.sqlservercentral.com/blogs/livingforsqlserver/2012/11/26/time-pass-with-transaction-log-part-4-ddl/

Printed 2014/12/21 11:06AM

Time Pass With Transaction Log - Part 4 DDL

By Ramkumar (LivingForSQLServer), 2012/11/26

In this article,  We are going to CREATE/ ALTER/ DROP a table and observe transaction log records created for those DDL statements.

Our focus is to observe schema level impacts in lower level.

1. CREATE TABLE

Lets create a simple table named tEmployee in a sample database EXPLORE_TLOG

USE MASTER
GO

-- Drop this database if already exists
DROP DATABASE EXPLORE_TLOG
GO

-- Create a database named Explore_TLog
CREATE DATABASE EXPLORE_TLOG
GO

USE EXPLORE_TLOG
GO

-- Create table is covered with a transaction name CREATE_TABLE1

BEGIN TRAN CREATE_TABLE1
CREATE TABLE tEmployee(intRoll int, strName char(20), strDept char(20))
COMMIT TRAN CREATE_TABLE1
GO

Here is the query to fetch t-log records generated for the transaction name CREATE_TABLE1.

SELECT
[spid],
[Current LSN],
[Operation],
[Context],
[AllocUnitName],
[Page ID],
[Slot ID]
FROM fn_dblog(null,null)
WHERE [Transaction ID] IN(
 SELECT [Transaction ID] FROM fn_dblog(null,null)
 WHERE [Transaction Name] = 'CREATE_TABLE1')

Note:
1. In the above query, first column spid tells which spid fired this CREATE TABLE statemet. We will see its use in detail later
2. Above create table statement has generated 27 t-log records (below table).

CREATE TABLE t-log records - Output (read description column):

 

SPid Current LSN Operation Context AllocUnitName Page ID Slot ID Description
51 0000001a:00000033:0005 LOP_BEGIN_XACT LCX_NULL NULL NULL NULL SPID of the user who fired this transaction
NULL 0000001a:00000033:0006 LOP_LOCK_XACT LCX_NULL NULL NULL NULL ACQUIRE_LOCK_SCH_M on Table
NULL 0000001a:00000033:0007 LOP_INSERT_ROWS LCX_CLUSTERED sys.sysschobjs.clst 0001:00000074 53 INSERT a row in Clustered index of sys.sysschobjs
NULL 0000001a:00000033:0008 LOP_INSERT_ROWS LCX_INDEX_LEAF sys.sysschobjs.nc1 0001:00000076 0 INSERT a row in NonClustered index of sys.sysschobjs
NULL 0000001a:00000033:0009 LOP_INSERT_ROWS LCX_INDEX_LEAF sys.sysschobjs.nc2 0001:00000078 0 INSERT a row in NonClustered index of sys.sysschobjs
NULL 0000001a:00000033:000a LOP_INSERT_ROWS LCX_INDEX_LEAF sys.sysschobjs.nc3 0001:0000007a 50 INSERT a row in NonClustered index of sys.sysschobjs
NULL 0000001a:00000033:000b LOP_MODIFY_ROW LCX_CLUSTERED sys.sysschobjs.clst 0001:00000074 53 UPDATE a row in Clustered index of sys.sysschobjs
NULL 0000001a:00000033:000c LOP_INSERT_ROWS LCX_CLUSTERED sys.syscolpars.clst 0001:00000055 50 INSERT a row in Clustered index of sys.syscolpars
NULL 0000001a:00000033:000d LOP_INSERT_ROWS LCX_INDEX_LEAF sys.syscolpars.nc 0001:0000006d 101 INSERT a row in NonClustered index of sys.syscolpars
NULL 0000001a:00000033:000e LOP_INSERT_ROWS LCX_CLUSTERED sys.syscolpars.clst 0001:00000055 51 INSERT a row in Clustered index of sys.syscolpars
NULL 0000001a:00000033:000f LOP_INSERT_ROWS LCX_INDEX_LEAF sys.syscolpars.nc 0001:00000037 47 INSERT a row in NonClustered index of sys.syscolpars
NULL 0000001a:00000033:0010 LOP_INSERT_ROWS LCX_CLUSTERED sys.syscolpars.clst 0001:00000055 52 INSERT a row in Clustered index of sys.syscolpars
NULL 0000001a:00000033:0011 LOP_INSERT_ROWS LCX_INDEX_LEAF sys.syscolpars.nc 0001:00000037 113 INSERT a row in NonClustered index of sys.syscolpars
NULL 0000001a:00000033:0012 LOP_MODIFY_ROW LCX_CLUSTERED sys.sysschobjs.clst 0001:00000074 53 UPDATE a row in Clustered index of sys.sysschobjs
NULL 0000001a:00000033:0013 LOP_INSERT_ROWS LCX_CLUSTERED sys.sysrowsets.clust 0001:00000012 91 INSERT a row in Clustered index of sys.sysrowsets
NULL 0000001a:00000033:0014 LOP_LOCK_XACT LCX_NULL NULL NULL NULL ACQUIRE_LOCK_SCH_M on Table
NULL 0000001a:00000033:0016 LOP_INSERT_ROWS LCX_CLUSTERED sys.sysallocunits.clust 0001:00000082 52 INSERT a row in Clustered index of sys.sysallocunits
NULL 0000001a:00000033:0017 LOP_INSERT_ROWS LCX_INDEX_LEAF sys.sysallocunits.nc 0001:00000035 103 INSERT a row in NonClustered index of sys.sysallocunits
NULL 0000001a:00000033:0018 LOP_INSERT_ROWS LCX_CLUSTERED sys.sysrscols.clst 0001:00000033 102 INSERT a row in Clustered index of sys.sysrscols
NULL 0000001a:00000033:0019 LOP_INSERT_ROWS LCX_CLUSTERED sys.sysrscols.clst 0001:00000033 103 INSERT a row in Clustered index of sys.sysrscols
NULL 0000001a:00000033:001a LOP_INSERT_ROWS LCX_CLUSTERED sys.sysrscols.clst 0001:00000033 104 INSERT a row in Clustered index of sys.sysrscols
NULL 0000001a:00000033:001b LOP_HOBT_DDL LCX_NULL NULL NULL NULL  
NULL 0000001a:00000033:001c LOP_INSERT_ROWS LCX_CLUSTERED sys.sysidxstats.clst 0001:0000007c 107 INSERT a row in Clustered index of sys.sysidxstats
NULL 0000001a:00000033:001d LOP_INSERT_ROWS LCX_INDEX_LEAF sys.sysidxstats.nc 0001:0000007e 1 INSERT a row in NonClustered index of sys.sysidxstats
NULL 0000001a:00000033:001e LOP_HOBT_DDL LCX_NULL NULL NULL NULL  
NULL 0000001a:00000033:001f LOP_MODIFY_ROW LCX_CLUSTERED sys.sysrowsets.clust 0001:00000012 91 UPDATE a row in Clustered index of sys.sysrowsets
NULL 0000001a:00000033:0020 LOP_COMMIT_XACT LCX_NULL NULL NULL NULL Commit Transaction. Table is created

 

In the above output, you can see lots of system object names in AlloUnitName column.
as I mentioned in previous article, these objects are protected and you can't directly query them.
If you want to query them you have to login as DAC (ADMIN:<InstanceName>)

here is list of protected system objects and equivalent documeted system views provided to Us. 
Note: this is just a high level  similaritie observed by me. you can observe lots of difference in column count and naming convention etc.

 

Internal protected object name in T-Log Similar Sys Views provided to Us
sys.sysschobjs  sys.objects
sys.syscolpars  sys.columns
sys.sysrowsets sys.partitions
sys.sysrscols    sys.columns?
sys.sysallocunits sys.allocation_units
sys.system_internals_allocation_units
sys.sysidxstats sys.indexes

 

If you are new to querying system views, run two below queries and observe the relationship among the system views.

-- sys.objects and sys.columns
SELECT so.object_id, so.name ObjectName, sc.column_id, sc.name column_name
FROM sys.objects so
 inner join sys.columns sc on so.object_id = sc.object_id
where so.name = 'tEmployee'

-- Object - Partitions - Allocation Units
SELECT
so.name,
so.object_id,
si.type_desc,
sp.index_id,
sp.partition_id,
sp.hobt_id,
sa.container_id,
internals.total_pages,
internals.used_pages,
internals.data_pages,
first_page,
root_page,
first_iam_page
FROM sys.objects so
 inner join sys.indexes si on so.object_id = si.object_id
 inner join sys.partitions sp on so.object_id = sp.object_id
 inner join sys.allocation_units sa on sa.container_id = sp.hobt_id
 inner join sys.system_internals_allocation_units internals on internals.container_id = sa.container_id
WHERE so.object_id = object_id('temployee')

 

2. ALTER TABLE

Now lets drop a column in tExample table and observe t-log records generated.

BEGIN TRAN ALTER_TABLE1
ALTER TABLE tEmployee DROP Column strDept
COMMIT TRAN ALTER_TABLE1
GO

Now fetch t-log records generated for this transaction

SELECT
[spid],
[Current LSN],
[Operation],
[Context],
[AllocUnitName],
[Page ID],
[Slot ID]
FROM fn_dblog(null,null)
WHERE [Transaction ID] IN(
 SELECT [Transaction ID] FROM fn_dblog(null,null)
 WHERE [Transaction Name] = 'ALTER_TABLE1')

As you can expect, there will be an UPDATE in sys.sysschobjs and DELETE in sys.syscolpars

ALTER TABLE - output:

 

spid Current LSN Operation Context AllocUnitName Page ID Slot ID
54 0000001a:000000c8:0001 LOP_BEGIN_XACT LCX_NULL NULL NULL NULL
NULL 0000001a:000000c8:0002 LOP_LOCK_XACT LCX_NULL NULL NULL NULL
NULL 0000001a:000000c8:0003 LOP_HOBT_DDL LCX_NULL NULL NULL NULL
NULL 0000001a:000000c8:0004 LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.sysrscols.clst 0001:00000033 104
NULL 0000001a:000000c8:0007 LOP_HOBT_DDL LCX_NULL NULL NULL NULL
NULL 0000001a:000000c8:0008 LOP_INSERT_ROWS LCX_CLUSTERED sys.sysrscols.clst 0001:00000033 104
NULL 0000001a:000000c8:000a LOP_MODIFY_ROW LCX_CLUSTERED sys.sysschobjs.clst 0001:00000074 53
NULL 0000001a:000000c8:000b LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.syscolpars.nc 0001:00000073 171
NULL 0000001a:000000c8:000d LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.syscolpars.clst 0001:00000055 52
NULL 0000001a:000000c8:000f LOP_LOCK_XACT LCX_NULL NULL NULL NULL
NULL 0000001a:000000c8:0010 LOP_COMMIT_XACT LCX_NULL NULL NULL NULL

 

Observation:
1. Dropping a column in a simple user table makes little changes in records inserted for CREATE TABLE in system objects.

2. There is an MODIFY_ROW operation in sys.sysschobjs (sysobjects) and some MARK_AS_GHOST in column level system objects such as sys.sysrscols and sys.syscolpars (syscolumns).

3. LOCK_XACT  - Schema lock.

3. DROP TABLE

Lets drop this tEmployee table and observe t-log records generated by DROP TABLE statement

BEGIN TRAN DROP_TABLE1
DROP TABLE tEmployee
COMMIT TRAN DROP_TABLE1
GO

here is the query to list T-log records for DROP TABLE statement:


SELECT
[spid],
[Current LSN],
[Operation],
[Context],
[AllocUnitName],
[Page ID],
[Slot ID]
FROM fn_dblog(null,null)
WHERE [Transaction ID] IN(
 SELECT [Transaction ID] FROM fn_dblog(null,null)
 WHERE [Transaction Name] = 'DROP_TABLE1')

Output:

 

spid Current LSN Operation Context AllocUnitName Page ID Slot ID
54 0000001a:000000cb:0006 LOP_BEGIN_XACT LCX_NULL NULL NULL NULL
NULL 0000001a:000000cb:0007 LOP_LOCK_XACT LCX_NULL NULL NULL NULL
NULL 0000001a:000000cb:0008 LOP_HOBT_DDL LCX_NULL NULL NULL NULL
NULL 0000001a:000000cb:0009 LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.sysrowsets.clust 0001:00000012 91
NULL 0000001a:000000cb:000c LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.sysrscols.clst 0001:00000033 102
NULL 0000001a:000000cb:000e LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.sysrscols.clst 0001:00000033 103
NULL 0000001a:000000cb:000f LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.sysrscols.clst 0001:00000033 104
NULL 0000001a:000000cb:0010 LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.sysallocunits.nc 0001:00000035 103
NULL 0000001a:000000cb:0012 LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.sysallocunits.clust 0001:00000082 52
NULL 0000001a:000000cb:0014 LOP_MODIFY_ROW LCX_CLUSTERED sys.sysschobjs.clst 0001:00000074 53
NULL 0000001a:000000cb:0015 LOP_MODIFY_ROW LCX_CLUSTERED sys.sysidxstats.clst 0001:00000096 88
NULL 0000001a:000000cb:0016 LOP_LOCK_XACT LCX_NULL NULL NULL NULL
NULL 0000001a:000000cb:0017 LOP_LOCK_XACT LCX_NULL NULL NULL NULL
NULL 0000001a:000000cb:0018 LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.sysidxstats.nc 0001:0000007e 1
NULL 0000001a:000000cb:001a LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.sysidxstats.clst 0001:00000096 88
NULL 0000001a:000000cb:001c LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.syscolpars.nc 0001:00000037 73
NULL 0000001a:000000cb:001e LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.syscolpars.clst 0001:00000055 50
NULL 0000001a:000000cb:0020 LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.syscolpars.nc 0001:00000073 171
NULL 0000001a:000000cb:0022 LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.syscolpars.clst 0001:00000055 51
NULL 0000001a:000000cb:0023 LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.sysschobjs.nc1 0001:00000076 3
NULL 0000001a:000000cb:0025 LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.sysschobjs.nc2 0001:00000078 53
NULL 0000001a:000000cb:0027 LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.sysschobjs.nc3 0001:0000007a 50
NULL 0000001a:000000cb:0029 LOP_DELETE_ROWS LCX_MARK_AS_GHOST sys.sysschobjs.clst 0001:00000074 53
NULL 0000001a:000000cb:002b LOP_COMMIT_XACT LCX_NULL NULL NULL NULL

 

Observations:

1. As you can expect, DROP TABLE marks records as *GHOST* (delete) in some system objects which are INSERTed when a table is created.

2. Single delete in sys.sysschobjs performs 4 seperate deletes internally. one for deleting cluster index record and 3 deletes for 3 non cluster index records (for example: clst, nc1, nc2, nc3 etc)

3. LOCK_XACT - Schema level lock.

Summary:

This simple excercise helps us to be familiar with some protected system objects. you can expect few more system objects when you create a table with constraints such as Primary key and Foreign keys.

Reference:

Paul Randal

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.