How can I limit the amount of data ILM keeps in the Run History RRS feed

  • General discussion

  •   ILM Knowledge Bit

    There is no way to limit the amount of information that ILM will store in the run history. You need to have an administrative process to manage this. Something I can suggest however is to install the MIIS Resource Kit v2 on your ILM server. The resource kit contains a command line tool called MIISCleanRunHistory.exe that allows to clean up your ILM server's Run History.

    You could e.g. execute the tool with the "/d" and or "/t" options which allow you to keep information for a certain period. Scheduling this to run every night or week could allow you to always have 30 days (example) worth of run history data available but also keep the database size fairly constant.

    Note that you must be a member of the MIISAdmins security group to clear run histories from an ILM server.

    If you don't like the idea to add the resource kit to your server, you could implement the following SQL script:

      Dim Service
      Dim DeleteDate
      Dim StartDate
      Dim N
      Dim M
      Dim sqlserv
      ' N is the number of days you wish to keep in your Run History
      N = InputBox("Enter the number of days to keep.", "Days To Keep.", 30)
      M = N - 1
      sqlserv = InputBox("Enter the sQL Server Name.", "SQL Server Name.")
      Set Service = GetObject("winmgmts:\root\MicrosoftIdentityIntegrationServer")
      Set Server = Service.Get("MIIS_Server.Name='MyMIISServer'")
      ' The next line checks that the value of N is greater than 30 days. 
      ' This makes sure that you keep 30 days worth of run histories.
      Do While N > M
        StartDate = Now() - N
        Wscript.echo Startdate
        'The next line puts the delete date in the correct format
        DeleteDate = Replace(StartDate, "/", "-")
        WScript.Echo "Deleting Run Histories from " & DeleteDate
        WScript.Echo "Result: " & Server.ClearRuns(DeleteDate)
        N = N-1
        Call SQLTruncateLog()
      Set Service = nothing
      'Subroutine to truncate SQL Logs
      Sub SQLTruncateLog()
        Dim cn
        Dim sConnection
        '//Instatiate a new connection object
        wscript.echo "Connected to SQL"
        Set cn = CreateObject("ADODB.Connection")
        '//open the connection
        sConnection = "Provider=sqloledb; Data Source=" & sqlserv & "; Initial Catalog= MicrosoftIdentityIntegrationServer; Integrated Security=SSPI"
        cn.open sConnection
        '//set the database context to the database you want to use
        cn.execute "USE MicrosoftIdentityIntegrationServer"
        '//execute your DBCC statement
        cn.Execute "DBCC ShrinkFile (2,1)" '
        set cn=nothing
        wscript.echo "Trucated ILM Log File"
      End Sub

    You can use this script as a template that can be modified so that it can be run in a batch file and scheduled for a daily run. It can be modified to accept arguments, or, the server name could be hardcoded as well as the number of days to keep.

      Go to the ILM Knowledge Bit Collection
    Wednesday, June 16, 2010 8:53 PM