Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Something is changing the RECOVERY MODE to SIMPLE to ALL databases... Expand / Collapse
Author
Message
Posted Monday, May 27, 2013 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 29, 2013 10:18 AM
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...
Post #1457121
Posted Monday, May 27, 2013 9:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 39,882, Visits: 36,228
Check all your SQL Agent jobs. Probably find it's a badly written index rebuild job.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1457122
Posted Monday, May 27, 2013 9:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 29, 2013 10:18 AM
Points: 3, Visits: 36
Hi, thanks foir the reply, I looked with this search in all jobswith 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.
Post #1457125
Posted Monday, May 27, 2013 10:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 39,882, Visits: 36,228
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 2008, MVP
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

Post #1457126
Posted Monday, May 27, 2013 10:22 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 17,620, Visits: 15,475
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1457127
Posted Monday, May 27, 2013 10:41 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 5,863, Visits: 12,942
check any maintenance plans as well if you have them.

---------------------------------------------------------------------

Post #1457130
Posted Monday, May 27, 2013 10:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 29, 2013 10:18 AM
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?
Post #1457131
Posted Monday, May 27, 2013 10:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 39,882, Visits: 36,228
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 2008, MVP
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

Post #1457132
Posted Monday, May 27, 2013 10:56 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 17,620, Visits: 15,475
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1457133
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse