Share via

Things you should know about the MSCS_CatalogScratch database

  • Article

The Product team received a number of queries regarding the MSCS_CatalogScratch database and so I thought I should write about it. Simply put the MSCS_CatalogScratch database is a scratchpad database (much like tempdb) which contains empty tables.These tables are used to hold intermediate results when running complex queries on the product catalog system. The reason behind creating a separate database as against using the tempdb was that the tempdb gets created everytime sql server is restarted and this means the permissions to create the tables have to be re-applied.

Things you should know about the MSCS_CatalogScratch database:

  1. Do not use this database for your internal use. Do not rename or drop this database.
  2. If you have to drop and recreate this database set the Recovery Model to Simple and uncheck the "Torn Page Detection" check box.
  3. This database is shared by all the product catalog databases installed on the server.
  4. When not in use the tables in this database are empty. These tables hold intermediate results of complex queries and are truncated when the results of the query are returned.
  5. You should periodically run the CatalogCleanUp.vbs script in the “Program Files\Microsoft Commerce Server 2002\Support” folder of your Commerce Server installation to physically drop these tables.
  6. The sql accounts accessing your Product Catalog system should be members of the db_ddladmin, db_datareader and db_datawriter roles in the MSCS_CatalogScratch database. They should not be added to the db_owner role to minimize any security risks.
  7. Finally, it is possible for this database to experience contention when used heavily, especially if there is a lot of traffic on your runtime site. If you experience any blocking issues with this database you should increase the number of datafiles with equal sizing.For eg you can create four data files each with a size of 1GB. See this link which deals with blocking issues with tempdb https://support.microsoft.com/?id=328551. The same issues also apply to the MSCS_CatalogScratch database