Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011

    Unanswered: shrink or reset corrupted log file


    I have an SQL Server 2008 Standard with no service pack,
    Windows server 2008 64 bits and a hard drive raid 5 with 6 disks.

    3 weeks ago, I lost an hard drive and replaced it, the raid rebuilded successfully.
    As a backup solution, I have Backup Exec 12.5 for Windows Server.
    The database is backed up with full backup 2 times a day and transaction log is backed up 1 time a day.

    The database itself is about 30GB.
    Transaction log gets to about 50GB per weeks and get shrinked every sunday.

    Since I got the hard drive fail, the transaction log no longer want to shrink.
    The file is now at 160GB.

    I also tried to manually (in t-sql) backup the db and transaction log twice before shrinking it, but it does not shrink.
    Tried to put database in simple recovery mode, does not work, so I changed it back to Full recovery mode.

    For the next weekend, I scheduled to detach database, rename log file and attach database. I know it's not the good way, that's why I ask some help.

    I'm stuck with an infinite growing transaction log.
    Is this situation happened to any of you?

    Thanks for your help.
    Last edited by alprog; 06-01-11 at 12:57. Reason: Missed a step

  2. #2
    Join Date
    Jun 2011


    After a whole week, I got absolutely no response and a database log file of 300GB.

    Fortunately, yesterday, after about 30 attempts, I found the only solution that can work, and this worked.

    The database had 2 log files, one of the files was broken and because of that, the 2 files would not shrink at all and always expand.

    To correct this, I got to detach the database, rename the 2 logs files and write some sql :

    I tried :
    EXEC sp_attach_single_file_db @dbname = 'DBName',
    @physname = 'D:\Database SQL Server\MSSQL\Data\DBName.mdf'

    But this did not work because there are 2 log files.
    So here is the workaround that fixed the problem :

    use master

    and I completed the work with a full backup.
    Hope this will not happen to anybody because it is not recommended to use this method, but sometimes it's the only way it works.

  3. #3
    Join Date
    Jan 2003
    Provided Answers: 17
    Do you have anything in the log_reuse_wait_desc column for this database? How often is the log backed up normally?

  4. #4
    Join Date
    Jun 2011
    log_reuse_wait : 2
    log_reuse_wait_desc: LOG_BACKUP

    and as I told in the first post :
    The database is backed up with full backup 2 times a day and transaction log is backed up 1 time a day.

    The reason why I did not restore from a backup is because I found the problem 3 or 4 days after the disk failure, so it was too late to restore since it is a production database that I cannot loose any data.

    Now everything goes like there never had any errors.

    The solution has been found in my second post but I don't know how to close the thread since it's my first time on this forum.

    Thank you

Tags for this Thread

Posting Permissions

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