| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2007-11-15 : 10:04:20
|
| Guys,Is there a way to compact the database? We do many inserts and deletes, where #inserts approximately equals #deletes,and yet the database size constantly keeps growing. I feel like there are a lot of holes in the database file, which, if gotten rid of, should decrease the overall database file size.I know that I can shrink the database file(s), but not sure if it is same as compacting it.Any ideas?Thanks a lot |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-11-15 : 11:18:12
|
| Try rebuilding a clustered index.try using dbcc showcontig to see if you have wasted space in the tables.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-15 : 11:21:53
|
| Record the size with sp_spaceused. Then run DBCC DBREINDEX on your tables and then DBCC UPDATEUSAGE on your database. Next run sp_spaceused again and verify if the size is smaller. If you'd like for us to take a look, post the results of sp_spaceused before and after the two DBCC commands are run.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
dirtydavey
Yak Posting Veteran
80 Posts |
Posted - 2007-11-15 : 11:32:04
|
| and maybe a DBCC SHRINKDATABASE if you feel its needed |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-15 : 11:37:58
|
quote:
Originally posted by dirtydavey
and maybe a DBCC SHRINKDATABASE if you feel its needed
Noooooo! Only do this if you know you no longer need the space in the near future. Shrinking the database or the files is pointless if you need the space again since it'll just expand again when it is needed. So we first need to work on the results of sp_spaceused, monitor that for about a week, then decide if the space can be reclaimed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
dirtydavey
Yak Posting Veteran
80 Posts |
Posted - 2007-11-16 : 04:16:00
|
Like I say, only do this if you think its needed. But it is a way to shrink the database down.Why would this be a bad idea. I would have thought that if they are doing the same amount of deletes as inserts doing a shrink at this point would give them the avarage size of there databse and would be unlikly to grow much more then this? And would be a good place to start.I totaly agree that shrinking files only to let them grow again is a pointless idea but as a one off and a way to get them to a sensibale size database is it bad. I am asking cos I dont know?quote:
Originally posted by tkizer
quote:
Originally posted by dirtydavey
and maybe a DBCC SHRINKDATABASE if you feel its needed
Noooooo! Only do this if you know you no longer need the space in the near future. Shrinking the database or the files is pointless if you need the space again since it'll just expand again when it is needed. So we first need to work on the results of sp_spaceused, monitor that for about a week, then decide if the space can be reclaimed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
|
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-11-16 : 17:04:15
|
| Guys,I did use DBCC DBREINDEX and it seemed to have worked great. I recorded the space before and after and this seems to have cut down the space by almost 50%, which is a huge gain for us. I will monitor it from now on to see whether it grows back in the next few days, or will be growing slowly.I have a question though: Why exactly does this operation cut down the space used? Anywhere I read about it, it just says that this operation will improve performance and nowhere did it mention that it will decrease the space used. How exactly is the space usage decreased? Is it by compacting the data and getting rid of the holes (defragmentation)?Thanks a lot! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-16 : 17:09:32
|
| Because your indexes were fragmented and using more pages than needed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-11-19 : 14:00:19
|
| I was surprised because we run INDEXDEFRAG every 8 hours to defragment all the indexes. Shouldn't that have taken care of this issue as well or do these two (i.e. INDEXDEFRAG and DBREINDEX) work differently in this regard?Thanks a lot |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Compacting the database