[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Controlling size of SQL 2008 tables

I’m concerned about two tables in the single database we use in sql 2008 R2. Table named “FEEDS” holds the temporary news feeds. The Title and Selections from FEEDS are then copied and saved in table NEWS that holds 300,000 or so news stories.

Table FEEDS is mainly for reference as we copy only the Title, RSSLINK, Source, and PUBLISHEDDATE. I don't need the CONTENT.

This news site is located on a CF hosting site and we’re limited to 5000MB for ONE database. Table FEEDS is disposable and it would grow in size far too large if we did not manually delete entries in the FEEDS table. But until those records are shrunk, the database grows too quickly.

We can use autoshrink but I’m told that’s not a good method and our CF site frowns on using it. I’m looking for some other methods to control the size of the FEEDS table. I have to live with the fact we’ll probably need to manually control the NEWS table so the database does not grow too large so my main concern is the expendable table, FEEDS.

My problem is that I only have experience with SQL 2000. I’m not looking for links to help or information files. I’d like your ideas, so please don’t answer unless you understand and have some ideas how SQL2008 can better be managed than SQL2000 in this regard.  This will be used with CF10.
0
Qsorb
Asked:
Qsorb
  • 4
  • 3
  • 3
1 Solution
 
arnoldCommented:
Do you have an option to use temporary tables?you can define a global ##temptable that you can access from more than the process that created it.
http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/

Alternatively, the manner in which you create/populate this table could be streamlined to only maintain the data you need.
0
 
QsorbAuthor Commented:
I'll ask our hosting provider about Temporary Tables in SQL Server. If not guess we'll need to more seriously consider using autoshrink on a daily basis. Any feelings or suggestions about using autoshrink?

My description of the question isn't quite correct or rather, up to date.
 
We use CFFEED to process feeds from many different news categories, such as SPORTS. There may be 10 to 40 feeditems or so for each category, and each is inserted into a table column, checking that there are no previous stories with the same title in the same category for the last x amount of days. Seems to work fine.

Then we use CFHTTP to get the RSSLINK for each story, parse through it for the news we want, and save it to the same ID and TITLE as for the original news feed via CFFEED.  We may later delete or mark OFFLINE stories based on content, size, etc. As the day processes, about half of the original news feeds are not used. Obviously that creates unnecessary file size on the database before being shrunk.

I thought about using two different tables, or just two different columns instead of using the same column for the news feeds. Again, we had planned to simply delete the news feed rows we did not use then later recovering the disc space after shrinking.

Hope that gives you a better idea of what I'm doing, and any suggestions still so welcome.
0
 
arnoldCommented:
Auto shrink will have a rather unwanted impact on the performance once during the shrink and multiple times during the expansion. I.e. depending on your setting % or XMB.
An alternate approach might be to allow the first person requesting the feed encounter a delay while he data is retrieved.
This way you will retrieve data only when there is an interest.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Anthony PerkinsCommented:
Which version of SQL Server are you using ?  SQL Server 2008 or SQL Server 2008-R2?
Which Edition of SQL Server are you using?
0
 
QsorbAuthor Commented:
> acperkins: My first sentence says it, "I’m concerned about two tables in the single database we use in sql 2008 R2."
0
 
QsorbAuthor Commented:
Arnold:

>Auto shrink will have a rather unwanted impact on the performance once during the shrink.

That's what I read. But if I autoshrink at 3am, might not be too noticeable.

>  and multiple times during the expansion
Expansion? Have no idea what that means.

> . I.e. depending on your setting % or XMB.
That is completely over my head; setting, %, XMB.

>An alternate approach might be to allow the first person requesting the feed encounter a delay while he data is retrieved.
Wondering, what you mean, what kind of delay, how? Not at all sure about this at all, being the novice I am. Would you give me a brief overview of what you mean?
0
 
arnoldCommented:
After the shrink down.  As you add data the size of the database is expanded by either percentages I.e.10% or fixed 10MB so you add 10/100 records and every 10-20 rows the sql server pauses and expands the database.
An analogy with auto-shrink  is like a balloon. When you need to add data you have to put the effort in to pushing in air to expand it before to can add.
The delayis proportional to the amount of space by which it needs to expand.

As long as you delete data that s unneeded, the space within the database is made available.  All the auto shrink function does is reduce the file size to match the amount of data in the database.
0
 
QsorbAuthor Commented:
Thanks. Guess I'll use manual control for a while and if I get careless, it's autoshrink for sure.
0
 
Anthony PerkinsCommented:
Simply put if you don't care about the performance in your database then by all means use Auto-Shrink.  If on the other hand this is a Production database with a lot of use and you decide to use Auto-Shrink, then make sure you have you have your resume updated, as it is a recipe for disaster.
0
 
Anthony PerkinsCommented:
My first sentence says it, "I’m concerned about two tables in the single database we use in sql 2008 R2."
And my second sentence read what Edition of SQL Server are you using?  If it is Standard or Enterprise then there are alternatives such as database compression.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now