Is this normal, large file size?

Question

Is this normal, large file size?

dharris-623527

Mr or Mrs. 500

Points: 544
More actions
January 29, 2008 at 7:37 am

#127648

Hi,
I work for a software company, and we have webapp that connects to a MS SQL 2005 database. The database without data is usually around 20MB for the MDF file and around 7 for the LDF file.
A developer recently updated the application which included alot of database changes. What I'm finding weird is, our database size now when it has no data is:
MDF - 2.5 gigs
LDF - 6.5 gigs
Is this normal, seems really large for a starting database.
Hope someone can help.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

Well, it sounds like the dev just grew the data to that size at one point and that's now the new minimum size of the file. Same with the log. I wouldn't worry about it really unless you just need the space. If you do, just do a shrinkfile on each of them and you should get your space back.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

dharris-623527 Mr or Mrs. 500 Points: 544 More actions · Answer 2

Thanks for the reply. The reason I brought this up was the application seemed little slower, wasn't sure if this was the cause. Either way I'll do a shrink on the database.

Thanks again.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 3

Y, having extra empty space in your DB wouldn't slow down your app.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

dharris (1/29/2008)
Thanks for the reply. The reason I brought this up was the application seemed little slower, wasn't sure if this was the cause. Either way I'll do a shrink on the database.
Thanks again.

I'd steer clear of shrinking databases. They have a tendency to make a mess of the "insides" of that database, to the point that it's recommended that you do a rebuild on all indexes on all tables if you do shrink the DB.

You may see that you speed comes back if you simply rebuild those indexes in there.

Like crever pointed out, databases LIKE to have some free space, so don't starve it. Auto-grow is the stuff that's actually fairly BAD for your database's performance, so give your data and logs a lot of room to grow into.

----------------------------------------------------------------------------------
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?

dharris-623527 Mr or Mrs. 500 Points: 544 More actions · Answer 5

oh ok. I'll take your advice. I think the developer has some bugs in the code causing the slowdown. He introduced STRUTS into the java app, so I'm thinking there maybe an issue there. I'll do some more investigating and see what I can find.

Thanks again for your help guys.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

Shrinking a db should be a very rare event, usually in response to a one time load. Keep some pad in your database to accomodate data growth, as Matt mentioned.

The log grows depending on data changes. If you had a large load, it might have grown larger than you normally need and you could shrink just that file (s). However keep some pad in your log as well. You don't want it shrinking and growing on a regular basis.

dharris-623527 Mr or Mrs. 500 Points: 544 More actions · Answer 7

One more question associated with this. If you look at the properties of the database theres an option to "AutoShrink", whats the best practice for this?

Thanks,

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Turn Auto-Shrink off (or - leave it off since that's the default setting). If you DO have to shrink a database, at least you should decide when you are going to do it. Shrinking either type of file will at very least cause a major slowdown and/or dopr connections, etc..., so you should do that at a non-peak time (especially since you're going to have to do all of those rebuilds we mentioned.) you have no guarantees when Auto-shrink will "decide" to start that process.

----------------------------------------------------------------------------------
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?