Post reply

Create trigger to track value of sp_configure

  • December 4, 2012 at 10:50 pm

    #393743

    Hi, i need to create trigger that sends mail whenever any value changed in sp_configure..

    I am looking for idea or example how i can start it..

    Thanks,

  • December 4, 2012 at 11:43 pm

    #1565578

    Hi Aadhar,

    I am not sure if thers is any way to monitor the execution of sp_configure procedure.

    You can use the below approach.

    1. Make sure "show advanced options" is always set to 1.

    2. Create a table tbl_spconfigure to hold the sp_configure values.

    3. Use a procedure to compare the current values to the stored values.

    3.1. Create a temp_spconfigure table and populate it with the current sp_configure values.

    3.2. Delete from the temp_spconfigure table the values that match the tbl_spconfigure values.

    3.3. Populate a tracking_spconfigure table with the old values (tbl_spconfigure) and the new values(temp_spconfigure table) for the values that exist in the temp_spconfigure table.

    3.4. Create a trigger on the tracking_spconfigure table to send you an email before/after populating.

    4. Truncate tbl_spconfigure and re-populate with the current values.

    5. Schedule a SQL Agent job to execute the procedure at a specified freequency (every minute may be).

    Hope this helps.

    Cheers,

    Mohammed Imran Ali.

  • December 4, 2012 at 11:55 pm

    #1565581

    Thank you Ali,

    But i have little confusion to create job and execute on every minute(Probably because we need to check values every moment) so it creates load on server to execute job unnecessary. We need to find the solution which executes only when value get change.

  • December 5, 2012 at 1:02 am

    #1565592

    Aadhar Joshi (12/4/2012)

    We need to find the solution which executes only when value get change.

    it this case too , you need to trigger job, job itself doesnt put any kind of impact from resource perspective only the code sitting inside it. and in your case your can make another job (controller job we can name it ) which will check your condition through sql code sitting in it and then trigger the main job. 🙂

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • December 5, 2012 at 1:10 am

    #1565598

    Thanks Bhuvnesh,

    But i am looking for solution which can operate through trigger..:(

    Thanks,

  • December 5, 2012 at 2:02 am

    #1565610

    Aadhar Joshi (12/5/2012)

    But i am looking for solution which can operate through trigger..:(

    trigger ? you want to work on row level ? i dont think its a good idea , on one end you are talking about overhead but in trigger case, you are bounding sql to put overhead

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • December 5, 2012 at 2:46 am

    #1565628

    Suppose let u think for a situation where value change can happen once in a month,

    If i go with job, i need to give time that executes job, the minimal time i consider is 30 seconds so after every 30 seconds it will check for value change.

    Rather if i create trigger that executes only when value change i mean in this case it fires only once in month.

  • December 5, 2012 at 2:48 am

    #1565629

    Or there could be a better solution..

  • December 5, 2012 at 3:00 am

    #1565635

    Use policy based management on the Server Configuration facet, give it the values it should be, then if the values change it alerts you

  • December 5, 2012 at 3:05 am

    #1565638

    Aadhar Joshi (12/5/2012)

    If i go with job, i need to give time that executes job, the minimal time i consider is 30 seconds so after every 30 seconds it will check for value change.

    Rather if i create trigger that executes only when value change i mean in this case it fires only once in month.

    now lets take your case in another way , now assume that you got hundreds of records where trigger get fired 100 times (there could be throusand and milliions .....as you can predicate the future data 🙂 ) so in that case for that particular interval of time you resouce usage will be at peaks because of hundreds of trigger calling so just to handle this kind of scenario job work in scheduled manner where you can handle the data at OFF- peak hours

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply