How to maintain a Microsoft Content Management Server database (836646)

MORE INFORMATION

Reduce the size of your MCMS database

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

1. 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.
2. 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.
3. Use SQL Query Analyzer to compact the database. To do this, run the following SQL query against the database. 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_LOB>', 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.
4. 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>