WF provides data maintenance functionality for the OOB SqlTrackingService by partitioning the tracking data upon completion of workflow instances. WF provides two schemes for partitioning out of box.
1. Partition On Completion which is designed for applications that have no downtime do not want to incur downtime. The partition is done on the completion of the workflow instance. That is your records will be in your regular tables until the instance completes and this is when the records are going to move to the added tables. When partitioning is enabled, a new set of tables are created periodically for tracking data and the tracked data of completed workflow instances will move to the new partitions without disrupting currently running applications. Read below to learn more about Partition On Completion.
2. On Demand Partitioning which you can use if your application has a downtime and want to do partitioning during that time or if you want to avoid moving records around when the instances complete or for any other reason. You can create a task that runs PartitionCompletedWorkflowInstances stored proc which will move all currently completed instances from the live tables to the partitions. You can run this task on demand to partition your SqlTrackingService tables.
Enable SqlTrackingService partitioning
Please see Setup and Enabling SqlTrackingService here for information on how to setup and enable SqlTrackingService. Partitioning is OFF by default, so follow these steps to enable partitioning of the OOB SqlTrackingService schema:
Sample C# Code Snippets
Turn on partitioning by setting PartitionOnCompletion to True
string connectionString = "Initial Catalog=Tracking;Data Source=localhost;Integrated Security=SSPI;";
SqlTrackingService sqlTrackingService = new SqlTrackingService(connectionString);
sqlTrackingService.PartitionOnCompletion = true;
Set partition interval to 'd' (daily)
internal static void SetPartitionInterval()
{
// Valid values are 'd' (daily), 'm' (monthly), and 'y' (yearly). The default is 'm'.
SqlCommand command = new SqlCommand("dbo.SetPartitionInterval");
command.CommandType = CommandType.StoredProcedure;
command.Connection = new SqlConnection(connectionString);
SqlParameter intervalParameter = new SqlParameter("@Interval", SqlDbType.Char);
intervalParameter.SqlValue = 'd';
command.Parameters.Add(intervalParameter);
try
command.Connection.Open();
command.ExecuteNonQuery();
}
finally
if ((command != null) && (command.Connection != null) && (ConnectionState.Closed != command.Connection.State))
command.Connection.Close();
command.Dispose();
Show tracking partition information and tables
internal static void ShowTrackingPartitionInformation()
//Show the contents of the TrackingPartitionName table
SqlCommand command = new SqlCommand("SELECT * FROM vw_TrackingPartitionSetName");
SqlDataReader reader = null;
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
if (reader.Read())
Console.WriteLine();
Console.WriteLine("***************************");
Console.WriteLine("Partition information: ");
Console.WriteLine("PartitionId: {0}", reader[0]);
Console.WriteLine("Name: {0}", reader[1]);
Console.WriteLine("Created: {0}", reader[2]);
Console.WriteLine("End: {0}", reader[3] is System.DBNull ? "NULL" : reader[3]);
Console.WriteLine("Partition Interval: {0}", reader[4]);
else
Console.WriteLine("No partition information present.");
if ((reader != null) && (!reader.IsClosed))
reader.Close();
List the names of the partition tables created
internal static void ShowPartitionTableInformation()
SqlCommand command = new SqlCommand(
"declare @trackingName varchar(255) select @trackingName = Name from vw_TrackingPartitionSetName " +
"select name from sysobjects where name like '%' + @trackingName");
Console.WriteLine("Partition tables: ");
while (reader.Read())
Console.WriteLine(reader[0]);