dbcc | compiler using the mpc parser combinator library

You simply need to wrap your "delete from all the tables" script with a "drop all foreign keys" script at the beginning, and "re-create all foreign keys" script at the end. I show one way to do that here:

• Drop and Re-Create All Foreign Key Constraints in SQL Server

However, I would argue that it is much cleaner to just script out the database and empty objects from source control than spend all this time and effort deleting data from one table at a time.

Anyway an attempt at what you're doing (if you truncate you don't also need to checkident, I'm not sure I would ever use undocumented and unsupported procedures like sp_msforeachtable, and I also stay the heck away from INFORMATION_SCHEMA). Please try this on a test database.

As I informed in the main post, after a while, the size of our database file log reached about 25 gigs and we could no longer even compress the database files. After some searching, I came to the conclusion that I should back up the log file and then compress the log file. For this purpose, I defined a job that prepares a file from a log backup file almost every 30 minutes, and the size of these files usually does not exceed 150 MB. Then, after each backup of the log file, I run the log compression command once. With this method, the size of the log file is greatly reduced and now we have about 500 MB of log file. Of course, due to the large number of transactions on the database, the mentioned job must always be active. If the job is not active, I will increase the log volume again.

Question 1

I think the problem is around the OR predicates inside the parenthesis in each condition, I believe it should be an AND predicate like shown below:

The problem is that you fetch a row just after you open the cursor, but don't do anything with the fetched values. So you end up missing the first row. To correct that, reorganize your code this way:

Normally, aggregation is much slower than not aggregating. However, moving the final result set out of the database also takes time.

The time spent returning 1,450,000 rows is clearly much greater than returning 24 rows. I would speculate that this accounts for much of the difference in your case. In fact, it probably accounts for more of the difference, because the aggregation should be slower.

Aggregation can make use of indexes on the tables, so your database might also have indexes that optimize the aggregation.

The problem is your understanding; the code is very likely working exactly as it is supposed to, and as I demonstrated.. RESEED resets the value of the next IDENTITY generated, it doesn't change any of the existing values.

Take SQL similar to what I gave in the comments:

These are just standard exception errors that you can capture using the CommonParameter -ErrorVariable or by using the automatic variable $Error.

Below is an example of using ErrorVariable. We tell Invoke-SqlCmd to store any errors encountered in a variable called 'dbCheckErrors'. We later check if this variable $dbCheckErrors contains any ErrorRecords and if it does we collect all the Exception messages into our $errorCollection array for later access. Once done with our db checks we can either view the messages, save the messages into file, both, or whatever else.

VERIFY_CLONEDB isn't supported in the version you are using. From the documentation:

VERIFY_CLONEDB
Verifies the consistency of the new database. This option is required if the cloned database is intended for production use. Enabling VERIFY_CLONEDB also disables statistics and query store collection, thus it is equivalent to running WITH VERIFY_CLONEDB, NO_STATISTICS, NO_QUERYSTORE. This option is available starting with SQL Server 2014 (12.x) SP3, SQL Server 2016 (13.x) SP2, and SQL Server 2017 (14.x) CU8.

You need to install at least CU8 to take advantage of this feature (we're on CU25 at the moment).

To get back, directly in your program, the results of a DBCC command you need to subscribe to the InfoMessage event of the SqlConnection class.