Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create trigger to track value of sp_configure Expand / Collapse
Author
Message
Posted Tuesday, December 04, 2012 10:50 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:09 AM
Points: 1,608, Visits: 359
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
Posted Tuesday, December 04, 2012 11:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:26 AM
Points: 72, Visits: 247
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
Posted Tuesday, December 04, 2012 11:55 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:09 AM
Points: 1,608, Visits: 359
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
Posted Wednesday, December 05, 2012 1:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #1392820
Posted Wednesday, December 05, 2012 1:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:09 AM
Points: 1,608, Visits: 359
Thanks Bhuvnesh,

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

Thanks,
Post #1392826
Posted Wednesday, December 05, 2012 2:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #1392839
Posted Wednesday, December 05, 2012 2:46 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:09 AM
Points: 1,608, Visits: 359
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
Posted Wednesday, December 05, 2012 2:48 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:09 AM
Points: 1,608, Visits: 359
Or there could be a better solution..
Post #1392863
Posted Wednesday, December 05, 2012 3:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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
Posted Wednesday, December 05, 2012 3:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #1392873
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse