I’m just starting out looking at Policy based management for SQL Server 2008. I haven’t had a large SQL Server base to manage recently so I haven’t really had a chance to use it. Where I’m at currently I’m the sole DBA and have about 300+ Databases that have had less than perfect maintenance and setup done to them so I figured I would test out PBM to get some things in order.
I noticed that many of the Databases were set to grow in 1mb increments. That’s fine and dandy to make a fast Auto Grow time but my VLF files are off the charts on this server. I know the real solution is managing the space and growing when I want to in size/chunks when I want to. For right now though with the 50 other tasks I have on my plate I wanted to knock this one out quickly.
Adding the policy was easy this post does a great job of showing you how to do this. The problem I ran into was finding the proper Facet. In this case you can find @growth in the Data File Facet. The second mistake I made is I tried to say in the check condition I wanted to find everything that’s not equal to 512. I figured that would give me a list of all my Databases out there. But that’s not what PBM wants. PBM wants you to tell it what the number should be and it will tell you who doesn’t match. When I said not equal it wouldn’t let me apply that after I got my list. Once I changed the rule to = 512 it found me all the databases I was looking for and allowed me to apply the change to set them to grow at 512 instead of 1.
Make sure when you are creating a PBM policy make your items equal to what you are looking for instead of not equal.