Database In Simple Recovery Mode Still Generating 100s of GB of logs daily

we have sqlserver 2008 database generates far many logs, switched simple recovery mode, continues write around 100 gb per day ".ldf" after shrinking database.

how can stop database writing many logs? not simple recovery mode for?

hi dsm0wman,

 

please refer books online how resolve issue long running transaction cause log filling up:

 

managing long-running transactions:

http://msdn.microsoft.com/en-us/library/ms366331.aspx

 

in addition, output, query long running transaction below:

  create procedure [dbo].[dbm_trapmessages_deletestale] @date datetime as  begin set nocount on;  declare @chunksize as int  declare @catchcount int;  declare @msg varchar(max), @sev int, @st int  declare @rowcountvar int  set @chunksize = 10000  set @catchcount = 0;  set implicit_transactions off  set transaction isolation level read uncommitted  set rowcount @chunksize  nextchunk:   begin try delete traps   from (select top 10000 trapid from traps where datetime < @date) as t1   where traps.trapid=t1.trapid   select @rowcountvar = @@rowcount set @catchcount = 0;  --null catch count  end try  begin catch set @catchcount = @catchcount + 1; --increment catch count  if (@catchcount = 3)  --if catch count reach value re raise exception begin  select @msg = error_message(), @sev = error_severity(), @st = error_state() raiserror (@msg, @sev, @st) end  goto nextchunk;  end  catch if @rowcountvar = @chunksize  goto nextchunk  set rowcount 0  delete trapvarbinds  from trapvarbinds left join traps on trapvarbinds.trapid=traps.trapid  where traps.trapid is null  insert into events ( eventtime, acknowledged, message, eventtype, networknode, netobjectid )  values ( getdate(), 1, 'all trap messages older ' + convert(varchar(50), @date) + ' deleted during nightly database maintenance' , 1000 , 0 , 0 )  end    

 

thanks,
weilin qiao

---

please remember mark replies answers if , unmark them if provide no help. can beneficial other community members reading thread.

SQL Server  >  Getting started with SQL Server