Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Controlling size of SQL 2008 tables

Posted on 2013-01-10
10
Medium Priority
?
323 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 80

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 80

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 80

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Screencast - Getting to Know the Pipeline
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

773 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