We have a large Clarion Application that we have been using for over 20 years that runs our service, accounting, billing, and also provides a GL.
The developer passed away earlier this year and MSSQL Express has hit the 10GB wall.
We need someone who can help us purge some of the excess data from Express to keep things working while we expand to SQL standard or move to a supportable Application structure, ideally Clarion. Currently we do not have the source code, but we are attempting to get it. Need someone that understands Clarion when interfaced using SQL, as well as SQL EXPRESS for helping reduce the data file sizes. Thank you …
Where abouts in the world are you located?
Its 10GB per database, you can create another database so this could be an “archive” database, so you dont have to delete the records, just move the oldest records from your “live” database into the “archive” database.
The archive db could be a rolling (Today - 5 years) or (Financial Year - 5 years) database.
You could also have multiple archive databases where each archive database covers a 5 year period.
eg
Live = (Financial Year1,2,3,4,5)
Archive1 = (Financial Year - 5 years (Years 6-10))
Archive2 = (Financial Year - 10 years (Years 11-15))
Archive3 = (Financial Year - 15 years (Years 16-20))
Likewise Clarion can handle multiple data sources easily, just duplicate the tables/files in the dct and give them a driver string which points to the relevant archive database. It will mean duplicating parts of your existing app, ie duplicating windows and reports so you can access the “archive” data, or just change the data source via a drop down from Live, to Archive1, Archive2 etc.
Depending on your windows and reports, you could combine the data from Live, Archive1, Archive2 & Archive3 into the workstations memory using the IMDD, if the need to show/report data across a wider timespan is required, and thats just another way.
I’m in the UK and can do some contracting or onsite if you want to PM me.
Been using MS SQL with Clarion since MS SBS2003 Premium edition and written stored procedures and MS SQL views.
I work late so can work Pacific time remotely if required.
And if you cant get the source, I could rebuild it using Ghidra and port it into the Clarion language and app/txa format, if there’s a budget for it.
Hi!
All the solutions you’ve been offered are valid. But to start, you should check the usage of each table in MS-Server Management Studio and address the issue starting with the ones that take up the most space, which are likely to be historical data.
Are there images/photo/Documents stored inside the application? If so, do you know if those files are stored inside the database?
You can move those files to outside the database using MS SQL’s File Stream feature. The files/data in File Stream do NOT count towards the 10GB limit of Express.
As @Gustavo_Saracca said, it maybe helpful to determine which table in your database is consuming the most space. You can use this query:
--http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
5 DESC
2 Likes
We are in Southern California
Not too familiar with MSSQL Express, in fact I never used it, only the full featured MS SQL, but from experience with any database engines if you use it for a long time they can have a lot of bloat - Is there such a thing as database file bloating? – SQLServerCentral Forums . If you rebuild your indexes or dump your database into a text (sql) file and import it back you may find that it uses a lot less space.
MSSQL Express has a lot of per design hard coded limitations so it may not be easy to perform such operation, but setting up a Windows Server with MSSQL should not take that long. You could even use a server in the cloud, lots of companies offering that.
Since you say you don’t have the source code I think it would be the best approach. It’s easy to delete records from a database, but not knowing the structure you risk loosing referential integrity. Moreover, deleting records will not shrink the size unless you as well do the maintenance of the database. I think at this stage you need MS SQL DBA rather than Clarion developer. You will of course need one once you obtain the source code, but make sure that you also get a copy of your developer environment as there would probably be custom templates in use. Another Clarion developer not having these templates may have a problem figuring out what they (templates) do. So in the next compile your app can loose some features.
Hi,
While SQL Server Express does have some limitations—such as a 10 GB maximum database size and restrictions on CPU and RAM usage—these don’t prevent you from doing essential maintenance tasks like rebuilding indexes or shrinking the database. You can still use tools like SQL Server Management Studio (SSMS) or T-SQL scripts to manage these tasks, just like with the full version of SQL Server.
Shrinking a database can be useful when you want to release unused space back to the operating system, but just a heads-up—this process can sometimes cause file fragmentation, so it’s best to use it cautiously.
If you want to go ahead, here’s how you can shrink the database (you’ll need sufficient access rights):
- Using SQL Server Management Studio (SSMS):
- Open SSMS and connect to your SQL Server Express instance.
- In Object Explorer, expand the Databases node.
- Right-click on the database you want to shrink and select Tasks > Shrink > Database.
- In the Shrink Database dialog box, you’ll be able to see the space available for reclaiming. Once you’re happy, click OK to shrink the database.
Using a T-SQL Script:
- You can also shrink the database using a T-SQL command:
sql
DBCC SHRINKDATABASE ('YourDatabaseName')
This will shrink both the data and log files of the specified database.
1 Like
If you cant get smss to work check the ports are enabled as its installed as a desktop db so the ports werent enabled by default then you can use smss to connect and thus control it.