Overview
To prevent accidental data loss, deleting a table in project within the Jet Data Manager does not delete the physical table in the SQL Server database. The downside is that tables deleted in the JDM application will still take up space in the database. A tool has been added in Jet Enterprise 2016 to provide the ability to identify tables left behind by the JDM that can be deleted to free up space and declutter the physical SQL database.
How-To Video
This video demonstrates how to use the SQL Database Cleanup Wizard to free up space and declutter the physical SQL database
Process
To use the database cleanup feature, follow these steps:
-
In the project tree, right click on the data warehouse, click on Advanced and click on SQL Database Cleanup Wizard. The JDM will read the objects from the database and open the SQL Database Cleanup window.
-
In the SQL Database Cleanup window, the content of the database is listed. Expand the root node, [database name] ([SQL Server Version]), to display the categories of content found on the second level. Depending on the content, the following categories are displayed:
• Tables: Tables in the database unrelated to any JDM project.
• Views: Views in the database unrelated to any JDM project.
• Procedures: Procedures in the database unrelated to any JDM project.
• Functions: Functions in the database unrelated to any JDM project.
• Deleted Project Object: Objects deleted from the project, but not from the database. These should be safe to drop.
• Project Objects (<Unresolved Project: [project id]>): Objects in the database related to an unknown JDM project, such as a project not in the current repository.
• Project Objects ([name of project]): Objects in the database related to a project in the current repository.
-
Expand Project Objects to display a list of Object IDs in the project. If you expand an Object ID, the tables, views etc. related to the object are listed. For example, if the object is a table, the valid, raw and other instances of the table are listed.
-
Right click on a table, view, procedure or function and click on Script to display the SQL script behind the object.
-
Right click on a table, view, procedure or table and click on Drop to drop the object from the database. Click Yes, when the JDM asks you to confirm the drop. A message will be displayed confirming if the DROP action succeeded or failed.
-
Right click on an Object ID or a Project Objects item and click on Drop to drop the object and all objects on the levels below. A window will open with a list of the objects that will be dropped. Clear the selection for any tables you wish to keep and click on Drop. Note: The JDM will automatically clear the selection for any incrementally loaded tables or tables with history enabled to prevent accidental data loss. The JDM will ask to confirm if the user wishes to drop those table types.
-
When all of the objects to be removed have been dropped from the database the user can close the window.
Comments