Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johnbellucci
Starting Member
4 Posts |
 Posted - 2004-08-08 : 11:09:09
|
| I am an IT consultant with limited SQL experience, who inherited a client who has a SQL7 database with a primary filegroup with two mdf files of 4055 and 4006 MB respectively, stored on a ... FAT system file!!! Forget the dumbass who installed a W2k server with FAT partitions for critical data. But there is more.Two days ago, several documents were created in the application that uses SQL7. There were no errors at first, but the user can't access the previous data he created and save earlier. No error message in the application itself.On the SQL server, the first error was an App Log error that says: 17053 :d:\mssql7\data\mydb_file2.mdf: Operating system error 112(There is not enough space on the disk.) encountered.There is indeed enough space on that disk, but because of the FAT partition limit of 4GB, the two primary filegroup files are at the limit. The transaction log is around 20MB. All files have unlimited growth, autoshrink, autogrowth by 10%.The second error repeated nearly 10 times was: Error: 1105, Severity: 17, State: 2Could not allocate space for object 'tblImages' in database 'mydb' because the 'PRIMARY' filegroup is full. At that time, the db was already on unlimited and auto-grwoth, but I added the autoshrink soon after.The db and transaction logs are backed up regularly, daily at night for the db, and every 6 hours for the tlog.I forced a full backup of all databases that was successful, but the tlog for mydb was NOT backed up although there was NO error message stating it wasn't. The missing data still can't be retrieved from the application front-end.I believe the data might still be in the log cache, was probably transferred to the tlog, but couldn't make it to the db because files are maxed up, which is why the application still can't retrieve the data. Am I correct ?Also the DBCC DBREINDEX (tblImages) job failed every time it was run within the past 24 hours.The latest error after db backup attempt is:Error: 1101, Severity: 17, State: 2Could not allocate new page for database 'mydb'. There are no more pages available in filegroup PRIMARY. Space can be created by dropping objects, adding additional files, or allowing file growth. The server has not been rebooted for a month, so the log cache should still have all "missing" data, right ?Also, I don't trust enough the disk system on that server, so I don't want to convert the FAT system to NTFS until I get a second server.Since file growth is allowed, but FAT prevents outgrowing, should I actually RESTRICT growth to some value around 4 GB, like 1MB above the current value, and add another file in filegroup ?Please give me some feedback ASAP. My customer needs to be back up in 6 hours !!!thanks! JB |
|
|
johnbellucci
Starting Member
4 Posts |
Posted - 2004-08-08 : 12:32:48
|
| Ok,I just went ahead and added a 3rd file to the primary filegroup.Within minutes, that file went from 1MB to about 28MB (the tlog was about 20MB), so I would think that the log cache that had been flushed to the tlog would also have updated the database by then.The reindex went fine this time without error. I am currently doing another full backup of the db right now.PROBLEM: The records that were entered while the primary filegroup was "full" are still missing in the front-end application.Is there any need to "flush" anything so that the db is up-to-date ?The tlog file original last modified time before the 3rd file addition was consistent with these recent updates, so I was hoping that the log cache would be flushed to the database as well, as shown by the 28MB increase.Any idea on how to recover this "lost" data, or should I just wait until the backup is done ?Only 5 more hours before the db is back in use, and I am still missing the latest entries (although the save gave no error... |
 |
|
|
johnbellucci
Starting Member
4 Posts |
Posted - 2004-08-08 : 13:38:32
|
| The database backup completed successfully, everything is ok in the Event Viewer, ... but I still can't see the records created while the filegroup was full... and I have only 4 more hours to go...I stopped/restarted the SQL server and backed up raw files just in case I try to reattach them on another server.Any idea where the data went, and if I could get it back ? |
|
|
|
johnbellucci
Starting Member
4 Posts |
Posted - 2004-08-08 : 21:26:19
|
| Ok, I guess I cannot get these records back.Everything besides that seems ok, no error anywhere, BUT the TEMPDB.MDF is now 280 MB !!! If it is where SQL Server leaves non-committed transactions, is it possible that my "lost records" are somewhere in there ? How to get them back without messing up the database ?Even if I leave the temp file alone, which I don't really like, if we re-enter the "missing" information, could these transactions interfere with the newly entered info. someday ?Any suggestions from the db gurus here? |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-08-09 : 12:34:12
|
| TEMPDB is where temporary data is stored, but it is not wehre uncommited transactions are stored.Your client is screwed for a couple of reasons:1) they hired cheap help to set up their application and server to begin with2) they hired cheap help (or no help) to maintain itLet this be a lesson to them. When it comes to databases and critical information, you gotta do your homework on who you hire. The application apparently didn't return any errors to the client telling them it couldn't add their data, so it's more than likely gone.I'd recommend that they take the time to reconfigure their server properly and get someone to patch up that application. Otherwise they are only going to find themselves here again. |
|
|
|
average_bear4u
Starting Member
2 Posts |
Posted - 2005-02-17 : 12:37:29
|
| Can anyone tell me if a SQL Server DB can be restored from a .BAK file without the MDF file? |
|
|
|
average_bear4u
Starting Member
2 Posts |
Posted - 2005-02-17 : 12:37:34
|
| Can anyone tell me if a SQL Server DB can be restored from a .BAK file without the MDF file? |
|
|
|
|
|
|
|
|
URGENT!!! SQL7 DB primarygroup full on FAT system