Cannot Backup Tran Log

  • The tran log on one of my databases was subject to non logged updates and therefore I was unable to back it up until I perform a full back up of the database. The problem came in when I attempted to back up the db, the tran log was so full that I couldn't even backup my db. What I ended up doing was increasing the tran log size, backed up the database and then the tran log. I don't want to have to increase the size of the tran log if I run into this problem again. Is there a way that I can clear out the tran log so that I can run the db backup. I checked Books Online but it didn't help much.

    Thanks

    E...

  • Did you try :

    Backup Log LogName with Truncate_Only

    This flushes all the commited transactions to the data files, and release some space from the log. After this you should backup the database to avoid loss of data

  • No I did not try that but would it not produce the same (non-logged updates) error no matter how I attempt to backup the tranlog?

    E...

  • The great thing about the the northwind database is it gives you a place to try things out. If your stuck your stuck, glad to help - but, you'll be better off for trying.

    Any hoot, after you get your database backed up and your log file flushed out, you might find your log fog file is now bigger than you intended it to be.

    To regain back some of that space take a look at this most excellent article/script

    http://www.sqlservercentral.com/scripts/contributions/26.asp

    Good luck and have fun,

    quote:


    No I did not try that but would it not produce the same (non-logged updates) error no matter how I attempt to backup the tranlog?

    E...


    John Zacharkan


    John Zacharkan

  • yes, thanks it's nice to know i can get back some of that space. Thanks Again

    E...

  • The script is for SQL Server 7.0 because it resolves an issue where the log shrink was a deferred operation in SQL Server 7.0 The behavior was changed in SQL Server 2000. Here's the KB article on shrinking the transaction log in SQL Server 2000:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;272318

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • Thanks for pointing that out Brian. I'm so up to my neck in 7.0 servers I sometimes forget about the 2K release. I'm dead when they release Yukon.

    quote:


    The script is for SQL Server 7.0 because it resolves an issue where the log shrink was a deferred operation in SQL Server 7.0 The behavior was changed in SQL Server 2000. Here's the KB article on shrinking the transaction log in SQL Server 2000:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;272318

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


    John Zacharkan

    Edited by - zach_john on 02/11/2003 10:32:29 AM


    John Zacharkan

  • quote:


    Thanks for pointing that out Brian. I'm so up to my neck in 7.0 servers I sometimes forget about the 2K release. I'm dead when they release Yukon.


    Isn't this the plight of most SQL Server Admins out there? 🙂

    I thought this stuff was supposed to auto configure and run itself? At least that's the impression IT managers have.

    Sorry for the rant... Carry on... Nothing to see here...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply