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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-10-04 : 07:40:46
|
| Vinson writes "Hello, I have just setup a site at http://autopinger.com that does pinging for blogs and podcasts automatically. I m providing some simple stats to my users every 30 mins when their blogs are crawled. In just 1.5 months, the database grew to 1.2GB with default settings in SQL server 2005. Till then, my site will go down at times. I tried to shrink the database and rebuild index and purge out any history that are 2 days old but the database size doesn't seem to be shrinking at all. I don't understand why is that I had drastically deleted a lot of data but the database size doesn't go down. Infact after deletion, the total number of records inside will be less than 5000 records and why is the size still at 1.2GB? Is there something that I m missing or may I know how does the database size grow in details? Please kindly enlighten me, thank you." |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-04 : 07:59:02
|
| Truncate the log file too, after a successful backup.Peter LarssonHelsingborg, Sweden |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-04 : 08:22:49
|
| SQL Server retains allocated space to the database. This is helpful if you deleted some and add some - the space within the file just gets reused. Extending the file is very "expensive", so SQL Server tries to avoid this.However, if you make an exceptional one-off deletion the database could usefully be reduced in size, but SQL Server does not know that new data won't be arriving soon! - so you will have to do this manually.Use the SHRINK command (enterprise manager : Right click database : tasks : ShrinkCheck the option to "move pages to front of the file"Note that you should NOT routinely shrink your database, but usually only after exceptional deletions. SQL Server will have to extend the database as soon as more data arrives, and this is an "expensive" task.It would be best if you set up a scheduled tasks to delete stale data - e.g. history that is > 2 days old. That way your database size will stay in equilibrium.Kristen |
|
|
|
|
|
|
|
|
SQL database growing too fast