<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Create trigger to track value of sp_configure...
Create trigger to track value of sp_configure
Rate Topic
Display Mode
Topic Options
Author
Message
Aadhar Joshi
Aadhar Joshi
Posted Tuesday, December 04, 2012 10:50 PM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 10:44 PM
Points: 1,091,
Visits: 229
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,
Post #1392792
Mohammed Imran Ali
Mohammed Imran Ali
Posted Tuesday, December 04, 2012 11:43 PM
Valued Member
Group: General Forum Members
Last Login: Sunday, May 19, 2013 6:33 AM
Points: 65,
Visits: 202
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.
Post #1392803
Aadhar Joshi
Aadhar Joshi
Posted Tuesday, December 04, 2012 11:55 PM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 10:44 PM
Points: 1,091,
Visits: 229
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.
Post #1392807
Bhuvnesh
Bhuvnesh
Posted Wednesday, December 05, 2012 1:02 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1392820
Aadhar Joshi
Aadhar Joshi
Posted Wednesday, December 05, 2012 1:10 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 10:44 PM
Points: 1,091,
Visits: 229
Thanks Bhuvnesh,
But i am looking for solution which can operate through trigger..
Thanks,
Post #1392826
Bhuvnesh
Bhuvnesh
Posted Wednesday, December 05, 2012 2:02 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1392839
Aadhar Joshi
Aadhar Joshi
Posted Wednesday, December 05, 2012 2:46 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 10:44 PM
Points: 1,091,
Visits: 229
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.
Post #1392861
Aadhar Joshi
Aadhar Joshi
Posted Wednesday, December 05, 2012 2:48 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 10:44 PM
Points: 1,091,
Visits: 229
Or there could be a better solution..
Post #1392863
anthony.green
anthony.green
Posted Wednesday, December 05, 2012 3:00 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
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
Post #1392870
Bhuvnesh
Bhuvnesh
Posted Wednesday, December 05, 2012 3:05 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1392873
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
