Knowledge Base

How to maintain a Microsoft Content Management Server database

Article ID: 836646

Article Last Modified on 4/15/2007

APPLIES TO

Microsoft Content Management Server 2001 Enterprise Edition
Microsoft Content Management Server 2001 Service Pack 1
Microsoft eMbedded Visual C++ 3.0
Microsoft Content Management Server 2001 Developer Edition
Microsoft Content Management Server 2002
Microsoft Content Management Server 2002
Microsoft Content Management Server 2002

SUMMARY

This article discusses general best practices for maintaining your Microsoft Content Management Server (MCMS) database by using tools that are included with the MCMS client tools and with SQL Enterprise Manager.

This article also discusses a substitute process for the Background Process (BP). This substitute process includes a SQL stored procedure version of the BP together with an ASP sample script to delete expired postings and to delete expired pages in your MCMS database.

Note The substitute process is for MCMS 2001 only.

INTRODUCTION

If you update your production Microsoft Content Management Server (MCMS) database frequently by using the Site Deployment process, you may notice that your production database is significantly larger than either your development database or your authoring database.

The difference in the database size occurs because the Site Deployment process creates an archive version of the object that it replaces during an Import process. However, the Background Process (BP) does not automatically delete these archive versions because these archive versions are not expired items or orphan objects in the database.

MORE INFORMATION

Reduce the size of your MCMS database

To reduce the size of your MCMS production database, follow these steps:

Manually delete the archive versions in the database:
1. Log on to the MCMS Win32 client (Site Builder or Site Manager).
2. Use one of the following methods, depending on whether you are using Site Builder or Site Manager:
  - In Site Manager, click Clear Revision History on the Tools menu.
  - In Site Builder, click Purge Revisions on the Tools menu.
3. Select a date and a time. All revisions before the specified date and time will be deleted. For example, if you select the current date and time, all revisions before the current date and time will be deleted.
  
  For production servers that must always be available, consider purging revisions more frequently because the Purge Revisions process may prevent database access for other processes. For example, if the Purge Revisions process takes a long time to be completed, the Site Deployment Import process may not have access to the database.
4. Click Clear.
Run the script version of the BP. To do this, use one of the following methods, depending on the version of MCMS that you have:
- If you have MCMS 2001, follow these steps:
  1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer to open SQL Query Analyzer.
  2. Log on to the correct computer that is running Microsoft SQL Server, and then select the MCMS database.
  3. Paste the following BP script in the Query Analyzer window, and then run the script against the MCMS database. This script creates a stored procedure that is named BP_Processing in the MCMS database.
```
CREATE PROC BP_Processing
AS
 DELETE FROM BlobTable
   WHERE BlobId NOT IN (SELECT ResourceBlobId 
                        from NodeResource 
                        where ResourceBlobId is not null)
   OPTION(MAXDOP 1)
GO
```
  4. Execute the BP_Processing stored procedure. To do this, run the following SQL command:
```
EXEC BP_Processing
GO
```
- If you have MCMS 2002, run the BP job.
  
  Note In MCMS 2002, the BP has already been converted to a SQL stored procedure. The SQL stored procedure is scheduled and is run as a SQL job. Therefore, MCMS 2002 users only have to run the BP job to complete this step.
Use SQL Query Analyzer to compact the database. To do this, run the following SQL query against the database.

Note Before you run this query, review the statements in this query with your database administrator to verify that the parameters are correct for your specific database size and for your specific business requirements.
```
DBCC SHRINKFILE('<DB_NAME_LOG_FILE>', 10)
DBCC SHRINKDATABASE(<DB_NAME>, 40)
BACKUP Log <DB_NAME> with no_log
EXEC sp_updatestats
```
Note In this query, <DB_NAME> is a placeholder for the name of your MCMS database.
Defragment your MCMS database and reduce the size of the database:
1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager to open SQL Enterprise Manager.
2. In SQL Enterprise Manager, locate your MCMS database.
3. Right-click your MCMS database, point to All Tasks, and then click Shrink Database. The Shrink Database dialog box appears.
4. Click to select the Move pages to beginning of file before shrinking check box.
5. Set Maximum free space in files after shrinking to a small number. For example, you can set the maximum free space to 5.
  
  Note If you are expecting the database to grow soon, it is a good idea to allocate more free space for the database. For example, if a large Site Deployment import is about to occur, allocate more free space for the database to avoid slow performance when the Site Deployment Import process is running.

Delete the expired postings and their associated pages

Note If you have MCMS 2002 installed, you do not have to do this procedure because the BP job that you ran in step 2 of the "Reduce the size of your MCMS database" section deletes expired postings.

In MCMS 2001, the default server-side BP job that is configured in the Server Configuration Application (SCA) interface deletes expired postings and their associated pages. However, the script version of the BP does not delete expired postings or their associated pages. Therefore, you may have to run the following ASP sample script to delete these items.

Note You do not have to run this ASP script if you run the MCMS server-side BP periodically.

To create this ASP script, paste the following sample script code in a blank ASP file, and then save the file in the root folder on the Microsoft Internet Information Services (IIS) virtual Web site that has been specified as an MCMS entry point. To run this ASP script, access the ASP page through Microsoft Internet Explorer by typing the URL of the ASP file that you saved in the root folder on the IIS virtual Web site.

Sample ASP script to delete expired postings and their associated pages

<!-- #include virtual="/NR/System/Access/Resolution.inc" -->
<html>
<body>
<h3>Delete Expired Postings</h3>
<hr>
<%
Dim objTopChannel
Set objTopChannel = AutoSession.RootChannel
If Not AutoSession.IsModeUpdate Then
    Response.Redirect("http://localhost/delexpired.asp" & "?" & objTopChannel.QueryStringModeUpdate)
End If

Call RemoveExpired(objTopChannel)

Sub RemoveExpired(objTheChannel)
    Dim objChildChannel
    Dim objChildPosting

    For Each objChildChannel In objTheChannel.Channels
        Call RemoveExpired(objChildChannel)
    Next

    For Each objChildPosting In objTheChannel.Postings
        'The date of January 1, 3000 is an arbitrary date chosen for this sample.  Users should change the date as they needed, for example, the current date.
  'However, the format of the date needs to stay as it is specified for the date conversion to work.
  'If (objChildPosting.ExpiryDate < Now) Then
        If (objChildPosting.ExpiryDate < cdate("January 1, 3000")) Then
            Response.Write(objChildPosting.Path & "<br>")
            Response.Write(objChildPosting.ExpiryDate & "<br>")
            objChildPosting.Delete
            AutoSession.CommitAll
        End If
'Response.Write(objChildPosting.ExpiryDate & "<br>")
    Next
End Sub
%>
<hr>
<h3>Done</h3>
</body>
</html>

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

813513 CMS server generates 404 and 500 errors during background processing

Additional query words: CMS MCMS site deployment database DB size shrink

Keywords: kbhowto KB836646

Microsoft KB Archive/836646

Contents