Starcounter only deletes data when explicitly told to. In code, data is deleted with the
Delete method or with
DELETE SQL queries, read more in Data manipulation - delete.
For example, if a database class is deleted from the code, the underlying table and the data in that table will not be deleted even if it's not referenced by any database class. The reason for this is that there's no way for Starcounter to determine if the data is abandoned and will never be used again.
To delete tables and columns that will not be used, you can mark that they can be dropped with SQL and then delete them by executing the command
staradmin start cleaner.
staradmin start cleaner launches the cleaning process by looking up all
DROP TABLE or
ALTER TABLE commands that were executed since the last time it ran. It will run until all those commands have been processed and then terminate. This will permanently delete the tables and columns.
Data that is deleted with
start cleaner can't be recovered. Backup your data before deleting anything to have a safe point to revert to.
Tables should be dropped if it's not referenced by a database class and the data in the table is not useful anymore. The reason to do this is to free up memory. For this example, we have a table
UselessTable that is not needed anymore:
[Database]public class UselessTable{public string Nothing { get; set; }}
In order to delete a table, the database class that references it has to be deleted. Thus, we delete the class
UselessTable from the code and all the references to the class.
To delete the reference between the database class and the table, rebuild and restart the application. If you don't do this, the Administrator will throw
ScErrColumnIsMapped (SCERR15014).
To mark that the underlying table should be deleted with all its data, run this command in the Administrator:
DROP TABLE MyApp.UselessTable
If you try to run
DROP TABLE without deleting all the references to the table in all the apps that are running, Starcounter will throw
ScErrTableIsReferenced (SCERR15012).
To permanently delete
UselessTable, execute
staradmin start cleaner, when it's finished, it will print:
Done (nothing more to clean).
If you add
UselessTable back to the code, executing
SELECT * FROM MyApp.UselessTable will yield no results. The data has successfully been deleted.
Running
staradmin start cleaner might produce transaction conflicts with running applications, thus, run the cleaning when the load is low to avoid performance drops.
Deleting unused columns works similarly to how unused tables are deleted: a SQL command is used to mark that it should be dropped, and
staradmin start cleaner deletes the data and column that has been marked.
For this example,
IrrelevantFact should be deleted from the
Person table:
[Database]public class Person{public string Name { get; set; }public string IrrelevantFact { get; set; }}
To drop a column, the corresponding property has to be deleted. In this case, the class will then look like this:
[Database]public class Person{public string Name { get; set; }}
To delete the reference between the property and the column, rebuild and restart the application. If you don't do this, the Administrator will throw
ScErrColumnIsMapped (SCERR15014).
Since the
ALTER TABLE command can't be executed while the table it alters is referenced in any running app - you have to stop the app before running
ALTER TABLE, you can do this by running
staradmin stop app MyApp.
In the Administrator, run this query to mark that the column should be dropped:
ALTER TABLE MyApp.Person DROP COLUMN IrrelevantFact
To delete the data and the column, run this command:
staradmin start cleaner
After this, the column is deleted and all the data that was stored in it is permanently deleted.
Data definition language queries (DDL) are executed in the Administrator to delete tables or columns. Running the queries by themselves does not delete the data - you also have to run the cleaner to delete the data. To run DDL queries with either
DROP COLUMN or
DROP TABLE, you can't have the affected tables loaded. This means that the apps with references to those tables can't run unless the references to the tables are removed in the app.
To help you with find unused tables and columns you can use the tool WhatsNotReferenced. It checks, when you start the database, for all tables and columns that are not in use by any currently running application
Again, be careful when permanently deleting data. If the data is of any value, you should make a backup before cleaning it.