Link to home
Start Free TrialLog in
Avatar of Qsorb
QsorbFlag for United States of America

asked on

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.
Avatar of arnold
arnold
Flag of United States of America image

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.
Avatar of Qsorb

ASKER

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.
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.
Which version of SQL Server are you using ?  SQL Server 2008 or SQL Server 2008-R2?
Which Edition of SQL Server are you using?
Avatar of Qsorb

ASKER

> acperkins: My first sentence says it, "I’m concerned about two tables in the single database we use in sql 2008 R2."
Avatar of Qsorb

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Qsorb

ASKER

Thanks. Guess I'll use manual control for a while and if I get careless, it's autoshrink for sure.
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.
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.