Switching off autoshrink
-
Other than making sure there are backups in place, is there anything else I need to do after switching off autoshrink ? We are using version
Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
-
Just look at whether you need to manually force a shrink, either on a scheduled basis or as needed. Depends on how much add/remove of course. Set the growth to a fixed amount rather than percentage usually helps keep it from ballooning.
Andy
-
Also, in you backups make sure you turncate the transaction log periodically right before the ful backup. Unless you are using simple recovery model. Even then set a max growth or periodcially check need to shirnk the file.
-
Surely you should backup the log rather than just truncate it?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
Bakup v truncate depends on needs. I'd say backup, but there are cases I don't.
BTW, MS recommendation for production is to NEVER use autoshrink. Run it as needed manually. Or scheduled, but not auto.
Steve Jones
-
One additional note. If you plan to add additional DBs to the same server down the road and do not want autoshrink turned on then set off in model DB as all defaults are based on that DB and it's structure. As for reasons not to do backup of TL and just truncate, I work in a Call Center environment where our data comes from the call switch. The data is on that server for 30 days. I only backup the DB once a week and let the TL truncate (no reason to worry about recovery.
-
Yall clue me in here. Other than for space reasons (as I believe you should have adaquate space to start with as one of the things I hear most is "Space is cheap") why would you ever shrink your log? What is it that would offset the perf hit you recieve when it grows again. Especially considering the reason it would grow would be a large transaction which is the absolute worst time to perform additional disk operations. I honestly would like to know.
Back in the day, this was considered to be a worst practice. Maybe I need to learn a new trick. Has something changed here and nobody bothered to tell me?
Is it something about the architecture of reading the large file to find an extent to place the data
Edited by - scorpion_66 on 12/05/2002 4:04:28 PM
Edited by - scorpion_66 on 12/05/2002 4:08:15 PM
-
Might be best to make this a new thread, but in my case. I've had a process go crazy and perform a one time log expansion (GBs) that is far beyond what I need in an ongoing environment. Shrinking it is a nice way to recover space.
Steve Jones
-
Agree with Steve, sometimes log (or db for that matter) will have a lot more space than is really needed, nice to reclaim some. Disk space cheap or not, if what you have starts to get full, you have to make some room until you buy more cheap disks.
Slightly off topic, the cheap disk thing is overrated. If you're in a small to med environment you're often in the position of either having to replace all the disks or buy a new container and disks. Not so cheap.
Andy
-
Thanks for all the replies folks. The database concerned gets all it's data from a Sybase replication/Direct Connect server so backups are only taken periodically. We've switched off autoshrink and so far everything looks fine.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply