Microsoft KB Archive/835984

Knowledge Base

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

Article ID: 835984

Article Last Modified on 11/15/2007

---

APPLIES TO

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

---

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 Web sites 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 the following script in SQL Query Analyzer:

   dbcc traceon(1118,-1)
   
    
    DROP DATABASE MSCS_CatalogScratch
    
   IF NOT EXISTS (Select '*' From master..sysdatabases Where Name = 'MSCS_CatalogScratch')
   BEGIN
           CREATE DATABASE MSCS_CatalogScratch
   ON
   PRIMARY ( 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',false
   GO
   -- 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)
   END
   GO

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 FIX: Concurrency enhancements for the tempdb database

Keywords: kbprb KB835984

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.