BDNA Normalize 2.x - Speeding up the RESET process
For those of you using BDNA Normalize version 2.x you may have noticed that when you're all done normalizing and you want to run normalize again, when you click on the RESET button, it can take a long time for that process to finish, especially if you have a larger SCCM environment.
ONE SIMPLE SOLUTION
When you click that RESET button, the Normalize service is doing a bunch of queries and integrity checks and deletions on the publish database and it's all recursive and thorough and can be a bit time-consuming on a large database (mine is in the 100's of gigabytes) or on a SQL server that's resource constrained (and what SQL server isn't these days!). However, depending on what your process looks like when you normalize (do you keep multiple normalize runs or just wipe everything out each time?) you can run a quick SQL statement before you click RESET in order to purge everything out of the publish db and THEN run reset. At this point SQL won't have much of anything to do so it'll go fast.
NOTE: This is really only a workable solution if you fully remove each normalize run before doing the next run. If you always keep a backup run and the current run, this solution isn't for you because it will remove EVERYTHING in the publish database.
- Open SQL Management Studio to the publish DB
- Run the following SQL statements AGAINST THE _PUBLISH DB ONLY!!
(change the code to use your _publish db, not XYZ)
DECLARE @SQL NVARCHAR(MAX)='';
SELECT @SQL = @SQL + N'TRUNCATE TABLE ' + x.TABLE_SCHEMA + '.' + x.TABLE_NAME + '; '
FROM INFORMATION_SCHEMA.TABLES x WHERE x.TABLE_TYPE = 'Base Table'
AND x.TABLE_CATALOG LIKE '%[_]PUBLISH';
EXEC sp_executesql @SQL;
And that's all there really is to it. What this code will do is
- Set the publish db as the current one
- Find all the tables in it and generate a big SQL string that looks like
TRUNCATE TABLE <abc>
TRUNCATE TABLE <xyz>
Well, at least for any base table for DBs that end in _PUBLISH
- It will execute that SQL statement and it will truncate literally EVERY table in the publish DB.
Obviously, be very careful with that code...you don't want to go emptying out your CM database or your Normalize database or some other publish database you've got on the same server. So use at your own risk and test, test, test.
Now, quick point here about TRUNCATE...TRUNCATE is a SQL command that very quickly purges every column out of a table and resets any identity columns back to their seed value. It is an unlogged event so once it's happened, there's no rolling it back. You can't undo this even if put into a transaction and rolled back. But that's what makes it so darned fast! No logging means no waiting for the transaction log to record all 5 billion rows that it's going to delete. It just wipes the rows and is done with it. So make sure this is what you want it to do.
At this point you can go back into the Normalize console and click the RESET button. It should be fast now.
Hopefully, you find this helpful!
- Created on .