Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    38

    Unanswered: Transaction log full

    Hi there,

    I'm still fairly new to SQL server and I've just ran into a problem when dropping some tables from my DB as my transaction log has become full. I looked at books online and it basically said to backup the log so it will also truncate the log. However I've ran out of disk space on my data drive so can't take a backup.

    Is there a way to just truncate the active log to shrink the size?

    Hope you can help??

    Thanks

    S

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    A couple of thoughts:

    1. You could try a network backup of your transaction log to a different server (might check the total size of the log file before starting this)...

    2. You could try setting the recovery model to simple and then shrinking your log file to the minimum size (Right Click DB, All tasks, Shrink DB, then click on files and select your log file).

    3. You said your "data drive"; does that mean another volume is available on the local server? If so, back up the transaction log to that.

    Depending on how critical this data is to you, you will want to consider a regular backup plan that includes full backups and transaction log backups.

    regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Jul 2004
    Posts
    1

    Question About transaction Log

    Hi there,

    Is it possible to delete the transaction log after takig the back-up of the whole DB , and ofcourse the log file.

    Need help in this matter...

    thanks

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Well, provided the whole server isn't locked up, you can:

    1. Set the recovery mode to simple.
    2. Run a shrink database on the transaction log file.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    If you take a SQL server backup of the trans log file, the space within the log file will be reused. Therfore you could say that it does not need to be deleted ;-)

    However if you need the disk space back, you could do as suggested above, although you will need to alter the recovery mode back to complete (after the log file has shrunk) and take yet another backup to make the db recoverable beyond the original backup.
    Regards
    Dbabren

Posting Permissions

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