0

I made a partitioned table with range left func. I then insert some rows into just one partition. Result:

select partition_id, partition_number, rows from sys.partitions where object_id = object_id('ptest')

result:

partition_id      partition_number rows
72057594464436224 1            8000
72057594464501760 2            0
72057594464567296 3            0
72057594464632832 4            0

then I update some rows of this table and query sys.dm_tran_locks, then I find some RID Locks on partition4 like this:

select * from sys.dm_tran_locks where resource_type = 'RID'

Result like this:

Resource_type Resource_description    
RID 3:13:192
RID 1:153496:257

When I use dbcc page to verify what page 3:13 contains, I get nothing:

dbcc traceon(3604)
dbcc page(db, 3, 13, 3)

Result like this:

PAGE: (3:13)


BUFFER:


BUF @0x03E98BCC

bpage = 0x5A622000                   bhash = 0x00000000                   bpageno = (3:13)
bdbid = 11                           breferences = 0                      bUse1 = 38298
bstat = 0x1c0010b                    blog = 0x79797979                    bnext = 0x00000000

PAGE HEADER:


Page @0x5A622000

m_pageId = (3:13)                    m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0xa008
m_objId (AllocUnitId.idObj) = 6973   m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594494910464                                 
Metadata: PartitionId = 72057594464501760                                 Metadata: IndexId = 0
Metadata: ObjectId = 1967398128      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 12                         m_slotCnt = 1                        m_freeCnt = 8094
m_freeData = 5867                    m_reservedCnt = 0                    m_lsn = (25294:376:199)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = -252731581              

Allocation Status

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

So, dbcc page confirms that there is nothing in page 3:13. There comes my question, why are RID locks exists on File 3:13 while nothing exists there?

Lost_Painting
  • 670
  • 6
  • 5
chenwq
  • 123
  • 1
  • 11
  • Or， question can also be like this: **Why SQL Server allocate pages on a completely empty partition？** – chenwq Jul 20 '11 at 01:53
  • Can you post your partition function too? – JNK Jul 20 '11 at 01:59
  • @JNK: `CREATE PARTITION FUNCTION PFUNC (INT) AS RANGE LEFT FOR VALUES ( 1, 10000, 20000); GO` – chenwq Jul 20 '11 at 03:04
  • A nomal Field. It's a heap table has two columns. `CREATE TABLE TEST(ID INT, _SEQNO INT IDENTITY(1, 1) ON PSCHEMA(ID)` – chenwq Jul 21 '11 at 04:09

1 Answers1

0

what's your " I update some rows of this table" T-SQL? Maybe your update T-SQL made it be locked . There isn't "nothing" here , there are still pages in the empty partition but no data

Lost_Painting
  • 670
  • 6
  • 5
  • I use this `Update table set ID = ID + 1`. I wantna know why there are empty pages in empty partition? – chenwq Jul 20 '11 at 03:02
  • **I have 4 partitions** and **DBCC IND** shows there is only pages in partition 1 which have actual data rows and in partition 4 which is the last partion and have 0 rows in it. – chenwq Jul 20 '11 at 03:06