Blog Post

Time Pass With Transaction Log - Part 4 DDL

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):

 

SPidCurrent LSNOperationContextAllocUnitNamePage IDSlot IDDescription
510000001a:00000033:0005LOP_BEGIN_XACTLCX_NULLNULLNULLNULLSPID of the user who fired this transaction
NULL0000001a:00000033:0006LOP_LOCK_XACTLCX_NULLNULLNULLNULLACQUIRE_LOCK_SCH_M on Table
NULL0000001a:00000033:0007LOP_INSERT_ROWSLCX_CLUSTEREDsys.sysschobjs.clst0001:0000007453INSERT a row in Clustered index of sys.sysschobjs
NULL0000001a:00000033:0008LOP_INSERT_ROWSLCX_INDEX_LEAFsys.sysschobjs.nc10001:000000760INSERT a row in NonClustered index of sys.sysschobjs
NULL0000001a:00000033:0009LOP_INSERT_ROWSLCX_INDEX_LEAFsys.sysschobjs.nc20001:000000780INSERT a row in NonClustered index of sys.sysschobjs
NULL0000001a:00000033:000aLOP_INSERT_ROWSLCX_INDEX_LEAFsys.sysschobjs.nc30001:0000007a50INSERT a row in NonClustered index of sys.sysschobjs
NULL0000001a:00000033:000bLOP_MODIFY_ROWLCX_CLUSTEREDsys.sysschobjs.clst0001:0000007453UPDATE a row in Clustered index of sys.sysschobjs
NULL0000001a:00000033:000cLOP_INSERT_ROWSLCX_CLUSTEREDsys.syscolpars.clst0001:0000005550INSERT a row in Clustered index of sys.syscolpars
NULL0000001a:00000033:000dLOP_INSERT_ROWSLCX_INDEX_LEAFsys.syscolpars.nc0001:0000006d101INSERT a row in NonClustered index of sys.syscolpars
NULL0000001a:00000033:000eLOP_INSERT_ROWSLCX_CLUSTEREDsys.syscolpars.clst0001:0000005551INSERT a row in Clustered index of sys.syscolpars
NULL0000001a:00000033:000fLOP_INSERT_ROWSLCX_INDEX_LEAFsys.syscolpars.nc0001:0000003747INSERT a row in NonClustered index of sys.syscolpars
NULL0000001a:00000033:0010LOP_INSERT_ROWSLCX_CLUSTEREDsys.syscolpars.clst0001:0000005552INSERT a row in Clustered index of sys.syscolpars
NULL0000001a:00000033:0011LOP_INSERT_ROWSLCX_INDEX_LEAFsys.syscolpars.nc0001:00000037113INSERT a row in NonClustered index of sys.syscolpars
NULL0000001a:00000033:0012LOP_MODIFY_ROWLCX_CLUSTEREDsys.sysschobjs.clst0001:0000007453UPDATE a row in Clustered index of sys.sysschobjs
NULL0000001a:00000033:0013LOP_INSERT_ROWSLCX_CLUSTEREDsys.sysrowsets.clust0001:0000001291INSERT a row in Clustered index of sys.sysrowsets
NULL0000001a:00000033:0014LOP_LOCK_XACTLCX_NULLNULLNULLNULLACQUIRE_LOCK_SCH_M on Table
NULL0000001a:00000033:0016LOP_INSERT_ROWSLCX_CLUSTEREDsys.sysallocunits.clust0001:0000008252INSERT a row in Clustered index of sys.sysallocunits
NULL0000001a:00000033:0017LOP_INSERT_ROWSLCX_INDEX_LEAFsys.sysallocunits.nc0001:00000035103INSERT a row in NonClustered index of sys.sysallocunits
NULL0000001a:00000033:0018LOP_INSERT_ROWSLCX_CLUSTEREDsys.sysrscols.clst0001:00000033102INSERT a row in Clustered index of sys.sysrscols
NULL0000001a:00000033:0019LOP_INSERT_ROWSLCX_CLUSTEREDsys.sysrscols.clst0001:00000033103INSERT a row in Clustered index of sys.sysrscols
NULL0000001a:00000033:001aLOP_INSERT_ROWSLCX_CLUSTEREDsys.sysrscols.clst0001:00000033104INSERT a row in Clustered index of sys.sysrscols
NULL0000001a:00000033:001bLOP_HOBT_DDLLCX_NULLNULLNULLNULL 
NULL0000001a:00000033:001cLOP_INSERT_ROWSLCX_CLUSTEREDsys.sysidxstats.clst0001:0000007c107INSERT a row in Clustered index of sys.sysidxstats
NULL0000001a:00000033:001dLOP_INSERT_ROWSLCX_INDEX_LEAFsys.sysidxstats.nc0001:0000007e1INSERT a row in NonClustered index of sys.sysidxstats
NULL0000001a:00000033:001eLOP_HOBT_DDLLCX_NULLNULLNULLNULL 
NULL0000001a:00000033:001fLOP_MODIFY_ROWLCX_CLUSTEREDsys.sysrowsets.clust0001:0000001291UPDATE a row in Clustered index of sys.sysrowsets
NULL0000001a:00000033:0020LOP_COMMIT_XACTLCX_NULLNULLNULLNULLCommit 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-LogSimilar Sys Views provided to Us
sys.sysschobjs sys.objects
sys.syscolpars sys.columns
sys.sysrowsetssys.partitions
sys.sysrscols   sys.columns?
sys.sysallocunitssys.allocation_units
sys.system_internals_allocation_units
sys.sysidxstatssys.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:

 

spidCurrent LSNOperationContextAllocUnitNamePage IDSlot ID
540000001a:000000c8:0001LOP_BEGIN_XACTLCX_NULLNULLNULLNULL
NULL0000001a:000000c8:0002LOP_LOCK_XACTLCX_NULLNULLNULLNULL
NULL0000001a:000000c8:0003LOP_HOBT_DDLLCX_NULLNULLNULLNULL
NULL0000001a:000000c8:0004LOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.sysrscols.clst0001:00000033104
NULL0000001a:000000c8:0007LOP_HOBT_DDLLCX_NULLNULLNULLNULL
NULL0000001a:000000c8:0008LOP_INSERT_ROWSLCX_CLUSTEREDsys.sysrscols.clst0001:00000033104
NULL0000001a:000000c8:000aLOP_MODIFY_ROWLCX_CLUSTEREDsys.sysschobjs.clst0001:0000007453
NULL0000001a:000000c8:000bLOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.syscolpars.nc0001:00000073171
NULL0000001a:000000c8:000dLOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.syscolpars.clst0001:0000005552
NULL0000001a:000000c8:000fLOP_LOCK_XACTLCX_NULLNULLNULLNULL
NULL0000001a:000000c8:0010LOP_COMMIT_XACTLCX_NULLNULLNULLNULL

 

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:

 

spidCurrent LSNOperationContextAllocUnitNamePage IDSlot ID
540000001a:000000cb:0006LOP_BEGIN_XACTLCX_NULLNULLNULLNULL
NULL0000001a:000000cb:0007LOP_LOCK_XACTLCX_NULLNULLNULLNULL
NULL0000001a:000000cb:0008LOP_HOBT_DDLLCX_NULLNULLNULLNULL
NULL0000001a:000000cb:0009LOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.sysrowsets.clust0001:0000001291
NULL0000001a:000000cb:000cLOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.sysrscols.clst0001:00000033102
NULL0000001a:000000cb:000eLOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.sysrscols.clst0001:00000033103
NULL0000001a:000000cb:000fLOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.sysrscols.clst0001:00000033104
NULL0000001a:000000cb:0010LOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.sysallocunits.nc0001:00000035103
NULL0000001a:000000cb:0012LOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.sysallocunits.clust0001:0000008252
NULL0000001a:000000cb:0014LOP_MODIFY_ROWLCX_CLUSTEREDsys.sysschobjs.clst0001:0000007453
NULL0000001a:000000cb:0015LOP_MODIFY_ROWLCX_CLUSTEREDsys.sysidxstats.clst0001:0000009688
NULL0000001a:000000cb:0016LOP_LOCK_XACTLCX_NULLNULLNULLNULL
NULL0000001a:000000cb:0017LOP_LOCK_XACTLCX_NULLNULLNULLNULL
NULL0000001a:000000cb:0018LOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.sysidxstats.nc0001:0000007e1
NULL0000001a:000000cb:001aLOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.sysidxstats.clst0001:0000009688
NULL0000001a:000000cb:001cLOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.syscolpars.nc0001:0000003773
NULL0000001a:000000cb:001eLOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.syscolpars.clst0001:0000005550
NULL0000001a:000000cb:0020LOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.syscolpars.nc0001:00000073171
NULL0000001a:000000cb:0022LOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.syscolpars.clst0001:0000005551
NULL0000001a:000000cb:0023LOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.sysschobjs.nc10001:000000763
NULL0000001a:000000cb:0025LOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.sysschobjs.nc20001:0000007853
NULL0000001a:000000cb:0027LOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.sysschobjs.nc30001:0000007a50
NULL0000001a:000000cb:0029LOP_DELETE_ROWSLCX_MARK_AS_GHOSTsys.sysschobjs.clst0001:0000007453
NULL0000001a:000000cb:002bLOP_COMMIT_XACTLCX_NULLNULLNULLNULL

 

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

 

Rate

Share

Share

Rate