February 10, 2003 at 8:22 am
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...
February 10, 2003 at 8:37 am
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
February 10, 2003 at 8:56 am
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...
February 10, 2003 at 11:31 am
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
February 11, 2003 at 8:40 am
yes, thanks it's nice to know i can get back some of that space. Thanks Again
E...
February 11, 2003 at 8:45 am
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
February 11, 2003 at 10:32 am
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
February 11, 2003 at 11:24 am
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