SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Database gone missing...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jensp
Starting Member

3 Posts

Posted - 09/14/2007 :  05:05:31  Show Profile  Reply with Quote
Hello. We have a smaller system on one of our servers where a couple of users where beta-testing. This system used a SQLExpress 2005 database (databaseName_data.mdf).

But yesterday we saw that we couldn't use the system anymore, we got errors about the connection to the database. We open SQL Management Studio and connected to the SQL Server and we saw the name of the database in the list, but it was completly empty. Nothing. Not the "folder" for Tables, Programmability, Security... nothing.

We then browsed to the folder where the MDF file used to be, and there we only found the LDF file. The MDF file was gone.

We "know" that no one here have been shutting down the SQL Service and then deleted the DB, so we are trying to figure out what has happen.

It's not a major issue, because it's just a beta-test, but we don't want this to happen later on again...

Does anyone have a clue of what might be going on?

We are using three instances of SQL Express on this test machine btw... One for the public system (wich used this db), one for development and one for some random tests...

The public server and develop server used databases with the same name, but of course, different files on the hdd (and different instances of SQL Express).

//J

Edit: I might add that we hadn't backed this db up yet... Is there some way to use the LDF-file to restore some of the data?

Edited by - jensp on 09/14/2007 05:09:07

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/14/2007 :  05:36:54  Show Profile  Reply with Quote
"Does anyone have a clue of what might be going on?"

Not really!

I don't think it would be possible to delete the MDF if SQL Service was running (unless the database was set to AutoClose).

Might be worth checking the SQL logs to see when it last started/shutdown, in case that tells you anything.

"Is there some way to use the LDF-file to restore some of the data?"

I very much doubt it. If the database was in Full recovery model you might be able to read the TLog with a 3rd party tool, such as from Lumicent (sp?)

Best to set up a maintenance plan to backup ALL databases when you install SQL Server. You can adjust that policy later if necessary.

Kristen
Go to Top of Page

jensp
Starting Member

3 Posts

Posted - 09/14/2007 :  05:52:00  Show Profile  Reply with Quote
Thanks for your reply. I've looked at the SQL Log files and this is what i got:


2007-09-12 14:42:13.21 spid51      Starting up database 'NAIS'.
2007-09-12 14:42:13.51             Fel: 18456, allvarlighetsgrad: 14, tillstånd: 16
2007-09-12 14:42:13.51             Login failed for user 'nais'. [CLIENT: 172.18.30.4'
2007-09-12 14:45:44.78 spid51      Starting up database 'NAIS'.
2007-09-12 14:45:44.95             Fel: 18456, allvarlighetsgrad: 14, tillstånd: 16
2007-09-12 14:45:44.95             Login failed for user 'nais'. [CLIENT: 172.31.0.1'
2007-09-12 14:48:52.51 spid51      Starting up database 'NAIS'.
2007-09-12 14:48:52.82 spid51      Fel: 17207, allvarlighetsgrad: 16, tillstånd: 1
2007-09-12 14:48:52.82 spid51      FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:\ADBS\NAIS\NAIS_Data.MDF'. Diagnose and correct the operating system error, and retry the operation.
2007-09-12 14:48:52.87 spid51      Fel: 17204, allvarlighetsgrad: 16, tillstånd: 1
2007-09-12 14:48:52.87 spid51      FCB::Open failed: Could not open file C:\ADBS\NAIS\NAIS_Data.MDF for file number 1.  OS error: 32(The process cannot access the file because it is being used by another process.).
2007-09-12 14:48:53.04             Fel: 18456, allvarlighetsgrad: 14, tillstånd: 16
2007-09-12 14:48:53.04             Login failed for user 'nais'. [CLIENT: 172.18.30.4'

(Some words are swedish)

I'm a newbie on this things so I can't really see what caused this... Anyone?

Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/14/2007 :  06:02:38  Show Profile  Reply with Quote
Well at 2007-09-12 14:48:52.82 the file was there, but "being used by another process". So SQL Server must have either a) closed it (e.g. AutoClose set), or b) have been in the process of starting up.

What else was accessing it? Tape backup software perhaps? One of the other instances of SQL Server (that would be bad I guess!)

That doesn't explain why the file is no longer there, but at 2007-09-12 14:48:52.82 something else was accessing that file and prevented SQL Server opening it.

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/14/2007 :  06:34:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Some defragmentation applications running?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jensp
Starting Member

3 Posts

Posted - 09/14/2007 :  07:19:07  Show Profile  Reply with Quote
I've managed to find what probably caused this to happen. I looked
at the LOG file for the other instance (our DEVELOP instance, named SQLDEVELOP1).
This instance used a database with the same name as the one who has disappeared (the one that dissapeared was on the instance
SQLEXPRESS). Both databases were namned NAIS, but the MDF and the
LDF-files where in completly different folders on the machine. But
when I look at the develop-instance log file I see that (at the same time as stated in the SQLEXPRESS log file) this instance is
trying to access the LDF file in the folder where the SQLEXPRESS database is.

I've checked the file path on the database on SQLDEVELOP1 and it
still points to the correct folder (that is, not the folder where
the file that has dissaperad were). So, therefore I wonder if it's
known that using two (or more) instances of a SQLEXPRESS server
wich both contains databases with the same name (but not the same
file, folders etc.) can cause a conflict like this? Well, apparently it CAN, because it looks like that's what has happened...

//J

Edited by - jensp on 09/14/2007 07:25:01
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/14/2007 :  08:38:54  Show Profile  Reply with Quote
I doubt that would have mattered, but maybe someone did a DROP or RESTORE (which maybe failed or somesuch) which as a consequence deleted the database, perhaps intending to recreate it, but the create was blocked - maybe because a file lock was then acquired by the other instance.

Anyways, I suppose it would be a good idea to put the database files for each instance in distinctly separate folders

Kristen
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 09/14/2007 :  10:42:19  Show Profile  Reply with Quote
Looks can be deceiving. I think Kristen has the nub of it. consider this conspiracy theory, if you will;

1) user backs up NAIS database on the DEVELOP instance.
2) user then attempts to restore this backup on the SQLDEVELOP1 instance, but forgets to MOVE the MDF file.
3) user manages to force the restore (perhaps autoclose is on?)
4) User realizes the database he has restored is no good to him, and drops it (dropping the MDF file from the DEVELOP instance).

Like the "magic bullet" theory, this has a few holes I am unhappy with (e.g. Why only one file would have been moved), but it comes close to explaining what happened.
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 09/14/2007 :  23:13:18  Show Profile  Reply with Quote
Try this: stop develop instance then check if the db comes back. If not, it's not related to other instance. Also check if db files are in both locations, and verify db file path in sysfiles on both instances.
Go to Top of Page

Zoroaster
Aged Yak Warrior

USA
702 Posts

Posted - 09/15/2007 :  00:34:46  Show Profile  Reply with Quote
It could possibly be a hardware issue, wouldn't be the first time disk issues caused files to disappear.




Future guru in the making.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/16/2007 :  03:16:12  Show Profile  Reply with Quote
"wouldn't be the first time disk issues caused files to disappear"

Not sure I agree with that. I consider it very hard for a directory entry to disappear ... if that disk block goes bad you'll get errors trying to access the directory (or RAID will protect you from that problem).

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000