February 22, 2011 at 5:04 am
I was looking at the SQL Log Files on one of my servers and saw this... what's going on here?
2/20/11 12:10:57 | SPID55 | Setting database option RECOVERY to FULL for Database X
2/20/11 12:10:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database X
2/20/11 12:09:57 | SPID55 | Setting database option RECOVERY to FULL for Database X
2/20/11 12:09:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database X
2/20/11 12:09:57 | SPID55 | Setting database option RECOVERY to FULL for Database X
2/20/11 12:08:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database X
2/20/11 12:08:57 | SPID55 | Setting database option RECOVERY to FULL for Database X
2/20/11 12:08:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database X
2/20/11 12:07:57 | SPID55 | Setting database option RECOVERY to FULL for Database X
2/20/11 12:07:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database X
2/20/11 12:07:57 | SPID55 | Setting database option RECOVERY to FULL for Database X
2/20/11 12:07:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database X
2/20/11 12:06:57 | SPID55 | Setting database option RECOVERY to FULL for Database X
2/20/11 12:06:57 | SPID55 | Setting database option RECOVERY to FULL for Database Y
2/20/11 12:05:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database Y
2/20/11 12:04:57 | Backup | Database backed up. Database Y.....
2/20/11 12:03:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database X
2/20/11 12:03:57 | SPID55 | Setting database option RECOVERY to FULL for Database X
2/20/11 12:03:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database X
2/20/11 12:03:57 | SPID55 | Setting database option RECOVERY to FULL for Database X
2/20/11 12:03:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database X
2/20/11 12:02:57 | SPID55 | Setting database option RECOVERY to FULL for Database X
2/20/11 12:02:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database X
2/20/11 12:02:57 | SPID55 | Setting database option RECOVERY to FULL for Database X
2/20/11 12:02:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database X
2/20/11 12:02:57 | SPID55 | Setting database option RECOVERY to FULL for Database X
2/20/11 12:02:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database X
2/20/11 12:02:57 | SPID55 | Setting database option RECOVERY to FULL for Database X
2/20/11 12:02:57 | SPID55 | Setting database option RECOVERY to SIMPLE for Database X
This is happening once per week or about every 6 days.
There are no jobs and no maintenance plans that I can see.
Thank you!
February 22, 2011 at 5:13 am
Did you try running profiler during this time if you see this happen at particular time or manually run sp_who2 when this is occuring ?
Also suggest querying the MSDB database.. jobs history system tables just in case GUI is misleading.
Cheers
Sat
Cheer Satish 🙂
February 22, 2011 at 5:14 am
TECHBABU (2/22/2011)
Did you try running profiler during this time if you see this happen at particular time or manually run sp_who2 when this is occuring ?Also suggest querying the MSDB database.. jobs history system tables just in case GUI is misleading.
Cheers
Sat
Like Tech said, even check also you default trace its located in youre LOG dir any alter database commands will be logged in the default trace
February 22, 2011 at 5:40 am
Ok slow down... So I need to start a trace? How long should I let it run?
February 22, 2011 at 5:45 am
Vertigo44 (2/22/2011)
Ok slow down... So I need to start a trace? How long should I let it run?
imho check first the default trace (which is always running) it is located in the instance Log directory. *.trc file. you can see there which login is responsible for it, and maybe it will give you the eureka feeling
February 22, 2011 at 5:47 am
I never knew about a default trace... I will check into it. Thanks!
February 22, 2011 at 5:52 am
Ok I found log_133.trc its 11mb in size. Now how to I use it / load it?
February 22, 2011 at 5:55 am
open it in youre SQL profiler
February 22, 2011 at 5:56 am
I'd start by looking at whatever job does your backups...
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 22, 2011 at 6:01 am
GilaMonster (2/22/2011)
I'd start by looking at whatever job does your backups...
There are no jobs\maintance plans the OP stated, but i agree cant believe this. unless they work with Sqlcmd schedule tasks or something
February 22, 2011 at 6:02 am
Good point. I was just on the server grabbing those .trc files and saw backups located in a strange directory or the databases...yet there are no jobs or maintenance plans listed. I'll ask around and see if we are using some 3rd party backup tool on this one.
I have the trace files now and using profiler to check them out. Thanks guys!
February 22, 2011 at 6:03 am
Good point. I was just on the server grabbing those .trc files and saw backups located in a strange directory or the databases...yet there are no jobs or maintenance plans listed. I'll ask around and see if we are using some 3rd party backup tool on this one.
I have the trace files now and using profiler to check them out. Thanks guys!
February 24, 2011 at 11:15 am
I found out that the application is running these backups. I unchecked the jobs it called Journal Management which I believe is what is causing the Journal database to switch back and fourth like this. Any ideas as to why they would program this feature in the application to tell the database to change recovery models in such as way? Could it be a crap way of managing the transaction logs?
Anyway, I disable all the jobs that run from the application and I am going to setup some maintenance plans from within the database. This should alleviate the recovery model switching issue.
Ideas a welcome!
February 24, 2011 at 11:20 am
Vertigo44 (2/24/2011)
Could it be a crap way of managing the transaction logs?
Probably (for certain definitions of 'managing')
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 24, 2011 at 2:48 pm
More likely it is their way of trying to reduce log usage during index rebuilds. They probably have code written that switches to simple recovery, rebuilds the index and switches back.
Loop on the selected indexes to be rebuilt and you see this kind of result.
Of course, the problem with this is that they have broken the log chain and you would not be able to restore to a point in time after that break if you had to restore from a prior known good backup.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply