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
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?
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.
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.
@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.
@TRAD - you don't need to close questions when they get 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
This explains my issue completely.
http://www.sqlskills.com/BLOGS/PAUL/post/IAM-page-corruption-examples.aspx
