February 21, 2008 at 8:58 am
I see a stored proc which belongs to the application, that might be altering the db recovery model, So 2 days back the trace was started to run and I am outputting the trace results into a sql table. I think sometime during the night time the recovery model has changed from Full to Simple. I am trying to find from the trace table to see if that has been traced, unfortunately I am unable to find that in the trace table. I was looking for key words like Alter, simple, full, recover etc.. but could'nt find that in the trace. I just stopped the trace on that db. Please suggest if I have to do something different. Thanks!!
February 21, 2008 at 9:19 am
what events are you tracing? Can you see the stored proc being executed and tie that in with the time log backups starting failing?
---------------------------------------------------------------------
February 21, 2008 at 10:14 am
Thanks for the quick response! I am pulling all the events to make sure I do'nt eliminate anything. I can't tie back the tran log backup sql job with the recovery model change on the db as the backup job currently does'nt run during the night time and the recovery model change happened after 10PM for sure as the last tran log backup job for that db ran fine @10PM.
February 21, 2008 at 10:31 am
well if you've got sp_completed and sqlstatement completed (or whatever called - pushed for time here), it should be in there.
run your tlog backups through the night or schedule frequent sp_helpdbs (or both) to narrow down when it happens, then can pinpoint where in trace to look
Anyone have the app knowledge to know when suspect sp gets fired?
---------------------------------------------------------------------
February 21, 2008 at 11:00 am
Import the trace into a DB table, then query it for any rows where TextData Like '%alter%' and TextData Like '%database%'
It may take a while, but it'll be faster than reading through manually.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2008 at 11:15 am
I haven't seen this mentioned yet - but the SQL Server log seems to capture changes to the RECOVERY model. I just did that and I get this in the SQL Server log as an entry:
"Setting Database option RECOVERY to SIMPLE for database MyDatabase"
With a time and a SPID. Should help narrow down substantially what you need to find in Profiler.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 21, 2008 at 11:23 am
george sibbald (2/21/2008)
well if you've got sp_completed and sqlstatement completed (or whatever called - pushed for time here), it should be in there.run your tlog backups through the night or schedule frequent sp_helpdbs (or both) to narrow down when it happens, then can pinpoint where in trace to look
Anyone have the app knowledge to know when suspect sp gets fired?
George,
Please explain the below:
got sp_completed and sqlstatement completed (or whatever called - pushed for time here), it should be in there??
Currently the tape backup's run during the night time so we can't run the tran log backup's during that time to avoid clashing, but we are going to change that soon.
How can I schedule sp_helpdbs?? Thank You!!!!
February 21, 2008 at 2:13 pm
Matt Miller (2/21/2008)
I haven't seen this mentioned yet - but the SQL Server log seems to capture changes to the RECOVERY model. I just did that and I get this in the SQL Server log as an entry:"Setting Database option RECOVERY to SIMPLE for database MyDatabase"
With a time and a SPID. Should help narrow down substantially what you need to find in Profiler.
I checked the sql server logs(Current - 02/21/2008) which is under management in SQL 2000, but I do'nt see anything about the recovery model changing to Simple. I also changed the recovery model from Simple to Full even that's not captured in the log. Please let me know if I am looking at the right place. Thanks much!
February 21, 2008 at 2:24 pm
Mh (2/21/2008)
Matt Miller (2/21/2008)
I haven't seen this mentioned yet - but the SQL Server log seems to capture changes to the RECOVERY model. I just did that and I get this in the SQL Server log as an entry:"Setting Database option RECOVERY to SIMPLE for database MyDatabase"
With a time and a SPID. Should help narrow down substantially what you need to find in Profiler.
I checked the sql server logs(Current - 02/21/2008) which is under management in SQL 2000, but I do'nt see anything about the recovery model changing to Simple. I also changed the recovery model from Simple to Full even that's not captured in the log. Please let me know if I am looking at the right place. Thanks much!
You are right - I just checked against a 2000 database, and it doesn't log that activity in 2000. I didn't realize that that was new behavior....That stinks...
Sorry!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 21, 2008 at 2:30 pm
Matt Miller (2/21/2008)
Mh (2/21/2008)
Matt Miller (2/21/2008)
I haven't seen this mentioned yet - but the SQL Server log seems to capture changes to the RECOVERY model. I just did that and I get this in the SQL Server log as an entry:"Setting Database option RECOVERY to SIMPLE for database MyDatabase"
With a time and a SPID. Should help narrow down substantially what you need to find in Profiler.
I checked the sql server logs(Current - 02/21/2008) which is under management in SQL 2000, but I do'nt see anything about the recovery model changing to Simple. I also changed the recovery model from Simple to Full even that's not captured in the log. Please let me know if I am looking at the right place. Thanks much!
You are right - I just checked against a 2000 database, and it doesn't log that activity in 2000. I didn't realize that that was new behavior....That stinks...
Sorry!
That's okay, Did you mean earlier in SQL 2005, where we can see in the SQL logs?
February 21, 2008 at 2:54 pm
yup. 2005 logs the change to the SQL Server logs.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2008 at 4:02 am
Mh,
if you are sure you have identified the stored proc altering database the only event you have to capture is stored procedures\sp:completed. This will tell you when the stored proc fires, but the actual sql within the stored proc will not be shown, so you will not be able to search on 'alter' or 'simple', just the stored proc name.
If you want to see the actual alter database you will need event stored procedures\sp:stmtstarting. Then you can search on alter etc BUT you will have a LOT more output. if you do this use sp:starting as well to 'frame' sp executions.
there is a find facility in profiler you can use, but hopefully you will have a narrow time frame you need to search in anyway, else load trace into a table, up to you.
sp_helpdb - set up a sqlagent job to run every minute which runs sp_helpdb 'dbname' and outputs to a file.
I'll bet theres SQL to return just the recovery mode but don't know it offhand - I bet Gail has one to hand!
george
---------------------------------------------------------------------
February 25, 2008 at 1:54 pm
george sibbald (2/22/2008)
Mh,if you are sure you have identified the stored proc altering database the only event you have to capture is stored procedures\sp:completed. This will tell you when the stored proc fires, but the actual sql within the stored proc will not be shown, so you will not be able to search on 'alter' or 'simple', just the stored proc name.
If you want to see the actual alter database you will need event stored procedures\sp:stmtstarting. Then you can search on alter etc BUT you will have a LOT more output. if you do this use sp:starting as well to 'frame' sp executions.
there is a find facility in profiler you can use, but hopefully you will have a narrow time frame you need to search in anyway, else load trace into a table, up to you.
sp_helpdb - set up a sqlagent job to run every minute which runs sp_helpdb 'dbname' and outputs to a file.
I'll bet theres SQL to return just the recovery mode but don't know it offhand - I bet Gail has one to hand!
george
Hi george,
Thanks for the response, I already started running the sql profiler trace with the following event classes:
sp:completed, sp:stmtstarting, and couple more event classes
In addition to the above, I am thinking of scheduling sql job using sp_helpdb, the follow is the way I am planning to set up the job:
Type: TSQL
Command: EXEC sp_helpdb N'dbname'
Under advanced: specify the output file.
I shall schedule the job to run every 3 hours, so that does'nt impact prod.
Please let me know if I am missing anything here. Thanks!!!
February 26, 2008 at 3:34 am
running sp_helpdb won't impact your prod run. if you only run every 3 hours you have a 3 hour winwindow to search for the change to recovery mode, I would run it more frequently. run every 5 mins if you want.
---------------------------------------------------------------------
February 26, 2008 at 9:25 am
george,
Per your suggestion, just captured the stored proc events and started the trace last night, I was able to capture the stored proc which I was thinking is doing that. Thank you very much!!! 🙂
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply