Author |
Topic |
bonekrusher
Starting Member
44 Posts |
Posted - 2010-10-21 : 14:46:56
|
Hi, I have a database that holds about 100mb of data, but the size used is about 15gb. Autogrowth is set to 'by 10mb, unrestricted growth'. The DBA before me said he shrunk the database but then the file grew right back to 15gb.Two part question:1. how do I prevent this from happening?2. How do I shrink the database (I read shrinking the database is not a good idea?) |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
bonekrusher
Starting Member
44 Posts |
Posted - 2010-10-21 : 15:16:13
|
Thanks for helping.The database shows:15086.70 MB Space Available.15319.56 MB SizeI also noticed that the initial size of the database is set to 15,297 MB.Can the initial size be changed? |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
Kristen
Test
22859 Posts |
Posted - 2010-10-22 : 02:03:07
|
"15GB is tiny and does not occupy much space"Whilst true I do think its a lot for a 100MB database ... |
 |
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2010-10-22 : 08:59:30
|
After the shrink occurs, be sure to rebuild your indexes as they are no longer going to be optimal.Terry-- You can't be late until you show up. |
 |
|
bonekrusher
Starting Member
44 Posts |
Posted - 2010-10-22 : 11:30:44
|
Thanks for all the comments. But I believe my problem is that the database was created with an initial size of 15,297 MB. So even if I shrink it, it will not shrink below the initial size. I think I am going to have to backup the database, create a new one and import all the data...Thoughts? |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
bonekrusher
Starting Member
44 Posts |
Posted - 2010-10-25 : 14:13:37
|
Thank you Tara - |
 |
|
bonekrusher
Starting Member
44 Posts |
Posted - 2010-11-09 : 08:32:31
|
Just to revisit this question. I found a very good article on the subject of SHRINKFILE "Why SHRINKFILE is a very bad thing, and what to do about it."[url]http://www.sqlservercentral.com/articles/SHRINKFILE/71414/ [/url]The title is misleading, but a very helpful article. |
 |
|
Kristen
Test
22859 Posts |
Posted - 2010-11-09 : 10:15:30
|
"The title is misleading, ..."I think the title is spot on! The article is describing that SHRINK should only be used as a one-time task, not as a routine procedure. For you its a one-time procedure, for many/most people who ask questions here about SHRINK its because they are using it as a routine procedure.The article says:"Shrinking a database file (either using SSMS or T-SQL code) should be avoided if at all possible""the downside is that you just fragmented a lot of your data.""nce the SHRINKFILE has completed, identify the Indexes that need to be rebuilt / reorganized and run that process. .... This will also have the negative impact of growing your file again, though hopefully not as much as you just shrank it by""This [is] just enough to bring to your attention that SHRINKFILE, SHRINKDATABASE and AutoShrink are potentially very bad things to do to a database" |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
bonekrusher
Starting Member
44 Posts |
Posted - 2010-11-09 : 15:07:22
|
sort of like:"You should never hit the panic button, but if you have to, press it only once" |
 |
|
Kristen
Test
22859 Posts |
Posted - 2010-11-10 : 02:39:15
|
Yeah, although I would rephrase slightly to "Don't press the panic button unless you know you will only need to press it once"Just so long as you aren't using SHRINK repeatedly then its fine - you had a genuine, one-off, need to shrink the database.I have thought that I did in the past, but when a short while later the size problem re-materialised then I went looking for the real cause! |
 |
|
Kristen
Test
22859 Posts |
Posted - 2010-11-10 : 02:44:30
|
Actually, re-reading that, maybe its the exact same thing you said. Nuance ... so hard to convey ... |
 |
|
bonekrusher
Starting Member
44 Posts |
Posted - 2010-11-10 : 09:23:54
|
LOL - I agree. Thanks again for all the help. |
 |
|
|