sql serversql-server-express
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 afraid that Filestream may not be appropriate and the number of files would be too big.
What is the best solution in this case?
I finally found the cause of my problem!
I tried for months to track down the issue, but finally gave up, disabled automatic change tracking, just manually initiated incremental population and moved on with my life.
Meanwhile, there had been another nagging error that I was having trouble tracking down. Periodically the website would throw a DB connection error:
Cannot open database "XXXX" requested by the login. The login failed. Login failed for user 'XXXX'.
It turned out that both of these problems had the same solution. All I had to do is turn off a database setting called Auto Close. To do this, just right-click the database and click properties. In the properties window select Options and set "Auto Close" to false.
As soon as I disabled Auto Close, my DB login issues went away and the automatic change tracking worked perfectly.
Thanks again for everyone's help. I appreciate it!
For your case and reasons to upgrade
merge replication is available in SQL Server CE 3.5
Stored procedures, views, triggers is not available in SQL Server CE 3.5
for a basic comparison between the 2 editions in your case working on a net book
1. SQL Server CE 3.5 can Runs as in-process with application SQL Server Express runs as a service
2. SQL CE supports maximum size of 4GB SQL Express can go up to 10GB
3. SQL CE Doesn't Support FILESTREAM SQL Express Supports FILESTREAM
5. Distributed transactions is not supported in SQL CE but supported in SQL express
7. Stored procedures, views, triggers is not supported in SQL CE but supported in SQL express
9. concurrent connections supported in SQL CE is limited to 100 but unlimited in sql express
this is a useful blog focused on sql CE
for the comparison between the 2 in your specific environment you can use these tools
SQL LOAD TEST SQL STRESS TEST TOOL
and check this post on SO about sql statment performance https://stackoverflow.com/questions/1555471/what-tools-are-available-to-test-sql-statement-performance
Best Answer
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.