March 28, 2008 at 5:30 pm
Hello,
I'm a newbie at MS SQL 2000 - From what I've understood, I can use simple recovery model to backup the database and I've set it to do that via Enterprise Manager. The log file is about 15GB for a 10GB database. Since I will never use the transaction log file to recover any data, is there any usefullness to keeping it around when its only occupying space?
thanks,
March 28, 2008 at 6:10 pm
Well, A) you don't have a choice, you must have a transaction log. and B) just because it is not being used for Restore/recovery does not mean that it isn't doing anything. It is still used as a transaction log, which a database needs for transaction rollbacks.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 29, 2008 at 12:06 pm
As I understand, you changed recovery model to simple. As berry already pointed that log file will be used in any case, but you can truncate to keep it smaller in size.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
March 29, 2008 at 4:30 pm
You obviously do not have autoshrink enabled so you need to manually shrink your transaction log using DBCC ShrinkDB or DBCC ShrinkFile
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2008 at 4:24 am
As indicated try to shrink the log file using dbcc shrinkfile. If the log file is not getting shrinked check if there any open transactions in your database using DBCC OPENTRAN ('Your DBName')
[font="Verdana"]- Deepak[/font]
March 31, 2008 at 10:40 am
thanks to all your responses. I'll try the shrink approach then.
March 31, 2008 at 10:50 am
Jack Corbett (3/29/2008)
You obviously do not have autoshrink enabled so you need to manually shrink your transaction log using DBCC ShrinkDB or DBCC ShrinkFile
Don't use autoshrink. It's a performance-killer. Will slow down your whole database.
A better option is to schedule a shrink task as part of your regular maintenance plans.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 31, 2008 at 10:52 am
Great! thanks.
March 31, 2008 at 10:56 am
GSquared (3/31/2008)
Jack Corbett (3/29/2008)
You obviously do not have autoshrink enabled so you need to manually shrink your transaction log using DBCC ShrinkDB or DBCC ShrinkFileDon't use autoshrink. It's a performance-killer. Will slow down your whole database.
A better option is to schedule a shrink task as part of your regular maintenance plans.
Don't take my mention of the autoshrink setting as advocacy, just noting that the post implies it is not on.
I have used autoshrink on low usage db's also.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 31, 2008 at 11:04 am
GSquared (3/31/2008)
Jack Corbett (3/29/2008)
You obviously do not have autoshrink enabled so you need to manually shrink your transaction log using DBCC ShrinkDB or DBCC ShrinkFileDon't use autoshrink. It's a performance-killer. Will slow down your whole database.
A better option is to schedule a shrink task as part of your regular maintenance plans.
Or - don't. As in - don't shrink.
The transaction log grows to the size it needs. So - short of accidents, bad code, oops'es or forgetting to turn on log backups (when not in simple mode), the size it gets to be is the size it "needs" to be to do everything it needs.
Auto-growth is IMO worse that auto-shrinking, so putting the trans log in a scenario where it wouldn't have enough room (and would need to grow again) is ultimately a waste of clock cycles IMO. Why shrink something that's going to grow again?
I might consider running a shrink manually every once in a while in case something "ugly" ran that made the t-log artificially big. I however would not run it through any scheduled process, or automatic. The fact that it would have to grow at all would mean something I'm doing isn't "working".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply