February 8, 2011 at 1:43 pm
We are trying to remove a file that contains no user data in a filegroup and we're receiving an error.
USE [dbname]
GO
DBCC SHRINKFILE (N'filename' , EMPTYFILE)
GO
Getting this error: DBCC SHRINKFILE: Index Allocation Map (IAM) page 8:8 could not be moved. Msg 2555, Level 16, State 1, Line 1 Cannot move all contents of file "MSGRESULTS20100902_yip" to other places to complete the emptyfile operation. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
early google search returned a solution to run updateusage, no success
dbcc updateusage(dbname, 'tablename')
ran this query to get a list of objects in filegroup:
SELECT
FileGroup = FILEGROUP_NAME(a.data_space_id),
TableName = OBJECT_NAME(p.object_id),
IndexName = i.name
FROM sys.allocation_units a
INNER JOIN sys.partitions p ON a.container_id = CASE WHEN a.type in(1,3) THEN p.hobt_id ELSE p.partition_id END AND p.object_id > 1024
LEFT JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
ORDER BY FileGroup
FileGroupTableNameIndexName
MSGRESULTS_ARCHIVEMsgResults_20100902IDX_MsgResults_MsgId
MSGRESULTS_ARCHIVEMsgResults_20100902IDX_MsgResults_EID
MSGRESULTS20100902MsgResults_20100902IDX_MsgResults_ProcessDtTm (bad filegroup)
MSGRESULTS20100923MsgResults_20101207IDX_MsgResults_MsgId
tried to drop indexes on the table and then emptyfile again, no luck.
tweeted, Paul Randall replied with running DBCC PAGE
dbcc traceon(3604)
dbcc page ('dbname', 8,8,3)
result:
PAGE: (8:8)
BUFFER:
BUF @0x0000000CECFC9B80
bpage = 0x0000000CEC6F4000 bhash = 0x0000000000000000 bpageno = (8:8)
bdbid = 9 breferences = 3 bcputicks = 0
bsampleCount = 0 bUse1 = 48747 bstat = 0xc00009
blog = 0x159a2159 bnext = 0x0000000000000000
PAGE HEADER:
Page @0x0000000CEC6F4000
m_pageId = (8:8) m_headerVersion = 1 m_type = 10
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 1384 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594128629760
Metadata: PartitionId = 72057594137346048 Metadata: IndexId = 1
Metadata: ObjectId = 2099048 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 90 m_slotCnt = 2 m_freeCnt = 6
m_freeData = 8182 m_reservedCnt = 0 m_lsn = (23935:3430896:4)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 185208796
Allocation Status
GAM (8:2) = ALLOCATED SGAM (8:3) = ALLOCATED
PFS (8:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (8:6) = NOT CHANGED
ML (8:7) = NOT MIN_LOGGED
IAM: Header @0x00000018B222A064 Slot 0, Offset 96
sequenceNumber = 0 status = 0x0 objectId = 0
indexId = 0 page_count = 0 start_pg = (8:0)
IAM: Single Page Allocations @0x00000018B222A08E
Slot 0 = (0:0) Slot 1 = (0:0) Slot 2 = (0:0)
Slot 3 = (0:0) Slot 4 = (0:0) Slot 5 = (0:0)
Slot 6 = (0:0) Slot 7 = (0:0)
IAM: Extent Alloc Status Slot 1 @0x00000018B222A0C2
(8:0) - (8:1272) = NOT ALLOCATED
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Tried to make sense of it, no such luck, restored the database on another server. Ran DBCC Checkdb(dbname), no errors.
Still won't emptyfile.
Tried to remove the file
Msg 5042, Level 16, State 1, Line 1
The file 'MSGRESULTS' cannot be removed because it is not empty.
Ran DBCC CHECKALLOC
found this:
File 8. The number of extents = 2, used pages = 7, and reserved pages = 9.
File 8 (number of mixed extents = 1, mixed pages = 1).
Object ID 99, index ID 0, partition ID 0, alloc unit ID 6488064 (type System allocation data), index extents 1, pages 6, mixed extent pages 0.
Object ID 2099048, index ID 1, partition ID 72057594137346048, alloc unit ID 72057594128629760 (type LOB data), index extents 0, pages 1, mixed extent pages 1.
select * from sys.objects where object_id = '2099048'
nameobject_idprincipal_idschema_idparent_object_idtypetype_desccreate_datemodify_dateis_ms_shippedis_publishedis_schema_published
MsgResults_201009022099048NULL10U USER_TABLE2010-09-02 18:36:09.9472011-02-03 18:43:38.093000
looked at table properties, this FG was a text filegroup, decided to copy data from this table to a new table in the correct filegroup, once copied, dropped the table that referenced the text filegroup and then was able to drop the file and the filegroup that was giving me problems.
Please help me out here, what should be the correct path to solving this issue. We have a DBCC CHECKDB process that runs nightly, how can we avoid these issues in the future, platform is SQL 2008 R2 CU2.
Thank you.
February 8, 2011 at 1:52 pm
That's a known issue that can occur sometimes with non-IN_ROW_DATA allocation units. Your workaround is the only solution I know of.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 8, 2011 at 2:34 pm
Thank you very much sir.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply