How to use TRUNCATE commands to reduce the size of the Application Center MSDE database (834079)

MORE INFORMATION

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:

1. Click Start, click All Programs, click Microsoft SQL Server, and then click Query Analyzer.
2. In the SQL Server drop-down list, click Machinename\MSAC.
   
   If the correct Machinename\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.
3. Verify that Windows authentication is selected, and then click OK.
4. Select ACLog database from the drop-down list.
5. Type the following command in the Query Analyzer window:

   sp_spaceused

6. To execute the command, click Execute on the Query menu.
7. To clear the query window, click Clear Window on the Edit menu.
8. Make a note of the current database size.
9. 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.
10. Type the following command:

    truncate table perfhistory2

    Repeat steps 6 and 7.
11. Type the following command:

    truncate table perfhistory3

    Repeat steps 6 and 7.
12. Type the following command:

    truncate table perfhistory4

    Repeat steps 6 and 7.
13. Type the following command:

    truncate table perfhistory5

    Repeat steps 6 and 7.
14. To verify that you have reduced the size of the database, repeat steps 5 through 7.

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.

1. Store the following commands in a file that is named Input.txt:

   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)

2. 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

3. Open a command prompt window.
   
   To do so, click Start, click Run , type cmd, and then click OK.
4. 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.
5. Type the following command, and then click OK:

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

   Substitute the correct server name for machinename in the command.
6. 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.

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