Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Controlling size of SQL 2008 tables

Posted on 2013-01-10
10
Medium Priority
?
320 Views
Last Modified: 2013-01-11
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
Comment
Question by:Qsorb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 79

Expert Comment

by:arnold
ID: 38766082
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
 

Author Comment

by:Qsorb
ID: 38768597
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
 
LVL 79

Expert Comment

by:arnold
ID: 38769084
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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38769204
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
 

Author Comment

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

Author Comment

by:Qsorb
ID: 38769277
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
 
LVL 79

Accepted Solution

by:
arnold earned 2000 total points
ID: 38769289
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
 

Author Closing Comment

by:Qsorb
ID: 38769332
Thanks. Guess I'll use manual control for a while and if I get careless, it's autoshrink for sure.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38769350
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38769353
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question