SQL Server Performance

Monitoring DB activities

Discussion in 'SQL Server 2008 General DBA Questions' started by glnp, Feb 19, 2011.

  1. glnp New Member

    Hi ,
    Here is my requirement
    I am a DBA maintaing 150+ sql servers.Now i need to know whenever any user creates database or drops database on SQL servers.Can any one provide solution for this which process is suitable like
    1.Creating trigger and this trigger will produce alerts whenever DB creation/Deetion happens on an instance.
    If so i need to have the customized trigger like
    • DB creaed/Dropped
    • User name
    • Instance Name; Can some one help me in writing this trigger.
    2.Any SQL server 2008 audits can be used to track this process.Then here we should go for Server level audit rather than DB level audit.But this SQL server audits wil affect the performance while tracking events and Disk Space while storing the SQL server events in log files.
    If so then how to create this audit please guide me.
    Please correct me if a wrong.Thank you very much in advance.......
  2. mguissine New Member

    [quote user="glnp"]
    Hi ,
    Here is my requirement
    I am a DBA maintaing 150+ sql servers.Now i need to know whenever any user creates database or drops database on SQL servers.Can any one provide solution for this which process is suitable like
    1.Creating trigger and this trigger will produce alerts whenever DB creation/Deetion happens on an instance.
    If so i need to have the customized trigger like
    • DB creaed/Dropped
    • User name
    • Instance Name; Can some one help me in writing this trigger.
    2.Any SQL server 2008 audits can be used to track this process.Then here we should go for Server level audit rather than DB level audit.But this SQL server audits wil affect the performance while tracking events and Disk Space while storing the SQL server events in log files.
    If so then how to create this audit please guide me.
    Please correct me if a wrong.Thank you very much in advance.......
    [/quote]
    Audit is only available in SQL Server 2008 Enterprise, so as long as all your servers are running Enterprise version you should be able to utilize SQL Server Auditing functionality.
    If you have mixed environment, than your option is Server level DDL trigger.
    BOL provides comprehensive guide on how to create DDL trigger, see here http://msdn.microsoft.com/en-us/library/ms189799.aspx, quick example:
    IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
    GO
    CREATE TRIGGER ddl_trig_database
    ON ALL SERVER
    FOR CREATE_DATABASE
    AS
    PRINT 'Database Created.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
    GO
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
    GO
  3. MikaS New Member

    Hello,
    Considering the large number of servers you have, Im assuming you're monitoring them with some monitoring software? SQL Server 2005 and onwards starts an automatic default trace which it keeps running in the background and it captures a lot of information with very little impact on performance. Most of the monitoring software I've come across allow you to check for keywords in text-files, so you could probably set that up rather easy.
  4. satya Moderator

    Welcome to the forums.
    See this http://www.sql-server-performance.com/articles/per/monitoring_8_steps_p1.aspx as a starter and as referred above AUDIT is an Enterprise Edition feature in SQL SErver 2008 onwards. Further you can deploy a practice to refer to SQL Server error logs and create DML triggers on such changes where you want to get notified.
    Above all it is ideal to have a monitoring software such as SCOM or any other alerting software to capture the events that are required on SQL SErver.

Share This Page