In SQL Server, the TRUNCATE TABLE statement can be used to delete all rows from a table without logging the individual row deletions. However, it only works if the table doesn’t have any foreign key constraints.

Here’s how you can use it:

TRUNCATE TABLE YourTable

Please replace YourTable with the actual name of your table.

Please note that TRUNCATE TABLE is a minimally logged operation and it will deallocate the data pages used by the table, which can result in much less log space usage compared to the DELETE statement.

However, TRUNCATE TABLE has restrictions. For example, you cannot use it if the table is referenced by a foreign key or if the table is part of an indexed view. In such cases, you would need to use the DELETE statement instead and potentially consider managing the transaction log space manually.

If your table has foreign keys, you cannot use the TRUNCATE TABLE statement directly. However, you can still minimize the log space usage by deleting the rows in smaller batches in a loop. This can be combined with the CHECKPOINT statement (in simple recovery model) or a backup of the transaction log (in full or bulk-logged recovery models) to free up log space.

DECLARE @RowsDeleted INT = 1;
WHILE @RowsDeleted > 0
BEGIN
    DELETE TOP (1000) FROM YourTable;
    SET @RowsDeleted = @@ROWCOUNT;
    -- If your database is in simple recovery model, you can use CHECKPOINT to free up log space
    -- CHECKPOINT;
    -- If your database is in full or bulk-logged recovery models, you should backup the transaction log to free up log space
    -- BACKUP LOG YourDatabase TO DISK = 'PathToYourBackupFile';
END

If you want to delete rows by controlling you can use this way:

DECLARE @count INT = 1;
WHILE @count < 20
BEGIN
    DELETE TOP (1000) FROM YourTable ;
    CHECKPOINT;
    SET @count = @count + 1
END

This query will delete 20.000 rows thousand by thousand