Commerce Server and the MSCS_CatalogScratch Database and Maintenance.

Thanks to Charles Teague for providing some of the technical detail for this article.

We’ll cover what the MSCS_CatalogScratch does in Commerce Server and how to maintain it either via VBScript or SQL Server Maintenance plans.

 

1) What’s the Catalog Scratch Database?
The Catalog Scratch Database is a database used to store the results of the queries to the Catalog System in Commerce Server.
The majority of the stored procedures will generate a table in this database.
The runtime APIs (Search, SpecificationSearch, Query, GetProducts) use this database.

The circumstances under which the Catalog Scratch database is created are detailed in the article below.

948772    The CS Catalog Scratch (MSCS_CatalogScratch) database is created when you unpack your first site resource in Commerce Server 2007
https://support.microsoft.com/default.aspx?scid=kb;EN-US;948772

This Catalog Scratch resource will be shared amongst your Catalog Resources if you have more than one.

2) Does the use of the Catalog Scratch database have any performance impact?
We discuss this in more detail in the following article.

835984    Contention may occur if only one database file is used for the Commerce MSCS_CatalogScratch database
https://support.microsoft.com/default.aspx?scid=kb;EN-US;835984

The general rule of thumb is to have as many data files as physical processors and to have the Trace Flag –1118 enabled in SQL Server.

This helps to ensure you have the best concurrency for operations on this database.

3) How do we go about maintaining this database?
As we discussed above this database is like a scratch pad for the Catalog System queries.
The tables should be empty after the stored procedures have completed but you will still be left with the tables.

So the general recommendation is to run the CatalogCleanup.vbs tool at least once a week as discussed at the following link.

How to Delete Temporary Tables from the MSCS_CatalogScratch Database
https://msdn.microsoft.com/en-us/library/bb520958(v=CS.70).aspx

4) So, what does CatalogCleanup.vbs do?
If you opened CatalogCleanup.vbs in your favourite text editor you would see that it does 2 things.

a) Check for the SQL connection string to a site Product Catalog database.

b) Call the stored procedure ‘ctlg_DropPersistentTablesInScratchDB’ in the same database.

There is also some error checking in place to handle errors.

The next thing to note is that the stored procedure is passed in a parameter which defines it’s behaviour.

Let’s look at that in more detail.

The normal execution of the stored procedure would look like this.

EXEC [dbo].[ctlg_DropPersistentTablesInScratchDB] @DropAll = 0

GO

The @DropAll parameter defines how the stored procedure removes the temporary tables.
It accepts either 0 or 1.

If you pass 0 to the stored procedure then it will delete all temporary tables in the MSCS_CatalogScratch database the currently do not have any active SPIDs.

When you use the value 1 for the parameter this tells the stored procedure to delete all temporary tables in the MSCS_CatalogScratch database. If you use the value of 1 for the parameter then you need to be aware that this may cause the stored procedure to run for a long period of time while it tries to delete the tables. And running with the parameter value set to 1 may cause problems for any catalog functions that are actually using data in the temp tables in the MSCS_CatalogScratch and the table gets deleted in the middle of the catalog operation.

So you can now run the CatalogCleanup.vbs script on the Commerce Server machine or add the stored procedure to your existing SQL Server maintenance plans.

I hope this is useful.