recovery mode changed

Last Post 31 Jul 2012 07:09 AM by ctseah. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ctseah
New Member
New Member

--
22 Jul 2012 11:44 PM


Hi,

I suspect there is some ghost jobs running which I could not locate in the maintenance plan and the sql agent jobs.

The message from sql server log shows this activity take place 'a alter database to make it simple recovery mode, and then alter database to make it full recovery mode again'.

I know this is a bad way of controlling the growth of the transaction log file and would like to stop this, but I could not locate where this could have been run from.

Anybody can share whether there is any method which I can take or command I can run to identify where this comes from ?

Thanks
regards
rm
New Member
New Member

--
23 Jul 2012 06:45 AM
Did it say from which machine/ip address?
ctseah
New Member
New Member

--
23 Jul 2012 09:18 AM

No it did not mention from which machine or ip address.
Seemed to be running from the server itself.
Or it could be through remote desktop connection.

regards
gunneyk
New Member
New Member

--
23 Jul 2012 11:49 AM
You can ge the user from the default trace files. Right click on the SQL Instance in SSMS Object Explorer and choose Reports - Standard Reports - Schema Changes History and scroll down to the time it occured.
ctseah
New Member
New Member

--
24 Jul 2012 06:27 AM
Hi,

I tried using the suggestion above, but the server error due to insufficient memory.
This server has only 4GB ram.

Anyway for me to read this default trace files ?

regards
gunneyk
New Member
New Member

--
24 Jul 2012 08:51 AM
Replace Path\filename.trc in the statement below with the path and name of the default trace file. You can file the path and the current trace file by running SELECT * FROM sys.traces and look for trace id 1. Look in that folder to see which file has the earliest number and use that one. The 2nd parameter in the fn_trace_gettable command is default which will read all the trace files in order starting with the one you list. So find the first number and use that. If it has been too long since this happened the trace files may have already rolled over the ones from that day.

SELECT *
FROM ::fn_trace_gettable('Path\FileName.trc', default)
ctseah
New Member
New Member

--
31 Jul 2012 07:09 AM

Hi

The select * from sys.traces followed by select * from ::fn_trace_gettable('Path\FileName.trc', default) worked !

I could locate the hostname where the command was executed.

Thank you very much.

Regards
You are not authorized to post a reply.

Acceptable Use Policy