Microsoft KB Archive/834079

= How to use TRUNCATE commands to reduce the size of the Application Center MSDE database =

Article ID: 834079

Article Last Modified on 3/24/2004

-

APPLIES TO


 * Microsoft Application Center 2000 Standard Edition
 * Microsoft Application Center 2000 Standard Edition
 * Microsoft Application Center 2000 Service Pack 2

-



IN THIS TASK

 * INTRODUCTION
 * If Query Analyzer is installed
 * If Query Analyzer is not installed
 * How to slow the growth of the database
 * REFERENCES



INTRODUCTION
This step-by-step article describes how to use truncate commands to reduce the size of the Microsoft Data Engine (MSDE) database that is installed by Microsoft Application Center 2000 if the database becomes too large.

back to the top



If Query Analyzer is installed
If you can access a SQL Server installation that both includes Query Analyzer and can connect to the database, follow these steps to reduce the size of the database:  Click Start, click All Programs, click Microsoft SQL Server, and then click Query Analyzer. In the SQL Server drop-down list, click  \MSAC.

If the correct \MSAC instance is not listed, click the ellipsis button (...) to see a list of all servers that are running SQL Server and that are currently active on your network. Click the correct instance in the list. Verify that Windows authentication is selected, and then click OK. Select ACLog database from the drop-down list. Type the following command in the Query Analyzer window:

sp_spaceused

 To execute the command, click Execute on the Query menu. To clear the query window, click Clear Window on the Edit menu.</li> Make a note of the current database size.</li> Type the following command:

truncate table perfhistory

Repeat steps 6 and 7 to execute the command and to clear the window for the next command.</li> Type the following command:

truncate table perfhistory2

Repeat steps 6 and 7.</li> Type the following command:

truncate table perfhistory3

Repeat steps 6 and 7.</li> Type the following command:

truncate table perfhistory4

Repeat steps 6 and 7.</li> Type the following command:

truncate table perfhistory5

Repeat steps 6 and 7.</li> To verify that you have reduced the size of the database, repeat steps 5 through 7.</li></ol>

back to the top

If Query Analyzer is not installed
If you do not have Query Analyzer, you can reduce the size of the database by using the Osql.exe command-line tool that is included in your MSDE installation. <ol> Store the following commands in a file that is named Input.txt:

<pre class="fixed_text">truncate table PerfHistory2 truncate table PerfHistory3 truncate table PerfHistory4 truncate table PerfHistory5 truncate table Config truncate table Events truncate table EventHelpMessages truncate table PerfHistory dbcc shrinkdatabase (AClog,truncateonly) dbcc shrinkfile (ACLog_log, 10, truncateonly)

</li> Save the file in the folder where MSDE is installed. The default location is the following folder:

C:\Program Files\Microsoft SQL Server\MSSQL$MSAC\Data

</li> Open a command prompt window.

To do so, click Start, click Run, type cmd , and then click OK.</li> Type the following command, and then click OK:

cd c:\Program Files\Microsoft SQL Server\MSSQL$MSAC\Data

Substitute the correct folder if your installation folder is in another location.</li> Type the following command, and then click OK:

osql -E -S \msac -d aclog -i input.txt -o output.txt

Substitute the correct server name for  in the command.</li> Review the Output.txt file that was created in the same location as the Input.txt file.

This output file indicates whether the command succeeded. The Aclog.mdf file indicates the size of the database.</li></ol>

back to the top

How to slow the growth of the database
To prevent the database from growing too large in the future, you can edit the properties of events for the cluster. To use the following methods in Application Center 2000 Microsoft Management Console (MMC), right-click events for the cluster, and then click Properties:
 * Keep logged events for fewer days

Change the default value of the Keep logged events for (days) option to less than 15.
 * Turn off performance counter logging

Clear the Enable performance counter logging check box.

back to the top

<div class="references_section">