As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options

SQL Error 28

RichyRichy Registered User regular
edited July 2008 in Help / Advice Forum
So, I'm working with MS SQL Server 2005 on Windows XP laptop.

I tried an operation (a series of delete to clear all the data) on my database, and somehow this caused the log file to balloon up to 20Gb (for a 4Gb db), literally filling up my entire HD. I had less than 10Mb left on my HD, I couldn't do anything. And the operation had crashed, and SQL server wouldn't access the db anymore, saying it was "recovering" it for hours on end.

Faced with no other options, I deleted the log file. Since then, I start SQL Server and try to connect, I get this error message. I can't connect, so I can't even delete the problematic database. I tried:
  • Using a blank file as a log file
  • Using the log file of another database, properly renamed
  • Using the log file of a different version of this database from another computer
  • Using both the database and log file from the other computer's version of the database
  • Installing the latest SQL Server service pack from Microsoft
None of this has made a bit of difference.

I'd like to avoid the option of uninstalling and reinstalling SQL Server. I'd also like, if possible, to save the DB tables structure (the data is not important, as I was trying to delete it when all this happened). But I have no idea how to resolve this problem.

Any suggestions would be appreciated.

sig.gif
Richy on

Posts

  • Options
    LewishamLewisham Registered User regular
    edited July 2008
    Please don't take this the wrong way, but this was exactly the sort of problem people were worried about in your last SQL thread: you shouldn't be administering a database that's so important to you. You should be using school resources.

    And I don't know the answer, sorry.

    Lewisham on
  • Options
    RichyRichy Registered User regular
    edited July 2008
    No offence taken. I'm well aware that I'm a database n00b. But the best way to learn is to do... and sometimes accidentally trash the whole damn thing. Like now, I learned not to delete db log files.

    Still seems silly that the program can't do anything if even one log file is missing.

    Richy on
    sig.gif
  • Options
    mellestadmellestad Registered User regular
    edited July 2008
    Do you have a full backup somewhere? It isn't sitting in your recycle bin is it? Your best bet would have been to use an un-delete program to restore it to the hard drive but it might be too late now if you have messed with stuff. Worth a try though. If you didn't, I hope you learn a lesson from this experience. Backup, backup, backup!!!

    Basically you need to create a new DB, which creates a log file. Then you need to bring the old mdf in to the new database. So migrate the mdf to a new db, don't try to migrate a new ldf to an old db. You will lose some data though, so expect corruption.

    Here is a article about it, but there should be something in Books Online about emergency database recovery. http://www.spaceprogram.com/knowledge/2002/06/recovering-from-deleted-log-file-on_12.html

    I do SQL admin work and when I run in to problems I find that dedicated SQL forums are the best bet, but this is a pretty common thing for a new guy to run in to, so there is a lot on Google. Plus I have a whole stash of SQL books that helped me learn. Good luck!

    mellestad on
  • Options
    RichyRichy Registered User regular
    edited July 2008
    Well I do have a backup of the DB. But how can I restore the DB if I can't get SQL server to connect? I can't get to my list of DB and restore it from there.

    Richy on
    sig.gif
  • Options
    bowenbowen How you doin'? Registered User regular
    edited July 2008
    If you have a backup of the database, just uninstall and reinstall SQL Server. It's not worth the headache.

    bowen on
    not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
  • Options
    mellestadmellestad Registered User regular
    edited July 2008
    What Bowen said. Just dump it and restore. If you really really don't want to dump it, then follow the guide I gave you. You should be able to disconnect the bad DB and then start a new one after the old one is unattached.

    mellestad on
  • Options
    RichyRichy Registered User regular
    edited July 2008
    That's what I was hoping to avoid :? Well if I don't get a better alternative in the next few hours, I'll reinstall when I get home tonight.

    Richy on
    sig.gif
Sign In or Register to comment.