Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create trigger to track value of sp_configure


Create trigger to track value of sp_configure

Author
Message
Aadhar Joshi
Aadhar Joshi
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 Visits: 451
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,
Mohammed Imran Ali
Mohammed Imran Ali
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 258
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.
Aadhar Joshi
Aadhar Joshi
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 Visits: 451
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.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 4076
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;-)
Aadhar Joshi
Aadhar Joshi
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 Visits: 451
Thanks Bhuvnesh,

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

Thanks,
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 4076
Aadhar Joshi (12/5/2012)
But i am looking for solution which can operate through trigger..Sad

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;-)
Aadhar Joshi
Aadhar Joshi
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 Visits: 451
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.
Aadhar Joshi
Aadhar Joshi
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 Visits: 451
Or there could be a better solution..
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6102 Visits: 6078
Use policy based management on the Server Configuration facet, give it the values it should be, then if the values change it alerts you



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 4076
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;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search