DMAR330

Recently, when performing a catalog search for subcategories, I got the following message:

Could not allocate new page for database 'MSCS_CatalogScratch'. There are no more pages available in filegroup PRIMARY. Space can be created by dropping objects, adding additional files, or allowing file growth.

Upon looking in the database, I saw literally hundreds of tables with results for this search, or results for this SPID. Since uor App has a treeview representing catalogs, and population on demand, we use the catalog search to retrieve each categorie's child categories and products, sometimes variants, etc..

How can I keep this database clean so that 2 months into production I don't get this error why doesn't the database keep itself in check if these tables are categorized by SPID I would assume that they aren't hanging around for optimization sake.

Any thoughts

Thanks,

Dave




Re: Commerce Server 2007 MSCS_CatalogScratch why does it get do full?

Noureddine ZEGHOUGH

Hello DMAR330,

MCSC_CatalogScatch database contains tables that are used to hold intermediate results when runing complex queries on the product catalog.

They are normally truncated when the result is returned back.

It is recommended de run regularly the CatalogCleanup.vbs to drop these tables (I use it only on my Cs2002 environment, I can't find it in Cs2007 and I don't know if It Applies).

I can't attach this file to this post so here is the source code:

' =================================================================
' This script will update all the fulltext catalogs used by the catalog system
' =================================================================
On Error Resume Next

' =============================================================
' BEGIN: LOCALIZATION NEEDED FOR THESE STRINGS
const L_Title_text = " Microsoft Commerce Server 2002 "
const L_Usage_text = " La syntaxe d'usage pour ce script est la suivante : "
const L_Usage1_text = " CatalogCleanUp.vbs ""chaine de connexion a la base de donnees MSCS_CatalogScratch"" "
const L_ErrorNumber_text = "Numero d'erreur : "
const L_Description_text = "Description : "
const L_FailedCreateWscript_text = "Impossible de creer WScript.Shell"
const L_CreateConnectionFailed_text = "Echec de CreateObject AdoDb.Connection"
const L_InvalidConnectionString_text = "Chaine de connexion non valide"
const L_FailedScriptExecution_text = "Une erreur s'est produite lors de l'execution du script SQL"
const L_Success_text = "L'operation s'est terminee correctement"
' END: LOCALIZATION NEEDED FOR THESE STRINGS
' =============================================================

' Global Variables
Dim AdoConnection
Dim ConnectionString
Dim SQLIds_Rs
Dim CommandText
Dim FtsProjectName
Dim WshShell
Dim WshArgs


Dim SQl
SQl = " SET NOCOUNT ON " _
& vbLF & " DECLARE @TableName_tmp sysname" _
& vbLF & " DECLARE @Tablespid_tmp int" _
& vbLF & " DECLARE @TableId int" _
& vbLF & " DECLARE @TableId_startpos int" _
& vbLF & " DECLARE @TableId_endpos int" _
& vbLF & " DECLARE @Query_tmp nvarchar(1000)" _
& vbLF & " DECLARE @SortedTable_tmp sysname" _
& vbLF & " DECLARE @length int " _
& vbLF & " IF EXISTS (Select '*' From tempdb..sysobjects" _
& vbLF & " where id =object_id(N'tempdb..#Catalog__PersistentTables'))" _
& vbLF & " BEGIN" _
& vbLF & " DROP TABLE #Catalog__PersistentTables" _
& vbLF & " END" _
& vbLF & " SELECT Name As TableName" _
& vbLF & " INTO #Catalog__PersistentTables" _
& vbLF & " From sysobjects" _
& vbLF & " where name like N'Catalog[_][_]%[_][_]for[_]spid[_][_]%'" _
& vbLF & " WHILE (1=1)" _
& vbLF & " BEGIN" _
& vbLF & " SET ROWCOUNT 1" _
& vbLF & " SELECT @TableName_tmp = TableName" _
& vbLF & " From #Catalog__PersistentTables" _
& vbLF & " IF @@rowcount = 0" _
& vbLF & " BEGIN" _
& vbLF & " SET ROWCOUNT 0" _
& vbLF & " BREAK" _
& vbLF & " END" _
& vbLF & " DELETE #Catalog__PersistentTables" _
& vbLF & " SET ROWCOUNT 0" _
& vbLF & " SET @Tablespid_tmp = convert(int, Substring(@TableName_tmp, charindex(N'__for_spid__', @TableName_tmp)+LEN(N'__for_spid__'),20))" _
& vbLF & " IF (NOT EXISTS (Select '*' From master..sysprocesses Where spid = @Tablespid_tmp))" _
& vbLF & " BEGIN" _
& vbLF & "IF EXISTS (Select '*' From sysobjects" _
& vbLF & "where id = object_id(@TableName_tmp) and Type ='U')" _
& vbLF & " EXEC (N'DROP TABLE
'+@TableName_tmp)" _
& vbLF & " END" _
& vbLF & " END" _
& vbLF & " TRUNCATE TABLE #Catalog__PersistentTables" _
& vbLF & " INSERT #Catalog__PersistentTables(TableName)" _
& vbLF & " SELECT Name As TableName" _
& vbLF & " From sysobjects" _
& vbLF & " where name like N'Catalog[_]Multiple[_][_]Catalog[_][_]Results%'" _
& vbLF & " WHILE (1=1)" _
& vbLF & " BEGIN" _
& vbLF & " SET ROWCOUNT 1" _
& vbLF & " SELECT @TableName_tmp = TableName" _
& vbLF & " From #Catalog__PersistentTables" _
& vbLF & " IF @@rowcount = 0" _
& vbLF & " BEGIN" _
& vbLF & " SET ROWCOUNT 0" _
& vbLF & " BREAK" _
& vbLF & " END" _
& vbLF & " DELETE #Catalog__PersistentTables" _
& vbLF & " SET ROWCOUNT 0" _
& vbLF & " SET @Tablespid_tmp = convert(int, Substring(@TableName_tmp, charindex(N'__for_spid__', @TableName_tmp)+LEN(N'__for_spid__'),20))" _
& vbLF & " IF (NOT EXISTS (Select '*' From master..sysprocesses Where spid = @Tablespid_tmp))" _
& vbLF & " BEGIN" _
& vbLF & " SET @SortedTable_tmp = N'Catalog_Multiple__Catalog__SortedResults_'+LTRIM(RTRIM(Str(@Tablespid_tmp)))" _
& vbLF & " IF EXISTS (Select '*' From sysobjects" _
& vbLF & " where id = object_id(@SortedTable_tmp)" _
& vbLF & " )" _
& vbLF & " BEGIN" _
& vbLF & " EXEC (N'DROP TABLE
'+@SortedTable_tmp)" _
& vbLF & " END" _
& vbLF & "IF EXISTS (Select '*' From sysobjects" _
& vbLF & "where id = object_id(@TableName_tmp) and Type ='U')" _
& vbLF & " EXEC (N'DROP TABLE
'+@TableName_tmp)" _
& vbLF & " SELECT @length =len('Catalog_Multiple__Catalog__Results_')+1 " _
& vbLF & " SELECT @tableid_startpos = charindex('_',@TableName_tmp,@length) " _
& vbLF & " SELECT @tableid_endpos = charindex('_',@TableName_tmp,@tableid_startpos+1) " _
& vbLF & " IF ( (@tableid_startpos >0) AND (@tableid_endpos>0)) " _
& vbLF & " BEGIN " _
& vbLF & " SELECT @TableId = convert(int,substring(@TableName_tmp,@tableid_startpos+1,@tableid_endpos-@tableid_startpos-1)) " _
& vbLF & " DELETE CTLG_PropertyTableMap" _
& vbLF & " Where Tableid = @TableId" _
& vbLF & " END " _
& vbLF & " END" _
& vbLF & " END"

