Situation

When using Subset or Privacy on a Microsoft Sql Server database you might encounter performance problems or transaction logs filling up.

Might it help to do something with the recovery models Microsoft Sql Server supports?


Explanation

Yes.

Microsft Sql Server supports three types of recovery models

Thes models help you to save/restore data in case of crashes.


  • Simple, no logging.
  • Bulk_logged, minimal logging for bulk updates. No point in time recovery.
  • Full, maximal logging, Point in time recovery.

See: https://msdn.microsoft.com/en-us/library/ms189275.aspx


Subset

Using Subset you will not create data but you copy data. That means that in most cases you can repeat your actions and never lose data.

For that reason it is recommende to use the Simple recovery model during subsetting.

After subsetting it is recommended to set recovery (back) to FULL.

(info) When using append or unique append you will need backups before running Subset. This will enable restore the (target) database when it crashes during Subsetting.


Privacy

Using Privacy temporary tables are created en filled with data. Normally it is not required to keep the logging for these temporary tables.

For that reason it is recommende to use the Simple recovery model during anonimizing.

After anonimization it is recommended to set recovery (back) to FULL.


You need to have alter database permission to execute:

   use master; 
   alter database <DBNAME> set recovery SIMPLE;