Use SQL Database Cleanup Wizard

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.

To watch the video on how to SQL Database Cleanup Wizard to free up space and declutter the physical SQL database, see The SQL Database Cleanup Wizard.

To use the database cleanup feature, follow these steps:

1. In the project tree, right-click the data warehouse, click Advanced and click SQL Database Cleanup Wizard. The JDM will read the objects from the database and open the SQL Database Cleanup window.

   

2. 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.

   

3. 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.

4. Right-click a table, view, procedure or function and click Script to display the SQL script behind the object.

5. Right-click a table, view, procedure or table and click 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.

6. Right click an Object ID or a Project Objects item and click 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 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.

7. When all of the objects to be removed have been dropped from the database the user can close the window.