April 6, 2013 at 9:21 am
Since we're going nowhere fast here...
Finish this script (the file name) and then run this trace for a couple of days. It will pick up all backups, all restores and all changes of recovery model. After a couple days, open up the trace output in profiler, see what else is taking full backups, if there are any log backups that explicitly truncate the log and if there are any changes in recovery model.
/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler */
/* Date: 2013/04/06 17:18:38 */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100;
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 115, 1, @on
exec sp_trace_setevent @TraceID, 115, 6, @on
exec sp_trace_setevent @TraceID, 115, 10, @on
exec sp_trace_setevent @TraceID, 115, 14, @on
exec sp_trace_setevent @TraceID, 115, 11, @on
exec sp_trace_setevent @TraceID, 115, 12, @on
exec sp_trace_setevent @TraceID, 128, 1, @on
exec sp_trace_setevent @TraceID, 128, 6, @on
exec sp_trace_setevent @TraceID, 128, 10, @on
exec sp_trace_setevent @TraceID, 128, 14, @on
exec sp_trace_setevent @TraceID, 128, 11, @on
exec sp_trace_setevent @TraceID, 128, 12, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
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 6, 2013 at 2:36 pm
GilaMonster (4/6/2013)
Since we're going nowhere fast here...Finish this script (the file name) and then run this trace for a couple of days. It will pick up all backups, all restores and all changes of recovery model. After a couple days, open up the trace output in profiler, see what else is taking full backups, if there are any log backups that explicitly truncate the log and if there are any changes in recovery model.
/****************************************************//* Created by: SQL Server 2008 R2 Profiler */
/* Date: 2013/04/06 17:18:38 */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100;
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 115, 1, @on
exec sp_trace_setevent @TraceID, 115, 6, @on
exec sp_trace_setevent @TraceID, 115, 10, @on
exec sp_trace_setevent @TraceID, 115, 14, @on
exec sp_trace_setevent @TraceID, 115, 11, @on
exec sp_trace_setevent @TraceID, 115, 12, @on
exec sp_trace_setevent @TraceID, 128, 1, @on
exec sp_trace_setevent @TraceID, 128, 6, @on
exec sp_trace_setevent @TraceID, 128, 10, @on
exec sp_trace_setevent @TraceID, 128, 14, @on
exec sp_trace_setevent @TraceID, 128, 11, @on
exec sp_trace_setevent @TraceID, 128, 12, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
Will do! thanks. I will let you know the outcome
April 8, 2013 at 7:15 am
So I found the issue. Apparently the previous DBA has a job that is changing the database from Full to Simple to do a database optimization. I don't understand why the previous database did that, but what is more confusing is that all of the other databases are going through the same process and they are not being affected. What gives?
April 8, 2013 at 7:24 am
Timing of the full backups? Those other databases having diff backups running after the 'optimisation' 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
April 8, 2013 at 7:31 am
If we look at the job schedule in terms of yesterday and today.
The job to optimize runs at about 8pm 04/07/2013, at the end it returns them to full. Then a full runs about 12 am which will be this morning 4/8. Then the Trans_log runs at 7 am 4/8
April 8, 2013 at 7:46 am
So the full backup is reinitialising the log chain and hence the log backups run. It's not a good maintenance schedule though. Switching to simple recovery for index rebuilds is not going to do good things to your ability to restore to a point in time (which I assume is why the DB is in full recovery).
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 8, 2013 at 7:51 am
I completely agree with you. I am not sure why they did that to begin with I will create a new optimization job later, but for right now I have to stop it because I need those Tran jobs to run. It's critical!
July 28, 2015 at 10:23 am
This is very simple step.
1. Take the full backup.
2. Take the log backup.
your problem will resolve.
August 14, 2016 at 5:06 am
Thank you very much
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply