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 |
|
Dunnes2002
Starting Member
2 Posts |
 Posted - 2010-09-14 : 03:43:29
|
| Hi,im having problems with a table i delete from and add to each week when a process runs. Basically it contains the previous 4 weeks data, before the weekly updates are added it deletes the oldest weeks columns (8 in total) and then adds the latest weeks columns in.The table also has a cluster index on an account number which is removed before the deletion and recreated after the latest weeks data is added.The problem is that the table grows in size each week from around 500mb when originally created and adds about 200mb each week. When i copy the table into another the new table is 500mb. It would seem that its not deleted the records from its memory (just like access) but i didnt think SQL Server was supposed to do this.Is there an easy was of compacting the table or is my only option to copy the table, delete the old etc?Cheersfor your helpMike |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-14 : 03:52:01
|
| Are you using Rebuild Index routine periodically?There is a SHRINK routine but you should avoid using this - the database will shrink, fragmenting your indexes and then the file will regrow fragmenting the physical file.However, once you get the system under control you may want to se SHRINK as a one-off to get the physical file size down to a sensible size (i.e. including space allowed for growth in the short to medium term) |
 |
|
|
naveengopinathasari
Yak Posting Veteran
60 Posts |
Posted - 2010-09-14 : 04:07:34
|
| If your are trying to delete all the data from the table try to use TRUNCATE so that memory info is been purged.Lets unLearn |
|
|
|
Dunnes2002
Starting Member
2 Posts |
Posted - 2010-09-14 : 04:30:25
|
quote:
Are you using Rebuild Index routine periodically?There is a SHRINK routine but you should avoid using this - the database will shrink, fragmenting your indexes and then the file will regrow fragmenting the physical file.However, once you get the system under control you may want to se SHRINK as a one-off to get the physical file size down to a sensible size (i.e. including space allowed for growth in the short to medium term)
I use the delete and create statements in the code to deal with the index, not sure about the rebuild routine? I will look into thisquote:
Originally posted by naveengopinathasari
If your are trying to delete all the data from the table try to use TRUNCATE so that memory info is been purged.Lets unLearn
No i only delete 8 columns of data, there are still 24 columns that are left untouched, so i didnt believe this would be of any use.Cheersfor the replies |
|
|
|
|
|
|
SQL 2005 copmpact Table?