A lot of applications, including SCOM and even SSRS groom their databases so they don’t grow out of control. For instance, in SSRS there is a table called ExecutionLogStorage in the ReportServer database. This table holds details about report executions. SSRS grooms this table so that it only keeps a specified number of days of data. This is good for performance reasons obviously because the larger the table grows, the worse it will perform. However, what if you need to keep more days than specified? You can probably change the number of days in a config file but then the table will begin to grow and the server will not perform optimally. In cases like this, I like to create what I call a table warehouse. A table warehouse is basically a new table that stores data from another table, but doesn’t get groomed. A table warehouse will grow much larger than the source table, but the application doesn’t use this table so it shouldn’t effect performance, but it will effect disk space so make sure you plan accordingly. This is basically a data warehouse, but I like to think of a data warehouse as more than one table and I usually transform and massage the data before I move it to a data warehouse.
Some reasons why you might want to create a table warehouse include archiving data and reporting on older data that may get deleted in the future.
You can probably create this type of table several different ways but I like to use the UNION operator. In this post, I’ll show you the statement I use and we’ll walk through it.
First, let’s look at the ExecutionLogStorage table:
This table stores information such as InstanceName, UserName, Format, Parameters, Start/End Time, etc. By default, it only stores information for 60 days. Every night, SSRS grooms this table so that the 61st day of data is deleted. We need to keep more than 60 days of data so that’s where the table warehouse comes into play.
First thing we’ll need to do is create our table warehouse table that will hold the data. I don’t need all the columns from the ExecutionLogStorage table so I’ll just create my table warehouse to store InstanceName, UserName, Format, TimeStart, TimeEnd, TimeProcessing, Status, ByteCount and RowCount.
CREATE TABLE [dbo].[ExecutionLogStorageTW](
[InstanceName] [nvarchar](38) NOT NULL,
[UserName] [nvarchar](260) NULL,
[Format] [nvarchar](26) NULL,
[TimeStart] [datetime] NOT NULL,
[TimeEnd] [datetime] NOT NULL,
[TimeProcessing] [int] NOT NULL,
[Status] [nvarchar](40) NOT NULL,
[ByteCount] [bigint] NOT NULL,
[RowCount] [bigint] NOT NULL
)
Next we can use the following query to see the differences between the ExecutionLogStorage table and our new ExecutionLogStorageTW table:
SELECT MIN(TableName) AS TableName ,InstanceName ,UserName ,[Format] ,TimeStart ,TimeEnd ,TimeProcessing ,[Status] ,ByteCount ,[RowCount] FROM ( SELECT 'ELS' AS TableName ,els.InstanceName ,els.UserName ,els.[Format] ,els.TimeStart ,els.TimeEnd ,els.TimeProcessing ,els.[Status] ,els.ByteCount ,els.[RowCount] FROM [ReportServer].[dbo].[ExecutionLogStorage] els UNION ALL SELECT 'ELSDW' AS TableName ,elstw.InstanceName ,elstw.UserName ,elstw.[Format] ,elstw.TimeStart ,elstw.TimeEnd ,elstw.TimeProcessing ,elstw.[Status] ,elstw.ByteCount ,elstw.[RowCount] FROM [ReportServer].[dbo].[ExecutionLogStorageTW] elstw ) tmp GROUP BY InstanceName ,UserName ,[Format] ,TimeStart ,TimeEnd ,TimeProcessing ,[Status] ,ByteCount ,[RowCount] HAVING COUNT(*) = 1
This query will UNION all the results from ExecutionLogStorage and ExecutionLogStorageTW and display the data that is different. As you can see from the results below there are 72325 results that are different. The TableName column displays the table that the data resides in, therefore you can see that all the data resides in the ELS (ExecutionLogStorage) table.
Since ExecutionLogStorageTW is empty it should show the exact results as:
SELECT * FROM [ReportServer].[dbo].[ExecutionLogStorage]