Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a table with a varchar(max) column that stores text ~4-10KB in size. Data is just being added - never modified, it is a log. My problem is that a table grows too large in size - I would like to keep about 1000000 rows in this table.

I am limited to SQL Server Express and hit the limit on database size. Compression is not available in SQL Server Express and I am afraid that Filestream may not be appropriate and the number of files would be too big.

What is the best solution in this case?

share|improve this question
1  
Are you actually doing anything with this log data or do you keep growing it until you hit database size limits? So you want to keep ~ 1M rows in a table-which million and how do you determine that? – billinkc Dec 13 '12 at 3:10
Does this information need to be stored forever or can you delete older items? – Zane Dec 13 '12 at 13:31
Sometimes I have a need to show it, but I don't edit it. Old data gets deleted. The problem is that I am forced to delete it too early - I need to be able to keep it for a longer time. ~1M is a rough estimate of the number of rows the table would have if I keep it for 6 months as I would like to. – Ross Dec 13 '12 at 19:34
If you aren't on SQL Server 2012 Express edition, it might make sense to look at that. MS increased the disk size to 10GB. – billinkc Dec 14 '12 at 0:37
We are on SQL Server 2008, but the limit of 10GB is the same – Ross Dec 16 '12 at 4:17

1 Answer

up vote 1 down vote

Because this is a log of sort, and you have space limitations on your database (due to your Express edition) I would recommend either archiving your historical log data away from the database or simply deleting data that is older than a certain elapsed duration threshold. It is not uncommon to have an agreement that history is retained only for a certain time period. Just ensure this is covered in your service level agreement provided it pertains.

share|improve this answer
That is what I am doing now. But it is very much desired to keep it for longer periods of time - this is a business need. Storing historical data in a separate DB is possible, but then there would be a programming overhead to retrieve this data from 2 databases when necessary, and I could still have exactly the same problem with the second database. – Ross Dec 13 '12 at 19:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.