In one of my more popular blog posts I told you Why using gs.sql is a BAD IDEA! Well today, I'm here to tell you a bit about why using gs.truncateTable is also a bad idea. The use of gs.sql has been deprecated as of the Geneva Release, due to the amount of damage (and outages) it has caused.
Whilst reading some of the community posts here recently, I have come across several posts, where Community Members have been recommending the use of the function: gs.truncateTable('TABLE_NAME'); to "clean up data" on tables. Again, like gs.sql, this an UNDOCUMENTED feature and should NOT be used under any circumstances.
If ServiceNow does not document a function, it is because:
- ServiceNow does not support it.
- ServiceNow can not offer support for it if it is used.
- ServiceNow does not want to encourage use of it.
gs.truncateTable is not supported by ServiceNow when truncating tables because...
- If any record elsewhere in the database, references any record in the table that you are truncating, you will potentially "break" your instance.
- Could result in orphan ("ghost") records in the database, or potentially cause an outage!
Due to the way that ServiceNow relates tables, when using the recommended methods, we carry out a cascade delete, which removes records correctly. This is just one scenario where the use of this function should be avoided at all costs. There are always ways of achieving your requirements within the product and information for how to carry these out are available in our product documentation.
To properly delete a custom table:
- Navigate to System Definition > Tables.
- Open the table to delete. [Recommended] Click Delete All Records.
- Click Delete.
- In the confirmation dialog box, enter delete and click OK.
The table and all items that reference the table will be removed.
To properly delete all records from a table:
- Navigate to System Definition > Tables and Columns.
- Select the table for which to delete records.
- Click Delete all records.
- In the confirmation dialog box, enter delete and click OK.
Always make sure you are 100% positive you do not need the records from this table. Once completed, the table will still exist and any references to the table on other tables (such as business rules or reference fields) are preserved, but the records will be gone.
Another recommendation is to use a Table Cleaner to remove data. This can be scheduled to run out of hours, and use a filter to safely remove the data from a specific table, for example to delete all records of a specific class, that are over a thousand days old.
It can be tempting to use alternative unsupported methods to achieve what you want but there are always repercussions. ServiceNow Technical Support is always here to guide you through fixing your instance and to achieve your requirements, should there be any ambiguity or concerns.
1 Comment
