question

Tim avatar image
Tim asked

DBCC SHRINKFILE Error

I am running dbcc shrinkfile (filename, targetsize) example [dbcc shrinkfile (adventureworks, 2500) and I get the following error. DBCC SHRINKFILE: Page 1:43775823 could not be moved because the partition to which it belonged was dropped. My question, what does the number 43775823 mean? Is this the page number? How do I find out what the page that can't be moved is related to? I need to get past this as I have 250 GB still free in this datafile. I created multiple file groups and have moved approx 800 GB out of the primary datafile. The shrinking operation has gone very smoothly up to this point. When I first came across this error I restarted the SQL Server Service, it didn't help, I then rebuilt all the indexes on the primary filegroup tables and that hasn't helped. The only article I have found on Google or Bing that addressed this, the guy deleted all indexes and was able to shrink the file, the then recreated his indexes. I have no problem doing this if I can determine what table is having the issue. Any help is appreciated. SQL 2005 SP3
sql-server-2005shrink-database
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image Tim commented ·
I ran DBCC CHECKFILEGROUP (1) WITH ALL_ERRORMSGS since the error on the shrink was happening on the data file in the primary filegroup. I get an error Msg 2576, Level 16, State1, Line 2, The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:42197353) in object ID 0, index ID -1, partition ID 0, alloc unit ID 4508440865406976 (type unknown), but it was not detected in the scan. CHECKFILEGROUP found 1 allocation errors and 0 consistency errors not associated with any single object. I am going to Google this but thought I would update here real quick. The DBCC PAGE command does not display an m_objId value, but does display a m_objId (AllocUnitId.idObj) with a value but searching sys.allocation_units does not get any results with the allocation_unit_id. Could this be an orphaned paged?
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
This is where I am not too sure. Corruption is a very specialised area, which takes quite a bit of digging to sort out. Can you restore a backup on a second system to see if the error is occuring there? If the error is not inside that backup, you may be able to use that to find the table that is broken. It may be prudent for you to swing over to the SSC forums and post on the corruption forum. Provide all DBCC outputs that you can. Paul may even be watching that forum, I'm not sure. You can also you twitter, I know that he is on there and will more than likely offer advice.
0 Likes 0 ·
Tim avatar image Tim commented ·
Since it is an allocation error (IAM) I was able to run DBCC CHECKALLOC (DBNAME, REPAIR_ALLOW_DATA_LOSS). It took less than 3 minutes to cleanup the allocation errors. I was able to verify that this allocation error is present in the production db as I was working with a restored copy working on a SQL 2005 upgrade. I appreciate everyones help. Paul Randal's blog on sqlskills.com got me where I needed as well as Williams direction.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
@TRAD - I just saw your answer and wanted to belatedly point out that REPAIR_ALLOW_DATA_LOSS does exactly is it says on the tin. You are basically telling the system to fix the problem, even if it means that it has to delete data from your table. I liken this to uninstalling a program from windows by formatting the hard-drive. It will uninstall the software, but can cause you to lose data.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@TRAD - you don't need to close questions when they get answered...
0 Likes 0 ·

1 Answer

·
WilliamD avatar image
WilliamD answered
Tim, 43775823 is the page number as you guessed. It is page 4775823 of file 1 in your database. Have you made sure that your database is in a consistant state? Have you run DBCC CHECKDB recently? I would make sure that this were the case before doing anything drastic. Once that is ok, then the following may be of use. ---------- ---------- First a word of warning: ***To do the following, you will need to use an undocumented DBCC command. DO THIS AT YOUR OWN RISK! A test system is really useful for this stuff!!*** ---------- ---------- Let me say that this command should not break anything and is used by Microsoft to troubleshoot and has been explained by the man who wrote it here: [Paul Randal (DBCC PAGE and DBCC IND)][1]<----- ***READ THIS SO YOU SEE WHAT I MEAN***. It will take the two numbers I mentioned above (File Number and Page Number) and display some information that you can then use to find the table that is "broken". As Paul states, you need to activate a trace to send the output of the command that follows it to your console instead of the error log: DBCC TRACEON (3604) You would then issue the DBCC command: DBCC PAGE ({YourDatabaseNameHere},{FileNumber},{PageNumber},{PrintOptions}) As an example (I take your database to be "TimsDatabase"): DBCC PAGE (TimsDatabase,1,43775823,0) This will find the information for file 1, page 43775823 of TimsDatabase and print just the header for that page. This should be enough to then return the `object_id` for the table in question. You will find this in the printout as "m_objId". You then take this number and run: SELECT OBJECT_NAME({Object_Id you just found}) You should then get the name of the table you are after. You can then turn off the trace option you previously set: DBCC TRACEOFF (3604) [1]: http://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-Using-DBCC-PAGE-and-DBCC-IND-to-find-out-if-page-splits-ever-roll-back.aspx
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+1, epic effort.
1 Like 1 ·
WilliamD avatar image WilliamD commented ·
@Matt - thanks muchly. I am *very* cautious when suggesting the undocumented stuff, although I see no reason to be too worried here, as long as he follows those steps.
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Agreed with Matt's comment. +1s all round!
0 Likes 0 ·
sp_lock avatar image sp_lock commented ·
+2 (if only) Top work... All the info in one place.
0 Likes 0 ·
Tim avatar image Tim commented ·
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.