Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Sql server 2005 - Database is very large

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

Posted - 2010-10-21 : 14:54:26
15GB is a tiny database these days.

1. The DBA likely thought he/she shrunk it, but it didn't actually work.
2. It is a terrible idea to shrink it. Why bother with it? 15GB is tiny and does not occupy much space.

How much free space is there inside the database files?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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 Size

I also noticed that the initial size of the database is set to 15,297 MB.

Can the initial size be changed?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-21 : 19:49:07
Use DBCC SHRINKFILE to shrink it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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 ...
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-23 : 14:53:44
That is not true. Run DBCC SHRINKFILE to shrink it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

bonekrusher
Starting Member

44 Posts

Posted - 2010-10-25 : 14:13:37
Thank you Tara -
Go to Top of Page

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.

Go to Top of Page

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"
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-09 : 12:58:04
I think the title is perfect!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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"
Go to Top of Page

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!
Go to Top of Page

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 ...
Go to Top of Page

bonekrusher
Starting Member

44 Posts

Posted - 2010-11-10 : 09:23:54
LOL - I agree. Thanks again for all the help.
Go to Top of Page
   

- Advertisement -