DBCC tells me to run with repair_allow_data_loss

I took over a database that had no maintenance jobs other than backups being done. I immediately restored the oldest backup (7 days ago) to another name and ran DBCC CHECKDB on it. It has the same number of errors and recommendation to run with repair.

My question is what exactly is going to potentially be lossed. The error is an allocation error. Does that mean my data is ok, just an allocation is messed up?

Thanks for any help.

Running on MSSQL 2005 SP3

_UPDATE I have ran the repair on my restored copy of the database. Doing a row count on the table that had the error from production to the test copy gave me the same results. Is there something else I should compair? I am doing another dbcc checkdb on the database since the repair finished.

more ▼

asked Sep 14, 2010 at 06:52 PM in Default

avatar image

1k 10 12 16

A simple rowcount may not show up data loss, try something like RedGate SQL Data Compare or Tablediff (http://msdn.microsoft.com/en-us/library/ms162843.aspx) to see what differences there are in the data, if any.

Sep 15, 2010 at 06:57 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Have you run it with repair on the test copy? I think I would run it there and see what the results are like. You can get a chance to compare the test to the live data and see if anything crucial changes are made. If you can identify the changes then you can choose whether to run it on live. A great resource for DBCC CHECKDB is Paul Randall's site www.sqlskills.com so go and read up all about the options that are available with that command.

more ▼

answered Sep 14, 2010 at 11:30 PM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

  • for testing the repair on a test instance.

@sqlnubi - just remember, REPAIR_ALLOW_DATA_LOSS can cause you to lose data (surprise, surprise!) - some people seem to not (want) to understand that fact and are surprised when data goes missing.

Sep 14, 2010 at 11:57 PM WilliamD
(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Sep 14, 2010 at 06:52 PM

Seen: 1800 times

Last Updated: Sep 15, 2010 at 05:47 AM

Copyright 2018 Redgate Software. Privacy Policy