Expiring Databases and Policy-Based Management

Today on Twitter my friend Jes “Run Forrest Run” Schultz Borland (Blog | Twitter) asked the Community “How do you clean up your dev environments? Let DBs sit out there forever? Delete after X months? Other? This seemed like an interesting issue to tackle and me being the PBM freak that I am, immediately I had a light bulb moment for a policy. In this post I’ll show you a policy you can run against your databases (can work in dev or whatever environment suits you) and will tell you which databases are older than 30 days old. As an added bonus, I’ll also show you how to add a custom extended property to set a custom expiration date.

Custom Expiration Date

First we’re going to modify our existing databases to define an expiration date. How are we going to do this? We’re going to add an extended property to our database. To do so on one database use the stored procedure sys.sp_addextendedproperty. The following code uses this stored procedure to add an extended property called ‘ExpDate’ with a value of ‘11/11/2011’.

EXEC AdventureWorks.sys.sp_addextendedproperty @name=N'ExpDate', @value=N'11/11/2011'

Why are we only doing this on one database? Well it will make more sense later when we run our policy against all our databases in our instance. Next up, the policy itself. As always I’m providing both the T-SQL for the policy or you can download the XML and import the policy directly.

Download ‘Expire Development Databases’ Policy Here

T-SQL:

--Create condition first
Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Databases older than 1 month', @description=N'', @facet=N'Database', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>OR</OpType>
  <Count>2</Count>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>GE</OpType>
    <Count>2</Count>
    <Attribute>
      <TypeClass>DateTime</TypeClass>
      <Name>CreateDate</Name>
    </Attribute>
    <Function>
      <TypeClass>DateTime</TypeClass>
      <FunctionType>DateAdd</FunctionType>
      <ReturnType>DateTime</ReturnType>
      <Count>3</Count>
      <Constant>
        <TypeClass>String</TypeClass>
        <ObjType>System.String</ObjType>
        <Value>day</Value>
      </Constant>
      <Constant>
        <TypeClass>Numeric</TypeClass>
        <ObjType>System.Double</ObjType>
        <Value>-30</Value>
      </Constant>
      <Function>
        <TypeClass>DateTime</TypeClass>
        <FunctionType>GetDate</FunctionType>
        <ReturnType>DateTime</ReturnType>
        <Count>0</Count>
      </Function>
    </Function>
  </Operator>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>GT</OpType>
    <Count>2</Count>
    <Function>
      <TypeClass>DateTime</TypeClass>
      <FunctionType>ExecuteSql</FunctionType>
      <ReturnType>DateTime</ReturnType>
      <Count>2</Count>
      <Constant>
        <TypeClass>String</TypeClass>
        <ObjType>System.String</ObjType>
        <Value>DateTime</Value>
      </Constant>
      <Constant>
        <TypeClass>String</TypeClass>
        <ObjType>System.String</ObjType>
        <Value>SELECT value FROM sys.extended_properties&lt;?char 13?&gt;
WHERE name = ''''ExpDate''''</Value>
      </Constant>
    </Function>
    <Function>
      <TypeClass>DateTime</TypeClass>
      <FunctionType>GetDate</FunctionType>
      <ReturnType>DateTime</ReturnType>
      <Count>0</Count>
    </Function>
  </Operator>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id

GO

--Now create the policy itself using the newly created condition
Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Expire Development Databases_ObjectSet', @facet=N'Database', @object_set_id=@object_set_id OUTPUT
Select @object_set_id

Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Expire Development Databases_ObjectSet', @type_skeleton=N'Server/Database', @type=N'DATABASE', @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0
GO

Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'Expire Development Databases', @condition_name=N'Databases older than 1 month', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'Expire Development Databases_ObjectSet'
Select @policy_id
GO

So let’s talk about this policy first before we run it. The condition on this policy is checking for two things. The first is to see if the creation date of the database is more than 30 days from the current date. Of course you can go into the condition and modify it to whatever you’d like but for the purposes of this post we’re using 30 days. Additionally we also have another clause using OR that is checking the value of the extended property field to see if that custom date is greater than the current date. How did we do this? We use the advanced editor in the condition creator and use the executesql function to query the database for the value of the ExpDate property. If no extended property exists for that database then the policy will simply use the 30 day condition clause mentioned earlier.

So now that we’ve seen the policy and its condition let’s see it in action! I’ve run this policy against the databases on my instance and we get mixed results here. Overall we see a bunch of databases that don’t pass the policy but we have one that does.

As you can see from the screenshots above, the policy that passed did so because it passed our secondary clause in our policy. The policy was run (at the time of this posting) on October 6th 2011. The expected expiration date from the first clause expects a date 30 days ago, which is September 6th. Our second clause, however, instead looks at the value of the extended property we set earlier. Since today’s date is less than that of the property set value of November 11th, the database is still considered to be current and active!

Next Steps

While this policy evaluation doesn’t let you take any action against the databases, this does give you a listing of what databases you should be paying attention to. This method lets you quickly identify which databases you should be “cleaning up” in your environments. You can schedule a policy like this to run only against your development environments by using the Central Management Server’s (CMS) functionality in conjunction with the Enterprise Policy Management Framework. The nice thing about using EPMFramework here is that results are saved in a database and you’re able to see those results in the great built-in reports. To see how to use CMS with Policy-Based Management, check out my webinar from the recent 24 Hours of PASS event!