mdf and ldf file size not increasing

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

SQL Server 2005 Forums

SQL Server Administration (2005)

mdf and ldf file size not increasing

Author

Topic

aam874
Starting Member

4 Posts

Posted - 2008-06-18 : 10:19:28

Hello, relatively new to SQL Server.

I have a database with 1 .mdf data file and 1 .ldf t-log file. There are multiple inserts/deletes/transactions performed on the data daily, but the size of the two files remains constant (5,774,,336 and 153,480 respectively)??? I perform daily full backups and hourly T-log backups (during business hours of 9-6) and these backup files change size, but why aren't my physical DB files changing? I have them set to auto-grow at 10% unrestricted...

Thanks for your help/advice

Arthur

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 10:22:02

They will grow only when needed.
How much is actually used in the files? How much space is taken/occupied?

E 12°55'05.25"
N 56°04'39.16"

aam874
Starting Member

4 Posts

Posted - 2008-06-18 : 10:25:55

Thanks for the reply Peso,

How do I find out "how much is actually used in the files" ?

The size of the mdf file on disk says 5,774,336 KB -- are you saying that not all of that space is being used by the database?

Again, thanks for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 10:48:14

Run "exec sp_spaceused" in the database you mention above.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 11:05:49

My testdatabase shows this information

database_name	database_size	unallocated space
Test		2549.44 MB	457.23 MB

reserved	data		index_size	unused
177936 KB	108976 KB	68680 KB	280 KB

E 12°55'05.25"
N 56°04'39.16"

aam874
Starting Member

4 Posts

Posted - 2008-06-18 : 11:23:56

I get:

database_name database_size unallocated space
MyDatabase 5788.88 MB 1140.91 MB

reserved data index_size unused
4606040 KB 1857048 KB 2736944 KB 12048 KB

I'm going to make an assumption and say that, until my 1140MB of unallocated space is used (by inserted data, etc.) that my .mdf file will stay the same size, then it will grow by 10% (approximately 500MB) when it needs more space? Please correct me if I have this wrong...trying to learn as much as I can in my new role :-)

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-06-18 : 11:38:24

You may also want to set the autogrowth increment to a flat rate, rather than a percentage. When the database grows next, it will grow by about 500 MB. Next time, it will grow by about 550 MB, and so on. Each time, it will take a little longer to grow. Ideally, you should keep tabs on the used space to grow the database when only a few folks are using it, as database growth tends to be disk intensive.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-18 : 11:44:51

Agree with Mcrowley. You shouldn't go with percentages especially with big database as it grows with percentage rate.

aam874
Starting Member

4 Posts

Posted - 2008-06-18 : 15:07:45

Yes, I was wondering about that. I think you are both right that a constant grow size would be better.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 15:37:55

So you have over 1 GB free space in your file, 20%.
Which growth rate do you expect?