| Author |
Topic |
|
jfk_lili
Starting Member
5 Posts |
 Posted - 2006-02-03 : 04:55:19
|
| Hi guys,I have read the psat posts. I know that Shrink is a bad practice.But I still want to get clarified, if the SHrink option has been turned on default?And If the free space exceed 25%, MSDE will perform a Shrink.Is there any way to configure it to others like 30%?And as I read from the past posts that the DEFRAG will be a good practice. But if there will be a option to choose the DEFRAG will be Auto performed. Because my aim is not to have a DBA to really look after the stuff.Many thanks for your input!!! |
|
|
jfk_lili
Starting Member
5 Posts |
Posted - 2006-02-09 : 08:57:00
|
| .....Why nobody reply....:( |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-09 : 12:00:28
|
| "I know that Shrink is a bad practice"..."Is there any way to configure it [AutoShrink] to others like 30%?"So why would you want to do that?"But if there will be a option to choose the DEFRAG will be Auto performed"I've answered in the other thread where you asked this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58982"Why nobody reply"Because the people who read this thread so far (there are only about 10 so far) did not have a suggestion ... ??Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-02-09 : 12:37:57
|
| why worry about autoshrink at all? shrinking the database is really only used for 2 reasons (that I can think of)1. you are running out of space2. you want to move your database to another hostshrinking the db will lead to many problems including increased disk i/o which can cause perf prblems as well as heavily fragmented datafiles - which also leads to perf problems.If you are looking for "defrag" scripts check out tara's blog. SHe has a couple of scripts that implement DBCC DBREINDEX and INDEXDEFRAG that are quite useful http://weblogs.sqlteam.com/tarad/. You can also use the maintenance plan wizard and set up an optimization job which will do something similar. Actually, I am not sure if MSDE has the maintenance plan wizard, if not then Tara's scripts are the way to go.btw, MSDE also defaults the databses to autoclose which is another setting you should disable. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-09 : 14:08:32
|
| 3. You did a one-off delete of a massive amount of old, stale, data.But even with 1 & 3 I would think twice about doing it. And for 2 you need to find a different solution - like buying more hard disks!Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-02-09 : 15:14:53
|
quote:
Originally posted by Kristen
3. You did a one-off delete of a massive amount of old, stale, data.But even with 1 & 3 I would think twice about doing it. And for 2 you need to find a different solution - like buying more hard disks!Kristen
I will usually shrink a database if I need to move the database to another host - if we are retiring a server or creating a DEV/TEST/QA instance for example. Instead of copying 100+ GB, if I can just copy 60GB I'll do it.But you are right, running a srhink on a database should be a rare occurance and you never should have autoshrink enabled.-ec |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-09 : 18:22:29
|
| "I will usually shrink a database if I need to move the database to another host"Yeah, me too of course. However, I question the wisdom - its only going to grow again, and fragment ... whereas (presumably) if I move the 100GB file (with 40GB free) to the new host the O/S will allocate contiguous disk space [if available].And the backup is only going to be 60GB ...(it is however noticeable, IME, that restoring a 60GB backup made from a 100GB database takes longer than a 60GB backup made from a 60GB [shrunk] database, and for 40GB of "slack" the time is considerable)Kristen |
|
|
|
jfk_lili
Starting Member
5 Posts |
Posted - 2006-02-10 : 06:15:49
|
| Hi Guys, thanks so much for the overwhelming response!!!But for the 2GB limit...is it apply to situation that runnign out off space :p |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-10 : 06:48:27
|
| SQL server will "reuse" any spare space in the database.So if your database is, say 1GB but only 900MB is "used" then there is a spare 100MB which will be used first. When that is full SQL Server will extend the database.For MSDE once the database is extended to 2GB it cannot be extended any more. Then you need to delete some data (but still you don't need to shrink it because the "free space" left by the deleted data will be reused.Kristen |
|
|
|
|
The Shrink Option has been Default on in MSDE?