Contention may occur if only one database file is used for the Commerce MSCS_CatalogScratch database

This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
If you use one database file for the Commerce MSCS_CatalogScratch database, access to the database file may experience contention. This contention occurs on websites that have heavy traffic and where catalog searching frequently occurs.

Note The Commerce MSCS_CatalogScratch database is the default database in Microsoft Commerce Server 2002.
RESOLUTION
To resolve this problem, create multiple database files for the Commerce MSCS_CatalogScratch database. To create multiple database files for the database, follow these steps:
  1. Stop the Microsoft Internet Information Services (IIS) service on each web server that uses SQL Server Commerce databases.
  2. Before you drop the catalog databases, note the users and the permissions that are associated with the databases.

    Note You must re-create the users and the permissions after you re-create the database.
  3. Run one of the following scripts in SQL Query Analyzer, depending on the version of Microsoft Commerce Server that you are using.

    Commerce Server 2002

    dbcc traceon(1118,-1)  DROP DATABASE MSCS_CatalogScratch IF NOT EXISTS (Select '*' From master..sysdatabases Where Name = 'MSCS_CatalogScratch')BEGIN        CREATE DATABASE MSCS_CatalogScratchONPRIMARY ( NAME = MSCS_CatalogScratch1,      FILENAME = 'c:\program files\microsoft sql server\mssql\data\MSCS_CatalogScratch_data1.mdf',      SIZE = 1000MB),( NAME = MSCS_CatalogScratch2,   FILENAME = 'c:\program files\microsoft sql server\mssql\data\MSCS_CatalogScratch_data2.ndf',   SIZE = 1000MB),( NAME = MSCS_CatalogScratch3,   FILENAME = 'c:\program files\microsoft sql server\mssql\data\MSCS_CatalogScratch_data3.ndf',   SIZE = 1000MB),( NAME = MSCS_CatalogScratch4,   FILENAME = 'c:\program files\microsoft sql server\mssql\data\MSCS_CatalogScratch_data4.ndf',   SIZE = 1000MB)LOG ON ( NAME = MSCS_CatalogScratch_log,   FILENAME = 'c:\program files\microsoft sql server\mssql\data\MSCS_CatalogScratch_log1.ldf',   SIZE = 100MB) END --Set the recovery model to simple for the MSCS_CatalogScratch database.ALTER DATABASE MSCS_CatalogScratch SET RECOVERY Simple-- Set the 'torn page detection' option to false because this is a scratch database.EXEC sp_dboption 'MSCS_CatalogScratch','torn page detection',falseGO-- Create the CTLG_PropertyTableMap table IF NOT EXISTS ( Select '*'                From [MSCS_CatalogScratch]..sysobjects                where name = 'CTLG_PropertyTableMap'                and type = 'U')BEGIN               CREATE TABLE [MSCS_CatalogScratch]..CTLG_PropertyTableMap               (                              PropertiesToReturn nvarchar(450) NULL,                       LongPropertiesToReturn nvarchar(4000) NULL,                       Spid           int,                       Tableid        int identity PRIMARY KEY                )               CREATE INDEX [CTLG_PropertyTableMap_NDX] ON [MSCS_CatalogScratch]..CTLG_PropertyTableMap(PropertiesToReturn)ENDGO

    Commerce Server 2007

    dbcc traceon(1118,-1)  DROP DATABASE MSCS_CatalogScratch IF NOT EXISTS (Select '*' From master..sysdatabases Where Name = 'MSCS_CatalogScratch')BEGIN        CREATE DATABASE MSCS_CatalogScratchONPRIMARY ( NAME = MSCS_CatalogScratch1,      FILENAME = 'c:\program files\microsoft sql server\mssql\data\MSCS_CatalogScratch_data1.mdf',      SIZE = 1000MB),( NAME = MSCS_CatalogScratch2,   FILENAME = 'c:\program files\microsoft sql server\mssql\data\MSCS_CatalogScratch_data2.ndf',   SIZE = 1000MB),( NAME = MSCS_CatalogScratch3,   FILENAME = 'c:\program files\microsoft sql server\mssql\data\MSCS_CatalogScratch_data3.ndf',   SIZE = 1000MB),( NAME = MSCS_CatalogScratch4,   FILENAME = 'c:\program files\microsoft sql server\mssql\data\MSCS_CatalogScratch_data4.ndf',   SIZE = 1000MB)LOG ON ( NAME = MSCS_CatalogScratch_log,   FILENAME = 'c:\program files\microsoft sql server\mssql\data\MSCS_CatalogScratch_log1.ldf',   SIZE = 100MB) END --Set the recovery model to simple for the MSCS_CatalogScratch database.ALTER DATABASE MSCS_CatalogScratch SET RECOVERY Simple-- Set the 'torn page detection' option to false because this is a scratch database.EXEC sp_dboption 'MSCS_CatalogScratch','torn page detection',falseGO-- Create the CTLG_PropertyTableMap table IF NOT EXISTS ( Select '*'                From [MSCS_CatalogScratch]..sysobjects                where name = 'CTLG_PropertyTableMap'                and type = 'U')BEGIN               CREATE TABLE [MSCS_CatalogScratch]..CTLG_PropertyTableMap               (                              PropertiesToReturn nvarchar(450) NULL,                       LongPropertiesToReturn CatalogDynamicSqlStatement               NULL,                       Spid           int,                       Tableid        int identity PRIMARY KEY                )               CREATE INDEX [CTLG_PropertyTableMap_NDX] ON [MSCS_CatalogScratch]..CTLG_PropertyTableMap(PropertiesToReturn)ENDGO
  4. Re-create the users and the permissions for the database.
  5. Restart the IIS service on each web server that uses SQL Server Commerce databases.
For more information about the trace flag, click the following article number to view the article in the Microsoft Knowledge Base:
328551 Concurrency enhancements for the tempdb database
Properties

Article ID: 835984 - Last Review: 12/08/2015 06:12:11 - Revision: 2.0

Microsoft Commerce Server 2002 Standard Edition, Microsoft Commerce Server 2007 Developer Edition, Microsoft Commerce Server 2007 Enterprise Edition, Microsoft Commerce Server 2007 Standard Edition

  • kbnosurvey kbarchive kbprb KB835984
Feedback