| Author |
Topic |
|
uberman
Posting Yak Master
159 Posts |
 Posted - 2006-09-13 : 03:50:48
|
| A db I am "interested" in occasionally seems to change recovery mode from Full (a good thing, 15min TRNs are taken) to Simple (a bad thing as no TRNs are taken)Apparently nobody is doing the change, it just seems to "happen"Is there any way that I can historically see when/how/who changed the mode from full to simple?If not is there any way in future a simple way to log this information so we can start some finger pointing? |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-13 : 04:05:08
|
| I don't think you could see this retrospectively, although you could recovery up to the point where it was changed (from backups) and see what was going on at the time - that might point-the-finger.I imagine that you could use SQL Profiler to see who/how it changes next time though.Kristen |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-13 : 10:14:29
|
| You might start by identifying who actually has the access to do that. It doesn't just "happen".CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-14 : 05:49:35
|
| "You might start by identifying who ..."... or possibly What ...Kristen |
|
|
|
Luis Martin
Yak Posting Veteran
54 Posts |
Posted - 2006-09-14 : 09:42:17
|
| Are you the only administrator, I mean sa? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-14 : 10:16:32
|
| "Are you the only administrator"Hehehe ... I think uberman would remember if he had changed the database to Simple!Kristen |
|
|
|
Luis Martin
Yak Posting Veteran
54 Posts |
Posted - 2006-09-14 : 11:42:56
|
quote:
Originally posted by Kristen
"Are you the only administrator"Hehehe ... I think uberman would remember if he had changed the database to Simple!Kristen
Yes, I pressume that, but he is the only one? |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-09-14 : 13:20:19
|
quote:
Originally posted by ubermanIf not is there any way in future a simple way to log this information so we can start some finger pointing?
finger pointing is not a good idea. remove access so that you are the only one who can make this change. -ec |
|
|
|
uberman
Posting Yak Master
159 Posts |
Posted - 2006-09-15 : 04:15:23
|
| Ahhhh, if only I was the one (sounds like a film quote)Unfortunately, the server is, how shall I say, "shared" and I don't own it, but I have full control and the finger is kinda pointing at meAnd I know I didn't do it guv. |
|
|
|
anilkdanta
Starting Member
25 Posts |
Posted - 2006-09-15 : 08:05:55
|
| Use SQL Profiler to capture the information about TSQL, SP executions.You can easily find out who , when has changed the Recovery Model of the database you are talking about. |
|
|
|
scottpt
Posting Yak Master
186 Posts |
Posted - 2006-09-15 : 09:22:54
|
| Start by getting a list of all users in the SYSADMIN server group.Next get a list of all db_owners in the database.These are the ones who can change that. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-15 : 10:07:13
|
quote:
Originally posted by scottpt
Start by getting a list of all users in the SYSADMIN server group.Next get a list of all db_owners in the database.These are the ones who can change that.
Or anyone who knows their passwords.Also:Logins in the dbcreator fixed server role can change the recovery mode.From 2000 BOL:"ALTER DATABASE permissions default to members of the sysadmin and dbcreator fixed server roles, and to members of the db_owner fixed database roles. These permissions are not transferable.'CODO ERGO SUM |
|
|
|
EugeneZ
Starting Member
26 Posts |
|
|
|
Recovery Full to Simple - who changed it?