We have setup a SQLExpress database about 15 days ago. The database size has already grown to over 1GB. I know the size limit on a SQLExpress DB is 10 GB. Is there any documentation we can look at to trim this and is there a way that SQL will automatically prune data? We have pruning enabled in the gateway and set to 1 year. Obviously, we are not going to make it to one year. Any suggestions are appreciated.
If possible, consider switching to a more competent free database, such as postgreSQL. Max db size is >100TB, and there is plenty of support available from large companies that are unlikely to go belly up(which is the main concern people have with these types of software)
If you are otherwise stuck with MSSQL, you are stuck with creating tasks in event scheduler to execute stored procedures/SQL scripts in your server via batch file.
Edit: SQL express doesn't have SQL Server Agent
3 Likes
Would be able to pull our SQL DB into postgreSQL?
You should be able to, but there may be a bit of conversion work to be done as the base column types don't line up one-to-one. Generally you would have to export from SQL express (normally into a CSV) and them import into postgres.
1 Like
I agree with Ryan, but regardless of which DB engine you're using it can pay to keep an eye on usage. I wrote an app to monitor our DB use at a weekly meeting. It was quite handy - but it's not ready for distribution!
The app has (1) a dropdown to select the database to be monitored, (2) a date picker and (3) a duration dropdown.
Dropdown options named query binding
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE 'hp_%'
ORDER BY schema_name
Dropdown options query binding transform
def transform(self, value, quality, timestamp):
objOptions = [{"label": 'default', "value": 'YourSchemaName'}]
for row in value:
objOptions.append({"label": row['schema_name'], "value": row['schema_name']})
return objOptions
Table Top 100 Historical Tags named query
SELECT a.tagid, COUNT(a.tagid) AS records, b.tagpath
FROM {schemaName}.sqlt_data_1_{tableYear}_{tableMonth} a
INNER JOIN {schemaName}.sqlth_te b ON a.tagid = b.id
WHERE a.t_stamp > (UNIX_TIMESTAMP(:dateTimeStart) * 1000)
AND a.t_stamp < (UNIX_TIMESTAMP(:dateTimeEnd) * 1000)
GROUP BY a.tagid
ORDER BY records DESC , a.tagid
LIMIT 0 , 100
Parameters for the table named query binding:
{schemaName}: returned by dropdown (1).
You should be able to figure out the rest.
We used the table to target tags above some nominal threshold and usually we found a stupid deadband setting which, when corrected, greatly reduced the data per day.
5 Likes
I think it would be a good candidate for the Exchange.
3 Likes