' ================================
' Helper function to report a descriptive error msg.
' ================================
Sub ReportError(ErrorInfo, ErrorNumber, ErrorDescription)
Dim ActualError
ActualError = ErrorInfo
If ErrorNumber <> 0 Then
ActualError = ErrorInfo & "." & vbLF & L_ErrorNumber_text & ErrorNumber & vbLF & L_Description_text & ErrorDescription
End if
MsgBox ActualError, ,L_Title_text
End Sub
Set WshShell = WScript.CreateObject("WScript.Shell")
If Err.Number <> 0 Then
Call ReportError(L_FailedCreateWscript_text, Err.number, Err.Description)
Else
Set WshArgs = WScript.Application.Arguments
Call Main()
end if

' =============================
' The main procedure that does all the work.
' =============================
Sub Main()
On Error Resume Next
' Parse the input arguments.
Call ParseArguments()
' If we have a valid connection string
IF Len(ConnectionString) > 0 Then
' Open a connection to the database
Set Adoconnection = CreateObject("AdoDb.Connection")
IF Err.Number <> 0 Then
Call ReportError(L_CreateConnectionFailed_text, Err.number, Err.Description)
Exit Sub
End if
Call Adoconnection.Open(ConnectionString)
IF Err.Number <> 0 Then
Call ReportError(L_InvalidConnectionString_text, Err.number, Err.Description)
Exit Sub
End if
' Get the database id of this database
Adoconnection.Execute(SQl)
IF Err.Number <> 0 Then
Call ReportError(L_FailedScriptExecution_text, Err.number, Err.Description)
Exit Sub
End if
MsgBox L_Success_text,,L_Title_text
End If
End Sub

' =================================================================
' Helper function to parse the input arguments
' =================================================================

Sub ParseArguments()
On Error Resume Next
IF WshArgs.Length < 1 Then
Call ReportError(L_Usage_text & vbNewLine & L_Usage1_text, Err.number, Err.Description)
Exit sub
End if
'The connection string should be the first argument
ConnectionString = WshArgs(0)
End Sub

So copy this source code in a text file and save it as CatalogCleanup.vbs

Run a command line prompt, and type the following "command" Wscript CatalogCleanup.vbs "ConnectionString"

Where ConnectionString is your MSCS_CatalogScratch connection string.

Let me know if it works

Regards,






Re: Commerce Server 2007 MSCS_CatalogScratch why does it get do full?

Colin Bowern

Dave,

You should be able to safely drop rows from old searches. I would review the script in the previous post and consider running it (backup first of course). While not a lot has changed that I'm aware of in the Catalog Scratch it should be empty when it is not being used.

Cheers,
Colin