2

Usually I use below query to get list of DBs which are with Percent growth or not having growth rate of 100 MB. I want to automate this process; How do I get alert when ever any developer change database growth rate value to any from our default configurations ?

SELECT DISTINCT
            db.name,
            growth,
            is_percent_growth
  FROM      sys.master_files mfile
            INNER JOIN sys.databases db ON mfile.database_id = db.database_id
  WHERE     ( growth <> 12800
              OR is_percent_growth = 1
            )
            AND db.state_desc = 'ONLINE'
            AND db.is_read_only = 0
            AND ( db.database_id > 4
                  OR db.name = 'Model'
                )
  ORDER BY  name
1

Probably depends on how quickly you want to be alerted.

If you just want to able to report on it then I'd probably suggest setting up a policy in Policy Based Management. You should be able to evaluate your instance on demand or on a schedule. You can generate an alert based off the SQL job it creates as well so you can be notified.

You could also look to just create a job that looked much like your query but either raise an error if there are databases that breach your policy (and append it to the alert)

The two options that would almost immediately let you know if someone had changed an autogrowth setting would be either via

  • DDL Server Scoped Trigger
  • SQL Alert using WMI to query ALTER_DATABASE Events

However, the latter would probably need to use tokens and would definitely require Service Broker to be enabled on all databases to work.

| improve this answer | |
  • can we get this information from SQL Server default trace running for SQL Instance? – AA.SC Jan 11 '17 at 5:43
  • You can get limited information from default trace - you could deduce that someone had run an ALTER DATABASE command on a particular database but I'm fairly sure you wouldn't be able to gleen anymore than that. Also, seeing as the functions around querying the default trace are going to be removed in the future, I'm not sure this is a route I'd want to go down. I mentioned PBM because it sounds to me as if you're trying to evaluate your databases against a "standard". PBM is ideally placed for this and really quite flexible although in my opinion, rarely used. – unclefredo Jan 11 '17 at 14:00
  • how about Management -> SQL Server Logs ? I made an unsuccessful try to get that information from log – AA.SC Jan 12 '17 at 10:13
  • ALTER DATABASE MODIFY FILE won't get logged to the SQL Error log I'm afraid. Have you tried setting up a Policy against a schedule? It's fairly simple to do and would get you most of the way there unless you want absolute real-time alerting on when someone fires that operation – unclefredo Jan 12 '17 at 10:40
  • can you guide me to setup that policy ? – AA.SC Feb 2 '17 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.