July 29, 2011 at 4:47 am
Hello,
We have come across a situation where user has complained about data loss. We tried to check the log entries of that specific day using a log reader software (the log backup of the database is not scheduled).But log entries are available only from the next day onward.
We found that the server was restarted on the same night and below error was loged in Windows event log from few other databases.
Error: 3314, Severity: 21, State: 4. During undoing of a logged operation in database 'tempdb', an error occurred at log record ID (49:16:12). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
We checked SQL error log and it has the error " File activation failure. The physical file name <Log file path with one additional space before _LDF> may be incorrect."
What could have caused the log entries to vanish?
Can anyone advise?
July 29, 2011 at 5:46 am
Is the DB in full recovery?
Do you have a full backup prior to the data loss?
Assuming you didn't delete the longs or truncate it you should be able to recover.
July 29, 2011 at 5:55 am
The database is in Full Recovery. We have the full backup of the state prior to data loss and data entry too.But we do not have log backup as it was not scheduled for that particular database.
July 29, 2011 at 6:06 am
Doesn't matter that you don't have the log backups because you can take 1 NOW.
Ask the user when the dataloss occured.
Then here's where it goes to trial and error if he's wrong.
Go do this while I make a sample script.
July 29, 2011 at 6:09 am
Yeah.I tried with the user. It seems the user is right. The data which was entered in some other database on the same drive was also lost.When we checked the tables we did not find any kind of data entered on that particular day which is not possible at it wasa busy working day.
July 29, 2011 at 6:16 am
I wonder why the data is lost from both the log file(as the log reader software does not show any log entry) and data file.
Please tell whether the following could have happened.
1. Due to some reason the log file could not be accessed by the SQL Server.
2. To make the daabase online SQL Server internally executed checkdb statement with allow data loss which put the database in this condition.
But not sure about it and even dont know how to track it.
July 29, 2011 at 6:23 am
What do you mean with the log entries are available from the next day onward? Do you truncate the log daily or something? If that's the case I don't see any way to recover this.
You also talk about drive failure. What was on that drive and what did you lose?
July 29, 2011 at 6:30 am
i.e. The data loss was report on 26th the data entry which is available in log is from 27th Onward.
Not exactly the drive error but windows error log on 26 night shows that the sql server services was terminiated. it was found that there was some disk space issue on the server that day. We have couple of database files kept on the same drive.We checked in other database file stored on the same drive and found a few entries from other database missing too.
So is it a repair,allow_data_loss type of thing?
July 29, 2011 at 6:38 am
I still don't see any problem. Just to make sure just run this for all your dbs :
DBCC CHECKDB('PROD_DB') WITH NO_INFOMSGS, ALL_ERRORMSGS
Once we're past that we'll go to the next step.
July 29, 2011 at 6:56 am
the command executed successfully without any error messages.
July 29, 2011 at 7:03 am
So you have a full backup on the 26th?
data loss on the 27th?
DB is in full mode?
Never ran any type of log backup on that db since the 26th?
July 29, 2011 at 7:08 am
Yes you are right.
July 29, 2011 at 7:13 am
To let you know there are few entries in SQL Server error log on 28th as
2011-07-28 08:57:09.09 spid56 The database 'dbname' is marked RESTORING and is in a state that does not allow recovery to be run.
2011-07-28 08:57:10.12 spid56 Starting up database 'dbname'.
2011-07-28 08:57:10.59 Backup Restore is complete on database 'dbname'. The database is now available.
2011-07-28 08:57:10.61 Backup Database was restored: Database: dbname, creation date(time): 2010/10/12(15:19:33), first LSN:
902:19572:39, last LSN: 902:19588:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'K:\Backup\dbfolder\dbname_backup.bak'}). Informational message. No user action required.
July 29, 2011 at 7:24 am
That means that someone did a restore on that database at that time.
If this is the DB you've lost data from then you're screwed, the data is gone and you have no way to get it back.
July 29, 2011 at 7:27 am
Actually nobody did the restore manually but the server was restarted once by the infra team...really not sure what has caused these entries in the error logs.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply