February 21, 2011 at 3:05 am
It started recently that for every few days the maintenance plains start to fail causing log backups to be missing. I read many different forums and found out that setting "allow update" in sys.configurations using "sp_configure 'allow update', 1; RECONFIGURE" can result in such behavior. I checked this setting and it was set to 1. I reset it to 0 and it seemed to be the remedy. But after the weekend the plans were failing again. I rechecked the setting and it was set to 1 again.
Is there a way (using profiler or reading tran logs somehow) to track down users that execute sp_configure with certain parameters?
Alternatively, is there a way to restrict the use of it? By the way, why does this "allow update" setting make maintenance plans fail in the first place?
February 21, 2011 at 4:03 am
First of all, I would check who has sysadmin rights on the server.
Probably somebody with little understanding of this setting has enough rights to change it.
-- Gianluca Sartori
February 22, 2011 at 8:10 am
gemisigo (2/21/2011)
It started recently that for every few days the maintenance plains start to fail causing log backups to be missing. I read many different forums and found out that setting "allow update" in sys.configurations using "sp_configure 'allow update', 1; RECONFIGURE" can result in such behavior. I checked this setting and it was set to 1. I reset it to 0 and it seemed to be the remedy. But after the weekend the plans were failing again. I rechecked the setting and it was set to 1 again.Is there a way (using profiler or reading tran logs somehow) to track down users that execute sp_configure with certain parameters?
Alternatively, is there a way to restrict the use of it? By the way, why does this "allow update" setting make maintenance plans fail in the first place?
Hi, anybody has the permission to execute sp_configure to view settings. Only sysadmin or serveradmin can change parameters.
Through Profiler, trace the SP:Starting and SP:Completed and filter the trace on the objectname column supplying the filter value of sp_configure
Below is the code to create and execute a silent server side trace to catch the culprit, all you need to do is set your drive\path and filename for the trace file 😎
/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 22/02/2011 15:05:54 */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 2, N'G:\some path\FileName', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 43, 7, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 8, @on
exec sp_trace_setevent @TraceID, 43, 48, @on
exec sp_trace_setevent @TraceID, 43, 64, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
exec sp_trace_setevent @TraceID, 43, 41, @on
exec sp_trace_setevent @TraceID, 43, 49, @on
exec sp_trace_setevent @TraceID, 43, 2, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 26, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 50, @on
exec sp_trace_setevent @TraceID, 43, 3, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
exec sp_trace_setevent @TraceID, 43, 51, @on
exec sp_trace_setevent @TraceID, 43, 4, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 28, @on
exec sp_trace_setevent @TraceID, 43, 60, @on
exec sp_trace_setevent @TraceID, 43, 5, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 29, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 22, @on
exec sp_trace_setevent @TraceID, 43, 62, @on
exec sp_trace_setevent @TraceID, 42, 7, @on
exec sp_trace_setevent @TraceID, 42, 8, @on
exec sp_trace_setevent @TraceID, 42, 64, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 9, @on
exec sp_trace_setevent @TraceID, 42, 41, @on
exec sp_trace_setevent @TraceID, 42, 49, @on
exec sp_trace_setevent @TraceID, 42, 2, @on
exec sp_trace_setevent @TraceID, 42, 6, @on
exec sp_trace_setevent @TraceID, 42, 10, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 42, 22, @on
exec sp_trace_setevent @TraceID, 42, 26, @on
exec sp_trace_setevent @TraceID, 42, 34, @on
exec sp_trace_setevent @TraceID, 42, 50, @on
exec sp_trace_setevent @TraceID, 42, 62, @on
exec sp_trace_setevent @TraceID, 42, 3, @on
exec sp_trace_setevent @TraceID, 42, 11, @on
exec sp_trace_setevent @TraceID, 42, 35, @on
exec sp_trace_setevent @TraceID, 42, 51, @on
exec sp_trace_setevent @TraceID, 42, 4, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 42, 28, @on
exec sp_trace_setevent @TraceID, 42, 60, @on
exec sp_trace_setevent @TraceID, 42, 5, @on
exec sp_trace_setevent @TraceID, 42, 29, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 34, 1, 6, N'sp_configure'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 22, 2011 at 11:26 am
Thank you very much, Perry, that should do the job. Besides, I've learnt a bunch of things from it, I've just started using the Profiler.
Two more questions though.
How do I get rid of the traces when I no longer need them? I can stop those created in the Profiler, but what about those that are created by this script?
February 22, 2011 at 12:11 pm
gemisigo (2/22/2011)
Thank you very much, Perry, that should do the job. Besides, I've learnt a bunch of things from it, I've just started using the Profiler.Two more questions though.
How do I get rid of the traces when I no longer need them? I can stop those created in the Profiler, but what about those that are created by this script?
Have a look at these links:
http://www.eggheadcafe.com/software/aspnet/33910111/script-to-stop-running-traces.aspx
http://msdn.microsoft.com/en-us/library/ms176034.aspx
SELECT id, * FROM SYS.TRACES;
--Stop selected trace:
EXEC sp_trace_setstatus @traceid=@selected_id, @status = 0;
EXEC sp_trace_setstatus @traceid=@selected_id, @status = 2;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 22, 2011 at 12:11 pm
gemisigo (2/21/2011)
By the way, why does this "allow update" setting make maintenance plans fail in the first place?
What version of SQL are you running? According to BOL: allow updates Option. This setting has no effect starting in SQL Server 2005. The command should fail when you issue the RECONFIGURE command. In SQL 2000 this setting allowed a user to make direct updates to system tables.
It started recently that for every few days the maintenance plains start to fail causing log backups to be missing. I read many different forums and found out that setting "allow update" in sys.configurations using "sp_configure 'allow update', 1; RECONFIGURE" can result in such behavior.
I'm not sure how a maintenance plan can fail just due to "allow update" being set or not set. Even in SQL 2000 I don't recall ever having this problem. The instances of SQL 2000 that we still have, have this setting changed for various reasons and I don't recall it ever causing our maintenance plans to fail. What error message do you get when the jobs fail?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 22, 2011 at 12:58 pm
Shawn Melton (2/22/2011)
What version of SQL are you running? According to BOL: allow updates Option. This setting has no effect starting in SQL Server 2005. The command should fail when you issue the RECONFIGURE command. In SQL 2000 this setting allowed a user to make direct updates to system tables.
Yes, I've read that, and have seen in many forum posts. The sad thing is it isn't true. The server affected is 2008 R2 RTM (hmm, they really should install those CUs available). The command does not fail after issuing RECONFIGURE, it alters the setting.
Shawn Melton (2/22/2011)
I'm not sure how a maintenance plan can fail just due to "allow update" being set or not set. Even in SQL 2000 I don't recall ever having this problem. The instances of SQL 2000 that we still have, have this setting changed for various reasons and I don't recall it ever causing our maintenance plans to fail. What error message do you get when the jobs fail?
It is very strange indeed. It might not explicitly relate to "allow update" itself but to something that happens after it 🙂 But it definitely changes the behavior, tried several times. I set it to 1 and the plans fail. I reset it to 0 and they succeed. I hope someone will come up with a solution how to avoid that. Or with an explanation at least. What do I do wrong?
The error message is "Alter failed for Server '<ip address here>'."
By the way, thanks for the links, Marios.
February 22, 2011 at 1:07 pm
Shawn Melton (2/22/2011)
By the way, thanks for the links, Marios.
No problem, BTW a quick thing to do to find out what may be using sp_configure is to script out all jobs in your instance and search for the "sp_configure" string. It's likely that a job could be doing this, as jobs run under the SQL Agent account which is SYSADMIN on the instance. If the culprit job is scheduled to run at set times, and you observe the odd behavior at the same times, that would be a strong indication that a job is your candidate.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 22, 2011 at 1:30 pm
Good idea. Though the last two occasions when the plans went awry were at completely different times of day and none of the scheduled jobs were run at that time I'll nevertheless check them.
February 22, 2011 at 2:30 pm
Scripting jobs is way too much hassle. In this order
Run server side trace
Identify culprit
Slap heads
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 22, 2011 at 2:48 pm
The trace is already running, but it can only catch the villain :alien: after 'committing' 😀 the crime. And it causes log backups to be missing. I'm willing to spend some time investigating if I can prevent this 'felony'. Not too much time, of course. Either way, slapping will be issued, my Lord 😉
"Death by tray it shall be" 😎
February 22, 2011 at 2:56 pm
gemisigo (2/22/2011)
The trace is already running, but it can only catch the villain :alien: after 'committing' 😀 the crime. And it causes log backups to be missing. I'm willing to spend some time investigating if I can prevent this 'felony'. Not too much time, of course. Either way, slapping will be issued, my Lord 😉"Death by tray it shall be" 😎
Do you understand how to stop and close the trace?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 22, 2011 at 3:04 pm
For what I've read so far (links and the short examples Marios provided), I guess I have to issue the following commands:
EXEC sp_trace_setstatus @traceid=@traceid_returned_by_creator_script, @status = 2;
EXEC sp_trace_setstatus @traceid=@traceid_returned_by_creator_script, @status = 2;
The first one stops it and the second one will close and remove it from the server. Is that all?
UPDATE!!!
I've found a mouse in the trap! Someone was trying to alter accesses to some catalogs and it implicitly resulted in an "EXEC sys.sp_configure N'allow updates', N'1'".
I made some tests and though the RECONFIGURE command does not fail after setting 'allow update' to 0 and it does fail after setting it to 1, it does not reset (and I guess it should not either) config_value to 0, hence it remains 1. I still don't know why this makes backups to fail but at least I know how to fight it until I can fix it for good. It might be related to some other settings that take effect after issuing RECONFIGURE. Any ideas are welcome.
Thanks to everyone for your help!
February 24, 2011 at 4:45 am
EXEC sp_trace_setstatus @traceid=@traceid_returned_by_creator_script, @status = 0;
EXEC sp_trace_setstatus @traceid=@traceid_returned_by_creator_script, @status = 2;
First one stops the trace
second one closes and removes the definition
😉
gemisigo (2/22/2011)
UPDATE!!!I've found a mouse in the trap! Someone was trying to alter accesses to some catalogs and it implicitly resulted in an "EXEC sys.sp_configure N'allow updates', N'1'".
can you post details of their actions for further investigation
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 24, 2011 at 5:02 am
Perry Whittle (2/24/2011)
can you post details of their actions for further investigation
Not yet, the details are being queried, awaiting for reply. Will update later.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply