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.
| Author |
Topic |
|
MelvinT
Starting Member
5 Posts |
 Posted - 2006-03-29 : 20:45:32
|
Hi! Everybody there!! I am faced some problem for SQL SERVER!Database size info:>> Current Size: 4.1GB>> Space Used: 3.7GBYesterday, I had shrink the database from 4.1GB to 3.7GB. >> Current Size: 3.7GB>> Space Used: 3.7GBToday, i checked it again, found out that the database was increase to 4.5GB. >> Current Size: 4.5GB>> Space Used: 3.7GBIs that SQL SERVER is running in this way? it is behaviour action? But my other database did not have this problem happenPlease help!!!  Thank youMelvin |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-03-29 : 21:37:44
|
| Well, if a SQL Server database runs out of space, it will automatically grow itself. The default growth rate is 10%. So, your 3.7GB database should grow by 370MB. Then, it probably grew at least once more to get you to 4.5GB. The growth could be in your data file or transaction log.As you add data to the system, or run a large transaction your databases will grow automatically as needed. In general, shrinking the database will only cause it to grow as soon as more data is put into the system. You can adjust the amount that the database grows by each time. Some people reccomend somewhere around 100-250MB's for smaller low volume databases and maybe as much as 500MB's for larger high volume databases. These numbers are just ballpark numbers for you to look at. It really all depends on how much data you are putting into the system and how often etc.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
 |
|
|
MelvinT
Starting Member
5 Posts |
Posted - 2006-03-31 : 22:14:22
|
| Thank you!If let say i schedule the SQL SERVER running Shrink database for every day. Any one can give me some Prof. advice for it? I do not whether my suggestion is good solution or not?Thanks!Melvin |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-04-02 : 23:15:47
|
| Well, from what I've read and been told by other experts, doing a "daily shrink" isn't really that healthy for the database. If you think about it, let's say you shrink the database down at midnight every day. As soon as you start putting data in the database again, it's going to grow again. Then shrink at midnight, then grow the next day, and so on.So, I think you need to ask yourself this:What do I hope to gain by shrinking my database?? If you are that tight on disk space, you should look at doing some disk cleanup or adding more disks. If you are hoping to gain some performance, I doubt you'll see measurable gains unless you select the "reorganize pages" option.I think you just need to change the amount that your database grows when it does need to grow. It's growing for a really good reason, so you need to let it do it, but let it do it a bit more gracefully than 400-500MB's at a time. Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
|
|
|
|
|
Database Size in SQL SERVER