Microsoft KB Archive/835984

= 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:  Stop the Microsoft Internet Information Services (IIS) service on each Web server that uses SQL Server Commerce databases. 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.  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  Re-create the users and the permissions for the database. 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.