Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Something is changing the RECOVERY MODE to SIMPLE to ALL databases...


Something is changing the RECOVERY MODE to SIMPLE to ALL databases...

Author
Message
Lorenzo Mota
Lorenzo Mota
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 36
Hi, I have a big issue that happens randomly, the dbagent is changing the recovery mode to simple, then performs a dbcc shrinkfile and after that, it performs a change to the recovery mode to full again...

I audited and this is the trace I got:

SELECT event_time, action_id, succeeded, session_id, session_server_principal_name,
target_server_principal_name, target_database_principal_name, database_name,
database_principal_name, schema_name, object_name, statement
FROM sys.fn_get_audit_file ('C:\Temp\SQLAudit\MyServerAudit_*.sqlaudit',default,default)
where statement like '%RECOVERY%'
order by event_time desc;

2013-05-26 04:24:47.6071124 AL 1 67 SERFINCO\dbagent DB_SoporteAplicaciones dbo ALTER DATABASE DB_SoporteAplicaciones SET RECOVERY FULL WITH NO_WAIT; DB_SoporteAplicaciones
2013-05-26 04:24:47.5981115 DBCC 1 67 SERFINCO\dbagent master dbo DBCC SHRINKFILE (DB_SoporteAplicaciones_log, 0);
2013-05-26 04:24:47.4851002 AL 1 67 SERFINCO\dbagent DB_SoporteAplicaciones dbo ALTER DATABASE DB_SoporteAplicaciones SET RECOVERY SIMPLE WITH NO_WAIT; DB_SoporteAplicaciones
2013-05-26 04:24:47.3510868 AL 1 67 SERFINCO\dbagent MDW dbo ALTER DATABASE MDW SET RECOVERY FULL WITH NO_WAIT; MDW
2013-05-26 04:24:47.3350852 DBCC 1 67 SERFINCO\dbagent master dbo DBCC SHRINKFILE (MDW_log, 0);
2013-05-26 04:24:45.9669484 AL 1 67 SERFINCO\dbagent MDW dbo ALTER DATABASE MDW SET RECOVERY SIMPLE WITH NO_WAIT; MDW
2013-05-26 04:24:45.9049422 AL 1 67 SERFINCO\dbagent dbPDP_CobroCustodias dbo ALTER DATABASE dbPDP_CobroCustodias SET RECOVERY FULL WITH NO_WAIT; dbPDP_CobroCustodias
2013-05-26 04:24:45.8929410 DBCC 1 67 SERFINCO\dbagent master dbo DBCC SHRINKFILE (dbPDP_CobroCustodias_log, 0);
2013-05-26 04:24:45.8579375 AL 1 67 SERFINCO\dbagent dbPDP_CobroCustodias dbo ALTER DATABASE dbPDP_CobroCustodias SET RECOVERY SIMPLE WITH NO_WAIT; dbPDP_CobroCustodias
2013-05-26 04:24:45.7989316 AL 1 67 SERFINCO\dbagent dbAdminProcesos dbo ALTER DATABASE dbAdminProcesos SET RECOVERY FULL WITH NO_WAIT; dbAdminProcesos
.
.
.
And goes on for the 39 user databases. The only databases that don't affect are: msdb, master, tempdb, and model.

It didn't happen for almost 2 months then it was done the 24th and the 26th in different times.

The problem is that a I have a logshipping config, that breaks with this...

I checked all the stored procedures and there is no command like those. I wonder if the DTS can do that, but I am checking. Any ideas?

I checked this post: http://www.sqlservercentral.com/Forums/Topic453312-5-1.aspx

but didn't find any clue...
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47206 Visits: 44367
Check all your SQL Agent jobs. Probably find it's a badly written index rebuild job.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Lorenzo Mota
Lorenzo Mota
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 36
Hi, thanks foir the reply, I looked with this search in all jobsSadwith SET RECOVERY, ALTER DATABASE, DBCC SHRINK)

EXEC sp_MSForEachDB ' Use ?; select ''?'', o.name,m.definition from ?.SYS.sql_modules m
inner join SYS.sysobjects o on m.object_id=o.id
where definition like ''%SET RECOVERY%'''

Didn't find anything, just comments...

Tell me what you think.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47206 Visits: 44367
That query's searching procedures and functions, not jobs.

Look at your SQL Agent scheduled jobs, check their steps, see what they do.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
You can try this as a shortcut

SELECT *
FROM msdb.dbo.sysjobsteps
WHERE command LIKE 'alter database%';





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
check any maintenance plans as well if you have them.

---------------------------------------------------------------------
Lorenzo Mota
Lorenzo Mota
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 36
Hi,

The Jobs perform 2 things:

1) calls to stored procs, that I already checked

2) Calls to DTSx (Haven't checked)

So, besides the DTS, is there anything automatic or internal of SQL Server that could be doing this?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47206 Visits: 44367
Lorenzo Mota (5/27/2013)
So, besides the DTS, is there anything automatic or internal of SQL Server that could be doing this?


No. SQL will never automatically change database settings. If recovery model is changing, you have some task that someone created which is doing that.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
GilaMonster (5/27/2013)
Lorenzo Mota (5/27/2013)
So, besides the DTS, is there anything automatic or internal of SQL Server that could be doing this?


No. SQL will never automatically change database settings. If recovery model is changing, you have some task that someone created which is doing that.


Like Gail said, this is something that somebody has created/written/implemented that is changing your recovery models.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search