0

I have a database where I'd like to delete all data from all tables. The database is full of referential integrity (foreign keys, etc), so I can't use truncate - I have to use delete.

I found this script (source: https://stackoverflow.com/a/1899881/6647188):

USE [myDatabase]
GO
EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO

However, the script fails w/ the following errors:

Msg 9002, Level 17, State 4, Line 7
The transaction log for database 'myDatabase' is full due to 'ACTIVE_TRANSACTION'.
Msg 9002, Level 17, State 4, Line 9
The transaction log for database 'myDatabase' is full due to 'ACTIVE_TRANSACTION'.

How can I rewrite the script to not fill up the transaction log? I've already tried restarting SSMS. Changing the log file to simple rather than full likely won't be easy since we don't have control of the log file (only DBA has access). However, I've looked into a concept called checkpoints, but am stuck.

Can someone alter this script so that it uses checkpoints to delete in batches then clears the log, rather than deleting it all in one go? Or provide another solution I haven't thought of?

Kyle Vassella
  • 2,296
  • 10
  • 32
  • 62
  • If this will be a regular occurrence it may make more sense to create a template database with no data. Delete the populated database as needed and create a fresh copy from the template. – HABO Jun 17 '20 at 03:04

1 Answers1

1

provide another solution I haven't thought of?

Drop all the constraints and use TRUNCATE TABLE. Then the rows won't be logged, only the extent allocations. Or script out all the objects and create them in a new database.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67