Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    7

    Unanswered: log shring when no place on the disk ?

    Hello,

    I run out of space on the disk, my db log is 130GB, I need to shrink db log. I made a backup through network by UNC of the database, but when I want to backup log, I get a message :
    BACKUP LOG cannot be performed because there is no current database backup.

    But the backup is reachable, I can restore from it.

    Any ideas ?
    Last edited by laserovic; 11-28-07 at 08:48.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by laserovic
    Hello,

    I run out of space on the disk, my db log is 130GB, I need to shrink db log. I made a backup through network by UNC of the database, but when I want to backup log, I get a message :
    BACKUP LOG cannot be performed because there is no current database backup.

    But the backup is reachable, I can restore from it.

    Any ideas ?
    I suspect from the error message that you are using SQL 2005, please confirm.

    SQL 2005 won't allow you to switch a database from SIMPLE recovery to FULL recovery and immediately start making LOG backups (you have to make the switch, then do a FULL backup and then startdoing LOG backups).

    That all being said, the scenario you are describing does not seem to make a lot of sense.

    1) Make sure that the full database backup you are making is actually completing successfully. Make sure that it was made AFTER you enabled FULL recovery mode.

    2) As an emergency measure (and this will ruin your recovery process), you can switch to SIMPLE recovery, switch back to FULL recovery and then shrink the log file. That should remove unused space, but you will need to take a FULL backup immediately.

    3) Finally, another thing to check is DBCC OPENTRAN to see if there are any open transactions that are preventing log entries from being cleaned out. It will also show you if there are issues involving replication (which would be a separate topic).

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    how did you let the log get to 130GB?

    Do you need the log?

    How about TRUNCATE ONLY
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2007
    Posts
    7
    Quote Originally Posted by hmscott
    I suspect from the error message that you are using SQL 2005, please confirm.

    1) Make sure that the full database backup you are making is actually completing successfully. Make sure that it was made AFTER you enabled FULL recovery mode.

    2) As an emergency measure (and this will ruin your recovery process), you can switch to SIMPLE recovery, switch back to FULL recovery and then shrink the log file. That should remove unused space, but you will need to take a FULL backup immediately.


    Regards,

    hmscott
    It is 2005. It helped.
    Thank you very much.

    But what I still don't understand is, why standard scenario didn't work:
    backup db
    backup log
    truncate log
    when the backup were realized through network (using UNC).
    It is in full recovery mode long time. There are no open trans.
    Regards,

  5. #5
    Join Date
    Nov 2007
    Posts
    7
    Quote Originally Posted by Brett Kaiser
    how did you let the log get to 130GB?

    Do you need the log?

    How about TRUNCATE ONLY
    the log got to 130Gb by rebuilding indexes.

    Btw, do you have some recommendations about rebuilding indexes ?

    In Sql 2000 I did it by a script using DBCC dbreindex during the night once a week, then log shrink (to be able to copy db to testing environment). I am not so experienced with SQL2005, but maintenance seems to be not perfect, so I used script and it grew up from 90 GB db + 5GB log to 130GB + 130GB.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would begin a tran

    Rebuild a single index

    Commit the tran

    Dump the log

    One by one until done
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Selectively defragging is good practice too e.g.:
    http://weblogs.sqlteam.com/tarad/arc.../27/60415.aspx

  8. #8
    Join Date
    Nov 2007
    Posts
    7
    Quote Originally Posted by Brett Kaiser
    how did you let the log get to 130GB?

    Do you need the log?

    How about TRUNCATE ONLY
    I don't need such a big log.

    It didn't work, because you need to mark it as unused. How to do that in a different way than by back up ?

    Regards,

  9. #9
    Join Date
    Nov 2007
    Posts
    7
    Quote Originally Posted by laserovic
    I don't need such a big log.

    It didn't work, because you need to mark it as unused. How to do that in a different way than by back up ?

    Regards,
    I will reply to myself: you may be right, it should work, truncate_only and than shrink.

Posting Permissions

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