Table dependencies during truncation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table dependencies during truncation

Author

Topic

Rahul Raj
Starting Member

41 Posts

Posted - 2015-03-05 : 21:21:57

Hi All,

I am performing a truncate operation on many tables but need to retain some records. Below is my approach :

- copy the data to be retained into another table
- disable the constraints
- truncate the table
- enable the constraint
- copy the data to the original table

The issue is that, we need to delete the records from the child table as well to main the referential integrity. I am not able to find the constraints on the table. Used the below query but it didn't give any constraints inspite the table has a referential constraint.
SELECT
OBJECT_NAME(f.parent_object_id) TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM
sys.foreign_keys AS f
INNER JOIN
sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
sys.tables t
ON t.OBJECT_ID = fc.referenced_object_id
WHERE
OBJECT_NAME (f.referenced_object_id) = 'YourTableName'

Can someone please help me with a query to identify the constraints along with the child table name.

Also, I don't have the data model thereby what should be my approach for identifying the table dependencies so that the constraints are maintained while copying back the records.

Thanks in Advance!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-03-05 : 21:34:21

Here's a script to delete the tables in correct order:

http://sqlblog.com/blogs/jamie_thomson/archive/2011/09/19/delete-all-data-from-a-database.aspx

You'd have to modify it to only do certain tables. It might be easier to modify the code to print out the SQL statements rather than execute them, and then copy/paste to a new window in SSMS where you can run the individual statements manually.

Rahul Raj
Starting Member

41 Posts

Posted - 2015-03-05 : 23:06:50

Hi Rob,

Many Thanks for the script!

But I would not be able to use this script due to the following reasons :
- The data to be deleted is huge ~ 350GB and the transaction log will get full.
- The delete would take lot of time around a day, and thereby we prefer to truncate the data after taking a back-up of the data to be retained as it is faster.

As I am new to sql thereby can someone please help to provide the query to identify the constraints and the dependent table so that all the unwanted data gets deleted without effecting the integrity.

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-03-06 : 07:05:50

Here's something to help find tables you can truncate:

WITH no_kids(ID) AS (SELECT object_id FROM sys.tables
 EXCEPT
 SELECT referenced_object_id FROM sys.foreign_keys)
SELECT N'TRUNCATE TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(ID)) + N'.' + QUOTENAME(OBJECT_NAME(ID))
FROM no_kids

Copy/paste the results to a new window and run selectively.

For larger tables that you need to delete, (and assuming you can't switch to SIMPLE recovery) you have 2 options:

1. Delete in small batches (10-100K rows, using DELETE TOP(N)) and do log backups after a certain number of batches, or :
2. Script out the foreign key definitions, drop them, truncate the tables, then re-create them.

I've done both options, and the 2nd one was also suggested in the link I provided. That seems to be the direction you originally wanted to go.

A third option might be better, now that I think about it:

- script out table definitions and create them in a new empty database
- DO NOT include indexes or any constraints (script them but don't apply them until later)
- copy over data you want to keep (use SIMPLE recovery on new database, and CHECKPOINT after each table is inserted)
- add indexes and constraints
- swap new and old database names (change recovery model to FULL if needed)

This lets you start fresh, keeping the old database online at all times, and minimizes transaction log activity. If something goes wrong you don't need to rollback, just start over. Switching database names takes a few seconds, so practically zero downtime.

The new database should have a properly sized transaction log to prevent it from growing during the import, and again use SIMPLE recovery with frequent CHECKPOINT statements.

Rahul Raj
Starting Member

41 Posts

Posted - 2015-03-06 : 13:53:53

Thanks a lot Rob for your valuable time!

Delete is not being preferred because it takes one full day for the processing to complete.

I will try to follow the third approach provided by you. I have the following queries for this approach :

- how can we ensure that the RI is maintained and all the required data is copied.(some tables have identity columns as well)
- How would the database swap work for SP and other objects apart from tables.

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-03-07 : 12:17:19

With the third option, referential integrity is actually controlled in the original database, you are in essence copying only valid data. Once everything is copied to the new database, adding the constraints will re-validate it. As long as they pass, your RI is valid.

Regarding identity columns, just use SET IDENTITY_INSERT ON for each table that needs inserting, then set it off when finished.

All other database objects like functions, procedures, etc. can be scripted and added after everything else is copied. User defined data types and bound defaults have to be added first if they are used in your table definitions.

Database swapping goes like this:

- Change name of old database to something else, e.g. SwapDB or ArchiveDB
- Change name of new database to match old database name

You may have to use ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE to kick users out in order to rename databases, then SET MULTI_USER afterward.

Rahul Raj
Starting Member

41 Posts

Posted - 2015-03-08 : 13:35:44

Hi Rob,

Many thanks for providing this useful approach and your valuable time. Can you please help me with the below queries :

1) I scripted the database for tables but there were other objects as well such as Stored Procedures, Database Roles,Schema,User defined Data types,User defined functions,users. As mentioned by you, I would be adding the User defined Datatypes before scripting the tables and then once the data is copied; everything else(other objects) can be copied.

2) I am not sure whether I would be able to run the scripts as it involves creating Stored procedures in the adm schema as well and I won't be having the sufficient privileges for this. Can provide the script to the DBA.

3) How will the system stored procedures be scripted.

4) All the tables from which the data needs to be deleted have no dependencies(used your no_kids query to check the same), and thereby I will be copying the selected data into the new database. The other tables which I do not want to delete can be scripted separately (with data) but I think unnecessary parent records would still exist as I am only deleting from the No dependency tables with huge data.

5) I have selected script indexes and check contraint to TRUE while scripting ,but how can these be ignored during the data copy and added later.

6) What should be my testing approach for this.I would have to compare the data of all the tables in new and old databases. How would the object permissions be replicated. Do I need to set "script object level permissions to TRUE" during scripting. Any other thing which I am missing here.

Thanks in advance

Rahul Raj
Starting Member

41 Posts

Posted - 2015-03-12 : 22:15:18

Hi Rob,

We are not planning to go with the last approach because of the following reasons :

1) All data would required to be copied
2) permissions on the object would required to be defined
3) We would not have sufficicent privledges to execute the scripts and create database.

Basically, it will be a good approach but we have found some pre-defined SP which deletes/Truncates the tables.

Many Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources