| Author |
Topic |
|
jensp
Starting Member
3 Posts |
 Posted - 2007-09-14 : 05:05:31
|
| 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).//JEdit: 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? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-14 : 05:36:54
|
| "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 |
 |
|
|
jensp
Starting Member
3 Posts |
Posted - 2007-09-14 : 05:52:00
|
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: 162007-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: 162007-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: 12007-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: 12007-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: 162007-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? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-14 : 06:02:38
|
| 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-14 : 06:34:36
|
Some defragmentation applications running?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
jensp
Starting Member
3 Posts |
Posted - 2007-09-14 : 07:19:07
|
| 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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-14 : 08:38:54
|
| 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 foldersKristen |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-09-14 : 10:42:19
|
| 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. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-14 : 23:13:18
|
| 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. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-15 : 00:34:46
|
It could possibly be a hardware issue, wouldn't be the first time disk issues caused files to disappear.
Future guru in the making. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-16 : 03:16:12
|
| "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 |
|
|
|
|
Database gone missing...