May 27, 2013 at 9:29 am
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.6071124AL 167SERFINCO\dbagentDB_SoporteAplicacionesdboALTER DATABASE DB_SoporteAplicaciones SET RECOVERY FULL WITH NO_WAIT;DB_SoporteAplicaciones
2013-05-26 04:24:47.5981115DBCC167SERFINCO\dbagentmasterdboDBCC SHRINKFILE (DB_SoporteAplicaciones_log, 0);
2013-05-26 04:24:47.4851002AL 167SERFINCO\dbagentDB_SoporteAplicacionesdboALTER DATABASE DB_SoporteAplicaciones SET RECOVERY SIMPLE WITH NO_WAIT;DB_SoporteAplicaciones
2013-05-26 04:24:47.3510868AL 167SERFINCO\dbagentMDWdboALTER DATABASE MDW SET RECOVERY FULL WITH NO_WAIT;MDW
2013-05-26 04:24:47.3350852DBCC167SERFINCO\dbagentmasterdboDBCC SHRINKFILE (MDW_log, 0);
2013-05-26 04:24:45.9669484AL 167SERFINCO\dbagentMDWdboALTER DATABASE MDW SET RECOVERY SIMPLE WITH NO_WAIT;MDW
2013-05-26 04:24:45.9049422AL 167SERFINCO\dbagentdbPDP_CobroCustodiasdboALTER DATABASE dbPDP_CobroCustodias SET RECOVERY FULL WITH NO_WAIT;dbPDP_CobroCustodias
2013-05-26 04:24:45.8929410DBCC167SERFINCO\dbagentmasterdboDBCC SHRINKFILE (dbPDP_CobroCustodias_log, 0);
2013-05-26 04:24:45.8579375AL 167SERFINCO\dbagentdbPDP_CobroCustodiasdboALTER DATABASE dbPDP_CobroCustodias SET RECOVERY SIMPLE WITH NO_WAIT;dbPDP_CobroCustodias
2013-05-26 04:24:45.7989316AL 167SERFINCO\dbagentdbAdminProcesosdboALTER 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...
May 27, 2013 at 9:31 am
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
May 27, 2013 at 9:58 am
Hi, thanks foir the reply, I looked with this search in all jobs:(with 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.
May 27, 2013 at 10:07 am
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
May 27, 2013 at 10:22 am
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[/url]
Learn Extended Events
May 27, 2013 at 10:41 am
check any maintenance plans as well if you have them.
---------------------------------------------------------------------
May 27, 2013 at 10:44 am
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?
May 27, 2013 at 10:49 am
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
May 27, 2013 at 10:56 am
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[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply