How would a SQL Server 2005 database lose a few days data?

Question

Hi! I really need some help here.

I'm the owner of a SQL Server Database application that lost three days data! I can't understand how or why.

So here is the set-up.

• SQL Server 2005 32bit standard edition database on Windows 2000 server. (Database B)

• Database is in simple recovery mode

• The database is connected as a subscriber to another database(SQL Server 2005 64bit enterprise edition on Win2k3 enterprise) using SQL Server continuous Merge Replication. (Database A)

DatabaseB was rebooted on night X as part of scheduled reboot. When the database came back up it was used as normal for a couple of days and data was created into it perfectly fine.

But then yesterday Day X + 4 it lost a lot of data.

Database B is on a server with another instance of SQL Server and they both started to run out of memory(conflicting with each other).

Here is the sequence of events from the event log when I think this happened.

AppDomain 2 (DatabaseB.dbo[runtime].1) is marked for unload due to memory pressure. 

AppDomain 2 (DatabaseB.dbo[runtime].1) unloaded. 

BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. 
The simple recovery model should be used to automatically truncate the transaction log. (on DatabaseB)

AppDomain 3 (DatabaseB.dbo[runtime].2) created.

I know the data is missing because of my audit logs and that a user had taken a screen shot of some of the data before it was deleted.

So here is my dilema...how could this have happened?

How can several days data go missing from DatabaseB?? (it subsequently is missing from the publication db also!)

Did the truncate with the Appdomain down cause the data to be flushed from the log?

Any and all theories considered. If anyone needs more data I can add it.

Help!

Answer 1

Merge replication is implemented with triggers, so it doesn't need full recovery. Is it possible that someone disabled all triggers in the db? its easy to do DISABLE TRIGGER [database] This would at least account for the subscriber losing data.

Those appdomain lines in the log don't mean that much, its the SQL CLR telling you its unloading assemblies to free up some memory. & then reloading them later on.

Truncating the log removes inactive parts that have been committed to disk, having the recovery model set to simple means there's no point in truncating the log, as the message suggests.

None of this explains why data went missing on both the servers though. There has to be something else that caused this.

How did you verify that for the 4 days when everything was 'created perfectly fine' that it actually was? do you have backups from these days? can you see records with time stamps from those days?

Is it possible there's a ghost in the machine that did a restore without telling you?

Answer 2

This isn't the answer you want to hear, but in a nutshell, SQL Server doesn't "lose" data. Someone deleted it. If you had the database in full recovery mode, you could use a product like Quest LiteSpeed to read the logs and identify exactly how it was deleted, but in simple mode...sorry, sir, but you're out of luck.