SqlTrackingService Data Maintenance 101
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:
Set SqlTrackingService.PartitionOnCompletion to True. Note the default is false.
Specify the partition interval by setting TrackingPartitionInterval.Interval in SqlTrackingService schema. Default is monthly “m”. You can set it to daily “d”, or yearly “y”. For example, if its monthly “m”, we will create set of tables every month
WF will create a new partition set (new set of tables). Table TrackingPartitionSetName will hold information about the partition set name – Name yyyy_m, CreatedDateTime, EndDateTime, and the PartitionInterval
Upon completion of workflow instances, their corresponding tracked data will be moved to the newly created set of tables
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;
command.Connection = new SqlConnection(connectionString);
try
{
command.Connection.Open();
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]);
Console.WriteLine("***************************");
}
else
{
Console.WriteLine();
Console.WriteLine("No partition information present.");
}
}
finally
{
if ((reader != null) && (!reader.IsClosed))
reader.Close();
if ((command != null) && (command.Connection != null) && (ConnectionState.Closed != command.Connection.State))
{
command.Connection.Close();
}
command.Dispose();
}
}
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();
Console.WriteLine("***************************");
Console.WriteLine("Partition tables: ");
SqlDataReader reader = null;
command.CommandType = CommandType.Text;
command.Connection = new SqlConnection(connectionString);
try
{
command.Connection.Open();
reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader[0]);
}
}
finally
{
if ((reader != null) && (!reader.IsClosed))
reader.Close();
if ((command != null) && (command.Connection != null) && (ConnectionState.Closed != command.Connection.State))
{
command.Connection.Close();
}
command.Dispose();
}
Console.WriteLine("***************************");
}