Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Posts
    2

    Unanswered: recovering multi-log db without logs

    We have a problem.
    A database was set to use 2 log files, and one of them somehow grew to a bloated 32GB, with a 400MB database! The recovery model was set to full, which will not be used again.
    Now, whenever we start SQL Server a system process places an exclusive lock on the database is question and never lets go of it. It doesn't seem to be doing anything but it never lets go, so nothing can be done with the database.

    We suspect the massive log file has something to do with the problem.
    We want to somehow remove the log files, but can't do a damn thing because of the lock.

    We have tried renaming the log files, then detaching the database and reattaching, but sp_attach_db and sp_attach_single_file_db won't recreate log files on databases that use more than one.

    We have tried creating a "dummy", empty database with the same name and log files, then moving in the original MDF, but it error out saying we can't use log files from another database.

    Does anyone have any suggestions?
    No, we don't have backups of any of the log files, only the mdf. This is going to be corrected, but we need to get this thing going!

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    sp_attach_single_file_db should cope with it if you have managed the dettach succesfully.
    It should recreate a single log file for you.

    Having a backup of the mdf sounds like you are just backing up the file. This might work if you close down sql server or dettach the database first but better to just backup the database.

    What is the status of the database when it has the exclusive lock on it@ recovering?

  3. #3
    Join Date
    Jan 2002
    Posts
    2
    Thanks for the reply. the attach_single_file_db doesn't work either with a multi-logfile database, if you don't have the log files available. We did correct the problem with a call to Microsoft.

    Thanks for the suggestion.

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    What did MicroSoft say?

  5. #5
    Join Date
    Nov 2002
    Posts
    24

    Post

    Originally posted by nigelrivett
    What did MicroSoft say?
    I guess that under the circumstances they suggested using
    sp_attach_single_file_db. But use if anyone is planning on using this command have a look at BOL first.

    BTW using full recovery mode is ok as long as you create a job that backs up the transaction log on a regular basis. One can easily set this up in enterprise manager -> sql server agent -> right click on datbase maintence plans -> new etc etc you can even tell it to write over trans backups older than a set period of time say 24 hours.

    Making a really good maintaince plain withing sql server is not brain surgery it just takes a little effort.
    TECMAN

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •