x

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

sqlnubi gravatar image

sqlnubi
983 7 10 12

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][1] so go and read up all about the options that are available with that command.

[1]: http://www.sqlskills.com
more ▼

answered Sep 14, 2010 at 11:30 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

+1 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.

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:

x1945
x47

asked: Sep 14, 2010 at 06:52 PM

Seen: 1575 times

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