Solved

Controlling size of SQL 2008 tables

Posted on 2013-01-10
10
313 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
  • 4
  • 3
  • 3
10 Comments
 
LVL 76

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 76

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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 76

Accepted Solution

by:
arnold earned 500 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now