January 12, 2007 at 10:02 am
I have a production database that is set to FULL recovery mode. Every so often this is somehow changed to Simple mode. This obviously should not happen. I am trying to track down who or what is doing this. I have gone through all of the jobs and steps on the server and do not see anything that would change the mode.
I created a job to run every 5 minutes that checks the Recovery mode of the database and emails me if it is in Simple mode. I created this job on Tuesday and got my first notification today at 9:15 that the mode had been changed.
I again checked the jobs to see if anything had run around that 9:15 time frame with no luck.
I would like to do a trace to see if I can capture the culprit in the act. I tried capturing SQL:StmtCompleted and SQL:BatchCompleted. I put a filter on the database id and also added a TextData filter of ALTER%. The problem is it doesn't look like the TextData filter is working. I am getting all kinds of entries with blank TextData.
Does anyone have a better idea for how to do this? Or does anyone know how to fix the trace to only capture ALTER statements. I need to do this to keep the overhead low.
Thanks for any help you might be able to provide.
George
January 12, 2007 at 10:10 am
Put RPC:Completed in there too. Seems like you should be getting some sql for the SQL:BatchCompleted.
If it's happening in a stored procedure, I don't think you'll see the 'Alter%', right?
January 12, 2007 at 10:22 am
Good idea. I can add the RPC:Completed. And you are correct, it it is a stored procedure doing this I will not see it in the trace. I guess I can look through syscomments for a reference to ALTER but I'd have to do that for all 24 databases on the server. Yuk.
The problem with the trace is that it doesn't look like the filter is working properly. I am getting a lot of StmtCompleted with blank TextData. I need to make the trace as lightweight as possible.
Thanks for your help.
George
January 12, 2007 at 10:29 am
Are you aware that in addition to "alter database" command and there is also the stored procedure "sp_dboption" which internally has an "alter database" ?
The "sp_dboption" isretained for backward compatability.
You should search and trace for both.
SQL = Scarcely Qualifies as a Language
January 12, 2007 at 10:59 am
It could be initiated from another server. For instance, a utility server running a reindex job, setting the DB to simple first to avoid huge t-log growth, but forgetting to set back to FULL when it's done.
January 12, 2007 at 11:19 am
True, I should check for sp_dboption as well. Good point.
Yes, it could very well be coming from another server. I've combed through most of my servers and haven't found anything. I have set up the reindexing routines on all of the servers. I thought that might be it as you suggested. But I put the db into Bulk_Logged rather than simple and then change it back to Full. This way the backup chain is not broken.
If I could get this trace working properly I could leave it running as the overhead would be pretty low. But the dang thing won't cooperate.
Thanks for all the input.
George
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply