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 |
|
nhess80
Yak Posting Veteran
83 Posts |
 Posted - 2009-02-16 : 14:57:44
|
| I know that auto shrink has been covered in this forum and it is said not to use it but I need your opinions on how I should handle what I am doing. It seems that shrinking the db would be good in my situation and if it is not I was hoping to get some guidance on how I should handle this.I deal with large amount of data and the files that hold the data that I do a bulk insert into my database are usually around 100mb and about 500,000 rows. These files are created daily and inserted weekly. So on Mondays I am inserting around 3.5 million rows. Now not all of this data is kept. The data is first inserted into a holding table and after that is done a query is run to grab the data that is needed and move that to another table. After that is done the holding table is truncated. This is done every week. My question is, is this something that I should use auto shrink or should I still not use this? I was trying to figure out a way to just shrink the holding table because I don’t want to fragment other tables in the DB. Any help would be great.Thanks,Nick |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2009-02-16 : 16:02:26
|
| Ok thank you for the quick response. I was just under the impression that it would keep getting bigger every time I inserted that data. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-02-16 : 20:09:39
|
| You can add a filegroup to your database and add a new data file to it specifically for this holding table. If you can place the file on a disk that is physically separate from the rest of your database it should also improve bulk loading performance. Either way, having a separate file and filegroup for bulk loading will reduce or even prevent other tables from being fragmented. |
|
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2009-02-17 : 12:27:06
|
Do you know of a good place that I can read about adding a filegroup? I have never done anything like that beforequote:
Originally posted by robvolk
You can add a filegroup to your database and add a new data file to it specifically for this holding table. If you can place the file on a disk that is physically separate from the rest of your database it should also improve bulk loading performance. Either way, having a separate file and filegroup for bulk loading will reduce or even prevent other tables from being fragmented.
|
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-02-17 : 12:55:04
|
| Books on line covers this pretty thoroughly. You can also right click the database, choose Properties and you'll have a filegroup section where you can add it.Mike"oh, that monkey is going to pay" |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-17 : 14:09:38
|
| Also moving clustered index to other filegroup will move table as well but not with non-clustered index. |
|
|
|
|
|
|
Best Practice - AutoShrink