Very odd data loss in SQL Server 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SvenM - 07 Mar 2008 22:07 GMT

Hi there.

I'm experiencing a very odd data loss in my SQL Server 2005. Some data
records have just gone missing (about 140 rows in one of the tables) and I
can't see any pattern. Also it seems to be, that this problem affects only 2
tables but I'm not sure about that since I haven't checked everything. Right
now I trying to find out when the data has started to be missing, but I
right now it looks like the is a coherence with a database shrink I did on
Wednesday. Is that even possible? This isn't the first shrink I did on a
database.

Is it even possible that this odd kind of data loss is the result of a
problem in SQL Server. Right now, I doubt it.

I hope that it helps, that I have a complete chain of transaction protocols
since the last good database. I hope I can restore a consistent database
with these. Well before that I have find out what happened.
The eventlog shows nothing and my next candidate is the sys.messages.

If you have any ideas about this error or where and how to look for the
reson, I'd be very happy to hear (read ;-) ) from you.

CU and thanks in advance,

Sven

Reply to this Message

Dan Guzman - 08 Mar 2008 15:52 GMT

> Is it even possible that this odd kind of data loss is the result of a
> problem in SQL Server. Right now, I doubt it.

I agree that it is highly unlikely. I suggest you start by running a DBCC
CHECKDB to rule out the posibility of database corruption.

> The eventlog shows nothing and my next candidate is the sys.messages.

sys.messages is a list of possible SQL messages rather than a log of those
that have occurred. Check the SQL Server error log file to see of there are
any related messages.

> If you have any ideas about this error or where and how to look for the
> reson, I'd be very happy to hear (read ;-) ) from you.

If you run in the FULL recovery model, you might try restoring a known good
backup to another database and applying logs with STOPAT and STANDBY to
isolate the specific timeframe the rows disappeared. The precise time might
provide a clue as to the cause.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Hi there.
>
[quoted text clipped - 21 lines]
>
> Sven

Reply to this Message

Tibor Karaszi - 08 Mar 2008 19:34 GMT

> If you run in the FULL recovery model, you might try restoring a known good backup to another
> database and applying logs with STOPAT and STANDBY to isolate the specific timeframe the rows
> disappeared. The precise time might provide a clue as to the cause.

Something I elaborated a bit on below:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp

And of course some 3:Rd part log reader utility can also help hunt down who/what did this.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>> Is it even possible that this odd kind of data loss is the result of a problem in SQL Server.
>> Right now, I doubt it.
[quoted text clipped - 37 lines]
>>
>> Sven

Reply to this Message

SvenM - 08 Mar 2008 19:58 GMT

> Something I elaborated a bit on below:
> http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
>
> And of course some 3:Rd part log reader utility can also help hunt down
> who/what did this.

I only found the tool from Lumingent. Do you recommend it? The cost aren't
that importent

Thanks and CU
Sven

> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi

Reply to this Message

Tibor Karaszi - 08 Mar 2008 22:13 GMT

> I only found the tool from Lumingent. Do you recommend it?

I don't favour one before some other. I suggest you check them out and use the one which suits you.
I've listed a few on my links page:
http://www.karaszi.com/SQLServer/links.asp

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>> Something I elaborated a bit on below:
>> http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
[quoted text clipped - 9 lines]
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi

Reply to this Message

SvenM - 08 Mar 2008 19:56 GMT

>> Is it even possible that this odd kind of data loss is the result of a
>> problem in SQL Server. Right now, I doubt it.
>
> I agree that it is highly unlikely. I suggest you start by running a DBCC
> CHECKDB to rule out the posibility of database corruption.

I had already done that, it showed me no error

>> The eventlog shows nothing and my next candidate is the sys.messages.
>
> sys.messages is a list of possible SQL messages rather than a log of those
> that have occurred. Check the SQL Server error log file to see of there
> are any related messages.

ok, I will check that

>> If you have any ideas about this error or where and how to look for the
>> reson, I'd be very happy to hear (read ;-) ) from you.
[quoted text clipped - 3 lines]
> to isolate the specific timeframe the rows disappeared. The precise time
> might provide a clue as to the cause.

Well, I'm right now im very happy I use the full Recovery model, but the
error seems to have occured on Wednesday and was detected on Friday. If I
find out the exact point in time, and if there ist a transaction, is it
possible to skip it in the restore?

A bit ;-) Thanks

CU, Sven

> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
>> Hi there.

Reply to this Message

Tibor Karaszi - 08 Mar 2008 22:12 GMT

> Well, I'm right now im very happy I use the full Recovery model, but the error seems to have
> occured on Wednesday and was detected on Friday. If I find out the exact point in time, and if
> there ist a transaction, is it possible to skip it in the restore?

You cannot skip one or some transactions when you restore, that could potentially cause havoc for
your data. What you can do is to stop at some time. Or use some log reader to "undo" based on what
you have in your log.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>>> Is it even possible that this odd kind of data loss is the result of a problem in SQL Server.
>>> Right now, I doubt it.
[quoted text clipped - 31 lines]
>>
>>> Hi there.

Reply to this Message

Dan Guzman - 09 Mar 2008 15:26 GMT

> Well, I'm right now im very happy I use the full Recovery model, but the
> error seems to have occured on Wednesday and was detected on Friday. If I
> find out the exact point in time, and if there ist a transaction, is it
> possible to skip it in the restore?

You can specify STOPAT immediately before the bad transaction and then
either copy the deleted rows elsewhere or import into your live database.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

>>> Is it even possible that this odd kind of data loss is the result of a
>>> problem in SQL Server. Right now, I doubt it.
[quoted text clipped - 34 lines]
>>
>>> Hi there.

Reply to this Message

SvenM - 08 Mar 2008 20:05 GMT

> sys.messages is a list of possible SQL messages rather than a log of those
> that have occurred. Check the SQL Server error log file to see of there
> are any related messages.

I checked the log file. Nothing. I guess this becomes interesting.

Thanks and CU,

Sven

Reply to this Message