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

RESOLUTION

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.