Who's been switching my Recovery Model

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Who's been switching my Recovery Model

Author

Topic

savvy95
Starting Member

23 Posts

Posted - 2005-08-30 : 12:19:22

A client has complained that somebody is switching their Db's recovery model from FULL to Simple and would like me to catch the culprit.

Is there a way to monitor who (or what job) makes the modification to Db?

Thanks

Kristen
Test

22859 Posts

Posted - 2005-08-30 : 12:28:31

Tricky, I would say!

Don't think you can do a trigger (although you can do something akin to that in SQL 2005 [on a system table I mean] I believe)

I think I would start with a scheduled task that logs the time when it changes and set that running every, say, minute. That would identify WHEN it changed, and that in turn might suggest a sheduled task that was running at the time and which did the deed - e.g. an overnight REBUILD INDEX that was trying to reduce log usage (but not setting the DB back again!).

Your scheduled job could also change it from SIMPLE back to FULL when it found it had been tinkered with ... that would then tell you (fairly accurately) how often it was getting set to SIMPLE

There's probably a better way though

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-30 : 12:37:58

I think SQL Profiler should be able to catch this.

Tara

savvy95
Starting Member

23 Posts

Posted - 2005-08-30 : 14:09:10

Thanks Kristen and Tara,

Kristen-- which system table holds the recovery model?
Tara -- I looked in Profiler but no events or data columns seem to meet the need. But I'm looking some more.

Any other ideas would be greatly appreciated

savvy95
Starting Member

23 Posts

Posted - 2005-08-30 : 14:30:41

Tara -- I tried Profiler and can get it to report when Db goes to Full and Bulk_logged, but not Simple. Funny, you'd think it Profiler would capture all not two-thirds of the required data. Anyhow, I'll keep looking. thanks again.

Kristen
Test

22859 Posts

Posted - 2005-08-30 : 23:47:45

"which system table holds the recovery model"

SELECT DATABASEPROPERTYEX('MydatabaseName', 'Recovery')

Kristen

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2005-08-31 : 03:08:22

Run profiler trace with


EVENTS : 
TSQL -         SQL:BatchCompleted
               SQL:StmtCompleted 
STORED PROCS - SP:StmtCompleted


FILTER : 
TextData Like "%ALTER%DATABASE%SET%RECOVERY%SIMPLE%"

Hemanth Gorijala
I Came. I Saw. I Normalized.

savvy95
Starting Member

23 Posts

Posted - 2005-08-31 : 09:56:41

Thanks Hemanth that did the trick with a bit of tweaking.

I'm looking forward to catching the culprit.

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 10:30:31

"I'm looking forward to catching the culprit"

Me too!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources