x

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
more ▼

asked Sep 06, 2010 at 11:50 AM in Default

Tim gravatar image

Tim
36.4k 35 41 139

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?
Sep 06, 2010 at 03:52 PM Tim

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.
Sep 07, 2010 at 12:35 AM WilliamD
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.
Sep 07, 2010 at 12:41 PM Tim

@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.
Sep 14, 2010 at 11:54 PM WilliamD
@TRAD - you don't need to close questions when they get answered...
Sep 16, 2010 at 02:03 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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)<----- 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)
more ▼

answered Sep 06, 2010 at 02:15 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

+1, epic effort.
Sep 06, 2010 at 02:43 PM Matt Whitfield ♦♦
@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.
Sep 06, 2010 at 11:57 PM WilliamD
Agreed with Matt's comment. +1s all round!
Sep 07, 2010 at 01:15 AM ThomasRushton ♦
+2 (if only) Top work... All the info in one place.
Sep 07, 2010 at 05:36 AM sp_lock
Sep 07, 2010 at 12:40 PM Tim
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1944
x35

asked: Sep 06, 2010 at 11:50 AM

Seen: 3593 times

Last Updated: Sep 06, 2010 at 01:50 PM