September 30, 2009 at 6:44 pm
*sigh* Well, allow me to start off by saying 1) This problem is not in my database; 2) I had nothing to do with the actions that led to this problem and 3) I'm only acting as a messenger, so I will not be able to immediately answer questions etc. On that note....
We have a new database in my group, running on SQL Server 2000. This is the straight-out-of-the-box installation; there has never been a patch applied, a fault entirely on the shoulders of my support company. So if there is a patch / update available that will fix this, I'd love to know about it. Anywho, back to the debacle. So the database was only 5 Mb in size, and all the data was strictly test data. However, for some reason the Log file was 57 Gb's, which was astounding. My boss and the guy responsible for the database spent most of the day today trying to figure out what to do, and I think they may have made an error in judgement. They detached the Log File from the database and then deleted it; when they went to restore the database using the BAK file from this morning, the data was restored but not the Log file. The database is currently inaccessible and completely useless. So they tried a number of different things and all to no avail - they cannot get the Log file recreated. They are getting various messages, ranging from Cannot Create Database Object to Datatype or Length of String Invalid (again, apologies for my extraordinarily vague descriptions, it's all they gave me).
OK, so yes, we need to get the installation up to speed with patches etc. However right now that's not feasible unless I can prove that there is a patch that would fix this problem. Yes, I need to investigate why the Log files are not included in the BAK files, but again, that's going to take some time. I'm hoping to get a "right now" fix so that the database will be operational by tomorrow morning. Any thoughts/ideas/suggestions would be much appreciated.
Thanks
Chris
October 1, 2009 at 1:19 am
Try dropping the database completely and then restoring a complete new copy. The errors may have to do with trying to replace a file that isn't there.
Restore will put a DB back in exactly the same state it was at time of backup, log files and all (though the log may not contain what it did at time of backup).
If you want to try and hack a log creation, read through this: http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2009 at 1:23 am
p.s. oh, the reason for the 67GB log file and small mdf. Full recovery model, no log backups. In that state, once the firstr full backup has been taken, log records will not be discarded because only a log backup will allow the space in the log to be reused. Net result, log grows until it fills the disk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2009 at 2:41 am
Hi,
What you want to achieve?
If you have a proper backup, you can restore the DB no issues in that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply