April 12, 2009 at 5:13 am
hi,
My Production database suddenly keeps switching from full recovery Model to simple recovery model what colud be the reason.
Which database recovery model is best and why do we choose that recovery model than remaining models?
Thanks
Parthi
April 12, 2009 at 6:51 am
Someone or some process such as a SQL Agent job must be switching it to simple recovery. I'm not aware of anything that does it automatically in the background.
Which model is best depends on the needs of your business. Simple recovery does not allow for any log backups to be taken. That means that recovery to a point in time is not possible. For some businesses or some databases, this is OK. Others want to protect as much data as possible. So there they use full or bulk-logged recovery. This allows for backup of the transaction logs in addition to backup of the data so that you can recover to a specific moment on the system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 12, 2009 at 7:19 am
You could use a DDL trigger (DDL_SERVER_LEVEL_EVENTS) to identify which process and command is changing the recovery model.
Something that logs to a table based on the following might work:
CREATE TRIGGER dtrg_alter_database
ON ALL SERVER
FOR ALTER_DATABASE
AS
SELECTEVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as cmd,
EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','int') as spid
GO
-- Later
DROP TRIGGER dtrg_alter_database
ON ALL SERVER;
/Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2009 at 8:43 am
parthikrishna (4/12/2009)
Which database recovery model is best and why do we choose that recovery model than remaining models?
Take a read through these:
Understanding Logging and Recovery in SQL Server
Managing Transaction Logs[/url]
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
April 12, 2009 at 2:24 pm
set up a simple server side trace and leave it running for a day or so. Should be able to find out/what is changing the status!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 12, 2009 at 3:06 pm
In case the instruction to alter the database to simple mode is held in stored proc code somewhere (I've been the victim of similar things in third party apps) there will be scripts on this site to search for strings in the syscomments table, so you could use one of those, but for a one off search heres a quick and dirty method: (presuming you don't have a huge number of large procs!)
run the following SQL in the context of the database with the issue outputting results in text format
select 'exec sp_helptext ', name from sys.sysobjects where type = 'P' order by name
copy the results of this query to another query window and run it
use edit\find to search the results of that query for keywords such as 'simple' or 'alter database'
---------------------------------------------------------------------
April 12, 2009 at 4:03 pm
Just to add to what George said I too have been victim of something similar, my problem was caused by a third party monitoring tool that had been configured incorrectly - By someone else I hasten to add 🙂 and that kept switching the prod db to simple recovery.
Gethyn Elliswww.gethynellis.com
April 13, 2009 at 6:35 am
this should cut down on the amount of procedures returned
select 'exec sp_helptext ' + name from sys.objects where type = 'P'
and is_ms_shipped <> 1
order by name
you dont want any procs that are MS default
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 16, 2009 at 10:45 am
I use this to search stored procedures. You could search on 'ALTER DATABASE'
-- Find the string 'xxxxxxxxxx' in Stored procedures then select 150 characters that contain it
-- the -25 returns results that start before the character string so you can see the context a bit.
.
select name, crdate, substring(text,CHARINDEX('CharacterString', text)-25,150) as 'Code Snippet'
from syscomments com
join sysobjects obj on com.id = obj.id
where text like '%CharacterString%'
order by name
April 16, 2009 at 1:50 pm
Since this is SQL 2005, rather use sys.objects and sys.sql_modules
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
April 16, 2009 at 2:57 pm
GilaMonster (4/16/2009)
Since this is SQL 2005, rather use sys.objects and sys.sql_modules
Yes, I need to rewrite my little system scripts.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply